Is there a way to take a value entered in a table to calculate a new value to populate a second field in the table? For example, if the user enters that the company has received $100 from a client, I need to show that $100 in the income field of the table, but then also calculate the commission based on a formula, and save that new commission value as well.
I was doing this in a query, but then the value doesn't get stored...
1. I want to populate a list box with all the Tables Names in my Database using VBA.
2. I also want to create another List Box or Grid (or any thing that will show table data) that when i have select a Table Name (See above (1)) it will show all the data in that table in the List Box. Was thinking some kind of SQL using vba to populate the list box?
I have two tables (Table 1, Table 2). Table 1 is blank and has the fields "Field1, Field2, and Field3." Table 2 has data and 10 different fields ("Field1, Field2", etc). I want to copy all data from the matching fields in Table 2 to the corresponding Table 1. I've tried an Update Query in design view. Did not join any records, and just a simple:
Field: Field 1 Table: Table 1 Update To: [Table2]![Field5]
This returns 0 records to update. I thought about a Make Table to override the entire Table, but that isn't working either.
I have a table called BID with the following fields
bidder, seller, iid, starttime, bidtime, and bidprice
bidder is an id number that is reference to uid of table USER
the USER table have the following fields,
uid, uname, city, and state
I created a BID form for the user to enter data into the BID table. What I am trying to do but is having trouble with is have the user search for their name that may already be store in the uname field in the user table, and what ever they choose base on their search, the uid of this uname gets store into the bidder field of the bid table.
I still have a second step where the user in the bid form can search the iid but shows the itemname from the item table, and the value of seller, iid, and starttime from the auction table gets populated into the bid table.
iid from the auction table is reference to the itemtype and the following fields exist in the itemtype table.
1. I have a table which lists all of the equipment we use in our company and would like to use it as a template in a form so that users can select a checkmark if the equipment is being used that day. The checked items would then be used to populate another table which records all of the equipment the user has selected. I thought the checkmark method would be best so that the user can scroll through the list of equipment and select multiple pieces for the day. The reason I don't use a multi-value field is so that the user can also enter quantity and hours of equipment used in the same table when they scroll through the list.
2. Once all of the equipment pieces have been checked, I would like the user to be able to click a review button which would navigate to the next form with all of the selections for the day. This new form will have the option to go back to the first form if any corrections need to be made. And also have the option to submit the results.
3. Once the user is finished with their selections, the user can then select Submit which will finalize the recorded equipment table and clear the checkboxes and any inputs from the equipment template table (without modifying anything) so that it can be used for the next day and so forth.
Hi all - I have a table that is keeping track of projects we are working on. There is a field with the overall cost of the project and a field with the percent paid. I would like a field that calculates that amount still owed. Is this possible?
Hi all. I have a TableA with 3 fields: FieldA, FieldB, Result. A Form1 based on these fields. User will put data in FieldA, FieldB and calculate in Result. Problem is how to keep result of calculation in TableA? Thanks
First off, I am not sure if this belongs in the queries forum but it seems most of the question is geared towards a table so I have posted it here.
At the moment I have a query that contains several fields that use nested IIf statements to determine which set of rules to apply. I was wondering if it is possible to somehow set the IIf variables up in a table and then for just to lookup the table in the query.
For example, one of my simpler IIf's looks like this...
Is it posible to perform calculations within a table or data entry form. I have a date of birth field and an age field. I have an Update Action Query that automatically works out the date, (See below). However if I design the form with a close form event that triggers the query I get the confirmation message. I can only supress all Action queries confirmation messages or none at all and I do not want to disable this. Therefore I would like the age to be calculated within the table, (or form if that was possible). However I do not know how to do it.
I'm currently using a Dlookup function to display a calculated field from a query. This works fine when only one record is entered on the many side; however when additional records are entered it doesn't add the additional records. What function/code can I use to add each record's total as it's entered on the many side?
This is what is working when the many side only has one record:
I am trying to build a Fitness tracking database, where i am trying to input a calculated item. there are two inputs and i want an out put, i put in Height and Weight, and then i have a TBL that has Height then the maximum weight that applies to that height...
so TXT BOX 1 = HEIGHT TXT BOX 2 = current Weight TXT box 3 = MAX Weight
my tbl looks like this
58" 131lbs 59" 136lbs 60" 141lbs 61" 145lbs
What im trying to do is have the form read automaticaly what i put in TXT BOX 1 and enter in automatically the MAX weight for that height.
I have one database containing several tables, two of which are associated to this inquiry:
1) tblCategories Contains two columns: CatID and Category Name
2) tblPending Contains many columns of data obtained through upload from a web form including a column to capture the CatID and also a column for Category Name. I wish to populate the Category Name column based on the results of data uploaded into CatID column
How is this accomplished? This is inherited project and I have very little experience with Access so just limping along... Specific instruction much appreciated.
hi - i have tried search for the solution to my query - but to no avail, so here goes:
I have a query which is based on more than one table. In the query, I have specified a calculation, eg. Final Price: SUM([Sale Price] - [Discount]) From this query I have a form, just showing everything. Details are put into the form, and viola, they appear in the query if checked. However, they do not appear in the table My assumption why this is not happening was because the Final Price is no longer "record source"d from the original table. How can I combat this so that it does appear in the table? Thank you (sorry if it is easy - but i dont have a clue!)
Hi all, I have a problem in returning values from 2 different tables because they are not related. Let me explain:
I'm trying to do "Payment Due" query by substracting the amount in the "Cost" table with the amount in the "Payment" table ([CostAmount]-[PaymentAmount]). However, since no payment has been made, the table contains no related record.
At first I thought the problem lies in null values the table return hence I tried to use NZ function to convert null to zeros. Then I realised that no values has been returned from the table due to no related record available.
Hi, I am wondering If I can Sum the value of rows in one field of my Query Table and Inserting that Calculated value into the bottom of the summed Row in the VBA or through Query Design View.
Please let me know, Thank YOU!
Below I have attached the picture of the data that Im trying to sum and insert into a new row that Hopefully can be created through MS access Query.
My Sales! table has fields [ItemType] and [Price] and VAT! table has [ItemType] and [VATRate].
Both tables has a common field which is [ItemType].
I am trying to make a query which will calculate the VAT figure by multiplying the Sales table [Price] with the corresponding [VATRate] in the VAT table by matching the [ItemType] in both tables.
I tried DLookup but couldn’t find a solution. How do I accomplish this?
I've read through the forums on saving the results of a calculation into a field is a bad idea. I somewhat understand the reasoning for it. But I don't think such situations arise for everyone.
But I have gone with the suggestion and created an updatequery, which simply goes through and updates the calculated value into a table. Now each time the updatequery is run its asking for permission as to the fact if I'm sure I want it to be run. How do I turn this option off. And should I call this updatequery afterupdate or beforeupdate?
Hello all, I'm quite new to Access. I've read many Access tutorials and site but I have yet to found the solution for my problem. So here it is (pardon my english):
Field AmountTotal is =(Nz([AmountSubTotal])+Nz([AmountSH])-Nz([Discounts]))
I manually input AmountSubTotal, AmountSH and Discounts. I expected an automatic calculation for AmountTotal.
In "Form view" I get the result of AmountTotal that I wanted. But the calculation result doesn't get recorded in my field data table AmountTotal. It just shows blank.
How do I record this automated results into my original table?
I am sure that in earlier versions it was easy to write the result of a calculated field on a form, back to a table, but I can't find the method in the help file for the current version of Access.
I have a [DOB] field with birth dates. I have another field [DOBExp] where I want to automatically calculate at date 15 days out, but exclude weekends and holidays. I have a separate table listing the holidays.
Would like a formula to use in my first table for this. Have no clue how to do this.
I shouldn't normally record calculated figures into my table. I'm willing to buck proper procedure here for ease of use for referencing in reports; also, it's just one data point that will be calculated only once and not changed.Anyhow, I have an IIF statement that calculates a total price based on a table of costs. I set this as the default value and it calculated properly - but once I set my text box to have a control source, it no longer calculates my cost.
I was hoping to set it so it calculated the cost and then write that total cost to my table.should mention that my calculated control is in a subform.
Access 2003. I am trying to save a calculated field (Item No) from a form to the accosiated field within the Products table
I have a hidden text box (itemNo) which is bound to the relevant field and a second text box with the following formula
="TV-" & Left(CatShort.column(1),3) & "-" & Format([ProductID],"0000") as its source
What I am trying to do is to provide an item number for each item of stock based on the Category short name (Catshort) and the incremented item ID Number, with a TV prefix.
I am aware that its a bad itea to place calculated field into the table if the calculation is likely to change, however once my calculation is stored it has no need to change.
I have tried the beforeUpdate method within the ItemNo textbox
Private Sub ItemNo_BeforeUpdate(Cancel As Integer) Me!ItemNo = Me!ItemCalc End Sub
but nothing happens (in that the table is not updated)
Any way of putting together an incremental item number based on the selected category with a "TV" prefix....