Feprincess
Apr 20, 2007, 12:55 PM
I'm setting up a Query in Design View and I need to find out the price per unit.
I need to mulitply two fields, Amount and Quantity with the Answer in the Amount Field. What wording should I use?
The two fields are from two different Tables.

ScottGem
Apr 20, 2007, 01:02 PM

This doesn't quite make sense. Generally you would be dealing with 2 tables here; Product and OrderDetails. They would look like this:

TblProduct
ProductID
Product
UnitPrice

TblOrderDetails
OrderDetailID
ProductID
Quantity

You would then calculate the extended price in your query using a column like:

ExtPrice: tblOrderDetails.Quantity * tblProduct.UnitPrice

However, it would be better to store the UnitPrice in the Detail record so, when the price changes the Detail record will reflect the price at time of order.

Depressed in MO
Apr 20, 2007, 01:32 PM
This doesn't quite make sense. Generally you would be dealing with 2 tables here; Product and OrderDetails. They would look like this:

TblProduct
ProductID
Product
UnitPrice

TblOrderDetails
OrderDetailID
ProductID
Quantity

You would then calculate the extended price in your query using a column like:

ExtPrice: tblOrderDetails.Quantity * tblProduct.UnitPrice

However, it would be better to store the UnitPrice in the Detail record so, when the price changes the Detail record will reflect the price at time of order.

Wouldn't she have to match a field between these two tables, such as Product ID (for ex.)?

ScottGem
Apr 20, 2007, 05:37 PM
Yes. She would need to create a join between the ProductID fields.

Feprincess
Apr 21, 2007, 11:22 AM
I created a field called 'Total' and used an equation like this.
[Amount]*[Quatntitiy] it worked this way.
I was trying to run the calculation in the criteria row.
Obviously, I learning Access right now. I'm talking an online class, so when I run into a problem I have to go to the internet.