Query Issue

Question

Hello...I have two tables. tblFioriWOList contains a Field named "Order" that can have duplicates. It also contains a Field "Priority" (that can contain the following values: 1, 2, 3, 4, 5, C or is Null). tblFioriWOListCost contains a Field also named "Order" that can/will have duplicates. Table 2 also contains a Field named "Cost Element" and another named "Total Quantity". I am trying to create a query (or multiple queries) to first group the Cost Element, secondly group the Priority into sub groups (i.e., Group 123, Group 345C) then count the number of Orders by Priority sub group and finally sum the Total Quantity by the Priority sub groups Groups. Following is an example of the output I wish to obtain. I have provided a query I have tried but can't seem to achieve what I have shown as the example.

tblFioriWOList

tblFioriWOListCost

Answer 1

Your query appears to be joining two tables on columns neither of which is a candidate key of its table. A join would normally be on a primary key (or a candidate key at least) in the referenced table, and a corresponding foreign key in the referencing table. The former will have distinct values. To do otherwise will produce numerous cross matches leading to erroneous results.

The following is a simple example:

SELECT [FirstName] & " " & [LastName] AS Customer,
COUNT(*) AS TransactionCount,
SUM(TransactionAmount) AS TotalAmount,
AVG(TransactionAmount) AS AverageAmount
FROM Customers INNER JOIN Transactions
ON Customers.CustomerID = Transactions.CustomerID
GROUP BY Customers.CustomerID, [FirstName] & " " & [LastName],
LastName, FirstName
ORDER BY LastName, FirstName;

This taken from my DatabaseBasics.zip demo which you'll find in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

The query returns the total number of transactions, the total amount of all transactions, and the average transaction amount per customer.