I'm new to access and working through a 'teach yourself' book. I want to maintain a control file that has the history of order numbers used (order_number, supplier, date_issued). I want to pick up the last record, add one to the order number, update with supplier and date, and then populate the order_number in my data entry form as well as add the new record to the control table?
View RepliesI need to populate a table with Grid reference values consisting of 6 figure integers, eastings and northings.
I am receiving/downloading the Grid Refs in a UK Grid Tile format, with 3, 4 or 5 figures preceded by letters.
E.g. SK 456 849, SP 6789 4356.
I have used some query functions and written a bit of VBA behind a form to convert the received coordinates into the full coords I want. The coordinates are now in unbound controls on a form, and I want to pass these back to a table so I can use the data for display purposes.
Ideally I want to link to the table from a GIS and autoupdate without having to open the database (my imported data is linked in to feed the database automatically).
I have done some searching and not found a method to pass the values into a table - What is my best way of doing this automatically?
I have created a form that is based on a table.
The fields in the form are currently pull downs or manual enrty fields (all based on the columns of the table... "Bound" I think you call it).
I also have fields that I manually brought into my form as text boxes that I am using to populate a series of numbers. I have named them P1, P2, P3 etc. They are unbound fields.
I have a field that I am totalling the unbound fields. It is a column in my table. At one point the control source name was the table coulmn name.
I have changed the control source to read =[p1]+[p2]+[p3]+ etc.
The total does show up in the field on my form
HOWEVER.... When I save the data the total number does not get brought into the table. All of the other fields do.
I am creating a database to keep up with the expenses for my art business. I have a table for Vendors with a field (Mileage) for one-way mileage that I enter manually (EX: 11.8 miles). Then I have a calculated field that basically just multiplies the Mileage field times two to get total mileage to and from my house to vendor location. (Total Miles) {The reason I want to do this this way right now is because I didn't keep up with my mileage this year and I am having to look up the distance from my home to each location)
Then I have a "supplies" table where I show the date, the vendor, the item, cost, etc, and I want to add a Total Mileage field based on the calculated Total Miles field in the Vendors table that will automatically populate based on the vendor of each transaction. I just can't remember how to make this work and I know just enough SQL to be dangerous.
My query contains two calculated fields [TaxSavings1] and [TaxSavings2], which are based on some currency and number-type fields in one of my underlying tables.
I just created another field in my query which looks like: [TaxSavings1]+[TaxSavings2]. Instead of adding the two fields, it actually lumps the two numbers together. For example, if [TaxSavings1] =135 and [TaxSavings2]=30.25, it will give me: 13530.25. I need it just to simply add, i.e. answer of 165.25.
Does anyone know how to correct this? Thanks in advance.
:confused:
Within my table if Field 1 has an answer of Self (from drop down), then, I would like Fields 6-12 to auto populate; however, if Field 1 does not have an answer of Self, then leave Fields 6-12 blank.
I am not quite sure how to lay this out. I am using Access 2010.
Table1 contain Two fields (3Months) and (6Months)
Table2 contain Two fields (3Months) and (6Months)
the table 2 is the source of a form that will let the user change the numbers. table 1 should change Date1 and Date2 Fields based on the two fields (3months) and (6months) if i want to make a lookup wizard it should be changed manually and if i make a calculated field i can't find other tables in the expression builder
I'm creating a database that keeps a track of questions and scores.
The questions in the database need to be dynamic and are changed frequently.
I have a scorecard table which keeps a record of scores and the applicable question at the time the record was saved. I need to do this because in 6 months time we may want to provide feedback. As the question may have changed we need to be able to refer back to what the question was.
I want the question field in my scorecard table to populate with the value in my question table.
I have tried a number of things including setting the question field in the scorecard table to a lookup based on the following query:
SELECT tblQ1.Q1
FROM tblQ1;
This works however only as a list or combo box. I don't want the user to have to select the question. I want it to auto populate, is this possible?
I need to create a New Form control for this situation:
If I enter a date into a field and the choice for another field is equal to a certain value. How can I get the date I entered to be automatically populated into another date field.
For example:
If I enter 11/10/2005 in a date field and I choose either "BN", "BA", or "BT" in a text field, I need that date of 11/10/2005 to be automatically populated in another date field on the same form.
Any help is greatly appreciated.
I have a query with a Date field for EndDate (the dates for end-of-week, Fridays in our case) and another field for Sales (number of sales, not dollars).I want to add 4 calculated fields that represent weeks and have the Sales appear in the correct column (field) for that date.So I will have columns for 10 July 15, 17 July 15, 24 July 15 and 31 July 15 and I want the Sales for each record to land in the correct date column, based on the EndDate column. (The 4 fields is just for the sake of the example, I will actually be having dozens of these calculated date fields).I tried to do it by setting up the 4 calculated fields like:
10Jul15: Sales
and then adding Criteria like:
EndDate = #10/07/2015#
It doesnt work.
I use access 2010.
I am assuming by the error code, one can not use a calculated field to calculate another field.
I am wanting to populate a field by entering a date in another field. I am trying to determine age(years, months, and weeks) of something by entering a date in another field. Is that possible in Access?
View 1 Replies View RelatedI have a form with a main form and two subforms. The first subform automatically populates it's linked field with the data in the key field of the main form, but the second subform does not auto populate it's linked field with the data from the key field of the first subform. I have created main forms and subforms before and this was not a problem.
Is there a way to cause the second subform to automatically populate it's linked field with data from the key field of the first subform?
I need a continuity field...transaction number..that can be both a parent and child key. Would like to create this upon accessing a new record / row.
View 3 Replies View RelatedHow do I populate fieldA in formB with FieldB from FormA..
So Field is the last numeric field in Form A..so I need to the take the calculation value of that field and be the starting value in formB.
automatically populate the value of a field [Industry] in a table after a user has entered the value in another field [IndustryCode] in the same table. The [IndustryCode] and "Industry" are stored in a separated table [2012IndustryCodeTable]. I have tried using DLookup and cannot get it to work.
Ex.
|IndustryCode|Industry|InvestigationDate|
|001 |Car |
|002 |Plane |
How can i get the [Industry] to populate the information from the [2012IndustryCodeTable] once a user enters the code into the [IndustryCode] field in a table?
Hi,
I have a calculated field in a query that concatinates an order number prefix and the part number to give the order number:
OrderNum: [OrderPrefix] & [PartNum]
There are 2 tables:
tblPartsList
PartNum
PartDesc
tblPartsListCategories
OrderPrefix
OrderNumCategory
This works fine except for one of the categories. For the third category there are no entries in the tblPartsList - because they are miscellaneous.
How do I change the calculation so that it works as above where there are corresponding Part numbers in tblPartsList and otherwise gives me the order number and any 2 digits e.g. something like [OrderPrefix]*
Having been trying to do this for ages but having trouble with syntax.
hi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 2 Replies View RelatedI am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Mant Thanks
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
I am new to Access so please be gentle.
I heve started a new table and one of the fields is Date of Birth.
I want to have another field that looks at the data in DOB and calculate the age, I believe that this can be done with funtion datediff, but the layout doesnt look for other fields, it just looks for a date and the Now statement.
Any ideas on how to make this work? does it have to be a querie?
Many Thanks
I have a table with customers, one with products and one transaction table. How can i produce a report, based on a query that will add up the total any one particular customer has spent to date from the transaction table?
View 1 Replies View Relatedhi. i wanted to kno how to have a field in a table that is calculated from other fields if this is possible. for example i have a order table which has a quantity field and a price field and i want to add a total field that will alutomatically calculate the total (quantity*price) and add it to the field. i know how to do this in a query but don know how to get that field from the query into the table. thanks for any help
View 1 Replies View Relatedi have opened a field (which was created in a table) in a form as a calculated fieled
the calculations are ok but only in the for
the same field in the table remainded empty
how can it be set so the table's field will get the clcaulation ?
** here is a sample file
I have this query which selects two fields and calculates it in another field.
But the calculated field has more than 1 expression.Eg
If field A < Field B,Field B + Field C AS X,If field A < Field C,Field A + Field C AS Y.The problem is that when I show this on the Form I want only 1 of the fields to be shown ie either X or Y .if any of the expressions is satisfied then only that field should be shown as a textbox control on the form and not both.How do I do this?? Its really complicated for me..Pls help..
Hello,
Can someone please help with this expression?
I have a table with text field Cust_ID and a
Number field MCounter.
I would like to put in an expression as source in a form
Text field based on the same table to AGGREGATE
all MCounter values for a Given Cust_ID
Something like
Sum(MCounter) where "Cust_ID"=[Cust_ID]
Is this a correct expression?
Many thanks for any help.