creating query- I'm stuck

[mkathyf]

this is an assignment, and I am stuck!
I need to create a query to determine the total dollar amount of all products sold to 3 specific stores....using three tables: Customers, Shipments and Products.
From Customer table list: StoreID. Chain. displaying the datat ONLY from stores 102, 103 and 110. (there are several other stores) From Shipment table list: Ship_Date. From Products table list: $/Case. nd, again from Shipment table list: Quantity.
To the right of "Quantity" field, I am to create a calculated field named "Total Quantity (calculated by multiplying the $/Case field by the Quantity field.

OK....first, it keeps telling me I have an incorrect syntax.
How do I need to write this?

Second, how do I write the criteria to show I only want those three stores in the calcuation?

I worked on this for an hour and can't seem to get beyond it!
I have brought down each of these

 

[WIS]

this is an assignment, and I am stuck!
I need to create a query to determine the total dollar amount of all products sold to 3 specific stores....using three tables: Customers, Shipments and Products.
From Customer table list: StoreID. Chain. displaying the datat ONLY from stores 102, 103 and 110. (there are several other stores) From Shipment table list: Ship_Date. From Products table list: $/Case. nd, again from Shipment table list: Quantity.
To the right of "Quantity" field, I am to create a calculated field named "Total Quantity (calculated by multiplying the $/Case field by the Quantity field.

OK....first, it keeps telling me I have an incorrect syntax.
How do I need to write this?

Second, how do I write the criteria to show I only want those three stores in the calcuation?

I worked on this for an hour and can't seem to get beyond it!
I have brought down each of these

Is StoreID in all 3 tbls. "$/Case" means $ divided by Case - use "$_Case" as a fld name. At the moment, what are you using to join the tbls?

 

[obiron]

Tables:

Customer:
CustomerID (unique)
CustomerName

Product:
ProductID (unique)
ProductName
Price_per_case

Shipment:
ShipmentID (unique)
ShipmentDate
CustomerID


ShipmentLines:
LineID (unique)
ShipmentID
ShipmentLineNumber - used to determine the sort order for the shipment
ProductID
Cases
(note.. This is assuming that the price per case is the same for all customers and never changes. If it does you would also need to store the case price on the shipment record)

To get shipment line totals

SELECT shipment!ShipmentID,ShipmentLine!ProductID,
  (ShipmentLine!Cases * Product!Price_Per_Case) as "Product_Total" 
FROM Shipment 
JOIN ShipmentLine on ShipmentLine!ShipmentID = Shipment!ShipmentID 
JOIN Product on Product!ProductID = ShipmentLine!ProductID

This will give you all of the shipment line totals.

From there you should now be able to figure out how to filter (WHERE) for the customers you want (join customers on CustomerID) group by CustomerID and sum on Product_Total.

You can do it all in one query, but it is easier to understand as two.

If you are still stuck, Post back and we will see if we can get you and A+

Obiron