If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Problem with multiplying value of two fields from different tables
I am trying to multiply two fields that are in a different table and show the
result in my orders form. The first Table is called Orders and has the following fields: Customer ID Order Number Item Number Date Quantity The Items Table consists of: Item Number Item Description Price Quantity Available In my Orders Form, I have the same as in the Items table including Totals. In the totals field i set control source: = [Items]![Price]*[Quantity] As a result i get this in the totals field: #Name? I set the Price field in the Items Table to Currency & Quantity Field in the Orders Table to: Number I am a newbie in MS Access, and i am currenly working on an assignment for my school. v/r David |
#2
|
|||
|
|||
Problem with multiplying value of two fields from different tables
Create a query that uses both tables.
Use this query as the RecordSource for your form. You can then get the values from both tables into your form. The approach you are using bothers me, though. Is there any chance that one of the items might change price in the future? If you do change the price, won't that mess up all your existing orders? For that reason, you might consider including the UnitPrice in the Orders table. You can use the AfterUpdate event procedure of the [Item Number] to look up the current price for you. The Northwind sample database has an example of how to do that (in the Orders Subform.) Another suggestion: Could one customer order contain multiple items? If so, see how Northwind handles that by using an Order Details table so one order can have many rows. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "dwal" wrote in message ... I am trying to multiply two fields that are in a different table and show the result in my orders form. The first Table is called Orders and has the following fields: Customer ID Order Number Item Number Date Quantity The Items Table consists of: Item Number Item Description Price Quantity Available In my Orders Form, I have the same as in the Items table including Totals. In the totals field i set control source: = [Items]![Price]*[Quantity] As a result i get this in the totals field: #Name? I set the Price field in the Items Table to Currency & Quantity Field in the Orders Table to: Number I am a newbie in MS Access, and i am currenly working on an assignment for my school. |
#3
|
|||
|
|||
Problem with multiplying value of two fields from different tables
"dwal" wrote in message ... I am trying to multiply two fields that are in a different table and show the result in my orders form. The first Table is called Orders and has the following fields: Customer ID Order Number Item Number Date Quantity The Items Table consists of: Item Number Item Description Price Quantity Available In my Orders Form, I have the same as in the Items table including Totals. In the totals field i set control source: = [Items]![Price]*[Quantity] As a result i get this in the totals field: #Name? Should it not be [Items]![Price]*[Orders]![Quantity] ??? |
#4
|
|||
|
|||
Problem with multiplying value of two fields from different tables
"Homer J Simpson" wrote in
news:0aEJj.34732$_v3.4294@edtnps90: "dwal" wrote in message ... I am trying to multiply two fields that are in a different table and show the result in my orders form. The first Table is called Orders and has the following fields: Customer ID Order Number Item Number Date Quantity The Items Table consists of: Item Number Item Description Price Quantity Available In my Orders Form, I have the same as in the Items table including Totals. In the totals field i set control source: = [Items]![Price]*[Quantity] As a result i get this in the totals field: #Name? Should it not be [Items]![Price]*[Orders]![Quantity] ??? Prefixing with the table name is only required when there is ambiguity in which field is addressed. In a query with the above two tables, [Item Number] appears in both so Access must be told which of the fields the programmer wants to use. If there is no ambiguity, because the field name is only in one table, Access doesn't need the table's name. However, in this case, I suspect that the original poster built the form on the Orders table only and is trying to reference the value from a table that's not in the recordsource. Both tables should be in the recordsource, using a query, or the value could be referenced using a dLookup() call -- Bob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com |
#5
|
|||
|
|||
Problem with multiplying value of two fields from different ta
First of all, I would like to thank for the quick response. I applogize that
I was not able to answer earlier. The Northwind Example is helping me alot in understanding how it all works. When you first said Northwind I had no clue what you were talking about but thanks to Google and MS Help I was able to figure out what you meant To Bob: Yes, you are correct. I did try tp built the form on the Orders table. V/r David |
Thread Tools | |
Display Modes | |
|
|