Need help with Access Query
Experts, can you please help me with this. I need a query to produce the IDTPartNumber and Description from the Equipment Table. And then I need the sum of Qty from the SalesEquipment table for the IDTPartNumber. And I need to find these based on year. So SalesDate Between #1/1/2014# and #7/25/2014#.
Helen Feddema
It looks as if there should be a many-to-many relationship here, with SalesEquipment as the junction table. It would be best to set up this relationship, to ensure referential integrity. Can you post the database (or a cut-down version of it)? I would like to have the actual tables to work with, but it might be possible to just use the appropriate fields from all three tables in a query, or if that doesn't work, make a date-limited query based on the Sales table, and link that to the other two tables to get the fields you need. Then either do a Sum on Qty in that table, or save that query as a select query, and make a totals query based on it to get the sum.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SELECT
e.IDTPartNumber
,MAX(e.Description) [Part Description]
,SUM(se.QTY) [Total Quantity]
FROM
Equipment e
JOIN
SalesEquioment se
ON se.EquipmentID = e.ID
JOIN
Sales s
ON s.ID = se.SaleID
WHERE s. SalesDate Between #1/1/2014# and #7/25/2014#
GROUP BY
e.IDTPartNumber
e.IDTPartNumber
,MAX(e.Description) [Part Description]
,SUM(se.QTY) [Total Quantity]
FROM
Equipment e
JOIN
SalesEquioment se
ON se.EquipmentID = e.ID
JOIN
Sales s
ON s.ID = se.SaleID
WHERE s. SalesDate Between #1/1/2014# and #7/25/2014#
GROUP BY
e.IDTPartNumber
try this query
SELECT Sales.SalesDate, Equipment.IDTPartNumber
FROM (Sales INNER JOIN SalesEquipment ON Sales.ID = SalesEquipment.SaleID) INNER JOIN Equipment ON SalesEquipment.EquipmentID
WHERE Sales.SalesDate Between #1/1/2014# And #7/25/2014#
SELECT Sales.SalesDate, Equipment.IDTPartNumber
FROM (Sales INNER JOIN SalesEquipment ON Sales.ID = SalesEquipment.SaleID) INNER JOIN Equipment ON SalesEquipment.EquipmentID
WHERE Sales.SalesDate Between #1/1/2014# And #7/25/2014#
oops, sorry i did not see the SUM part...
ASKER
Perfect Instructions Thanks. I got errors on the other suggested solutions.