I have three tables.
“Departments” table: department_name, department_id.
Table “LowPrice”: departmend_id, product_name, purchased, sold, price.
Table “Basic”: departmend_id, product_name, purchased, sold, price.
In the tables “Basic” and “Low” there can be several records pertaining to one department.
There is no relations between these tables. I want to develop a query that will group records from the Low and Basic tables by the department number from the Departments table. When grouping, the cost of sold and purchased goods from the “Basic” and “Low price” tables for each department is summed up.
My query code:
SELECT Departments.departmend_id, Sum([Basic]![purchased] [Basic]![price]) AS PurchasedBasic Sum([Basic]![sold] [Basic]![price]) AS SoldBasic, Sum([Basic]![purchased] [Basic]![price]) - Sum([Basic]![sold][Basic]![price]) AS DiffBasic, Sum([LowPrice]![purchased] [LowPrice]![price]) AS PurchasedLowPrice, Sum([LowPrice]![sold] [LowPrice]![price]) AS SoldLowPrice, Sum([LowPrice]![purchased] [LowPrice]![price]) - Sum([LowPrice]![sold] [LowPrice]![price]) AS DiffLowPrice FROM (Departments LEFT JOIN Basic ON Departments.departmend_id = Basic.departmend_id) LEFT JOIN LowPrice ON Departments.departmend_id = LowPrice.departmend_id GROUPBY Departments.departmend_id;
But now only those departments for which there are records in only one table are counted correctly. If there are records for a department in two tables, then their number is multiplied and the query gives an incorrect result.
Please enlighten me what I need to change in my query.
Advertisement
Answer
Consider combining LowPrice
and BasicPrice
into one table since qualitatively they contain the same information. Simply, add a Type field to designate low price and basic price records.
Make-Table Query (to be run once)
SELECT p.* INTO Prices FROM (SELECT b.department_id, b.product_name, b.purchased, b.sold, b.price, 'Basic Price' AS type FROM BasicPrice b UNION ALL SELECT l.department_id, l.product_name, l.purchased, l.sold, l.price, 'Low Price' AS type FROM LowPrice l ) p
Once your properly normalized those two relations for more efficient storage, run conditional aggregation. Below uses table aliases and calculated columns which is supported in MS Access.
Conditional Aggregation Query
SELECT d.departmend_id, d.department_name, SUM(IIF(p.Type = 'Basic Price', p.[purchased] * p.[price], NULL)) AS PurchasedBasic, SUM(IIF(p.Type = 'Basic Price', p.[sold] * p.[price], NULL)) AS SoldBasic, [PurchasedBasic] - [SoldBasic] AS DiffBasic, SUM(IIF(p.Type = 'Low Price', p.[purchased] * p.[price], NULL)) AS PurchasedLowPrice, SUM(IIF(p.Type = 'Low Price', p.[sold] * p.[price], NULL)) AS SoldLowPrice, [PurchasedLowPrice] - [SoldLowPrice] AS DiffLowPrice FROM Departments d LEFT JOIN Prices p ON d.departmend_id = p.departmend_id GROUP BY d.departmend_id, d.department_name;