Access Query from multiple tables

0

votes

I currently have an Access Database with a few tables, among them Order, OrderDetails and Client.

Order (OrderID, TimeStamp, FKEmployeeID, FKClientID, OrderStatus, Comments)
OrderDetails (OrderDetailsID, FKOrderID, FKProductID, Quantity, Cost, Total Cost)
Client (ClientID, Name)

I'm trying to build a query where I can get the total orders that a client has made and the total Items.

Example:

Customer,  Total Orders,  Total Items
John,          5,              15
Alex,          2,              30
Ana,           1,              3

Whenever I try to make a query Total Orders and Total Items give me the same number.

Any help would be greatly appreciated!

sqlms-access

1

votes

This is supported even by ms access:

SELECT c.Name,  
(select count(*) 
 from Orders o 
 where o. FKClientID = C.ClientID) as [Total Orders],
(select sum(Quantity) as Items 
 from OrderDetails od 
 inner join Order o on o.OrderID = od.FKOrderID
 where o.FKClientId = C.ClientID) as [Total Items]
from Client c;

0

votes

Unfortunately, MS Access doesn't support COUNT(DISTINCT). You can do this with two aggregations:

SELECT c.[Name], COUNT(*) As NumOrders, SUM(o.NumItems) As NumItems
FROM Client as c INNER JOIN
     (SELECT o.OrderID, o.FKClientID, COUNT(*) As NumItems 
      FROM [Order] as o INNER JOIN
           OrderDetails as od
           ON od.FKOrderID = o.OrderID
      GROUP BY o.OrderID, o.FKClientID
     ) as o
     ON o.FKClientID = c.ClientId
GROUP BY c.ClientId, c.Name;

0

votes

What about this solution (clients without orders are left out hereby):

SELECT Client.[Name], 
       Count(myTotalItems.OrderID) As TotalOrders, 
       Sum(myTotalItems.TotalItems) As TotalItems
FROM Client,
    (SELECT First([Order].OrderID) As OrderID, 
            First([Order].FKClientID) As 
            ClientID, 
            Count(OrderDetails.OrderDetailsID) As TotalItems 
     FROM [Order], OrderDetails 
     WHERE OrderDetails.FKOrderID like [Order].OrderID
     GROUP BY [Order].OrderID) As myTotalItems 
WHERE myTotalItems.ClientID like Client.ClientID 
GROUP BY Client.[Name];

0

votes

It would be easier for you if you divide the task into multiple queries. I am giving the sample using Northwind database, you can test and see on it. Note that the structures and fieldnames are very similar to yours.

First create one that gets ClientId, OrderId, OrderDate and Sum of Quantity.

SELECT c.CustomerId, o.OrderId, o.OrderDate, sum(od.Quantity) AS Qty
FROM (Customers AS c INNER JOIN Orders AS o ON c.CustomerId = o.CustomerId) 
INNER JOIN [Order Details] AS od ON o.OrderId = od.OrderID
GROUP BY c.CustomerId, o.OrderId, o.OrderDate;

Save this as "OrderOfClients" (it would be saved in Queries). Next create a query that uses this one and asks for date range:

SELECT c.CustomerId, c.CompanyName, 
   Count(*) AS [Total Orders], 
   Sum(Qty) AS [Total Items]
FROM Customers AS c 
INNER JOIN OrdersOfClients AS co ON c.CustomerId = co.CustomerId
WHERE co.OrderDate Between [@startDate] And [@endDate]
GROUP BY c.CustomerId, c.CompanyName;

You can save this one as "OrdersOfClientsSummary" and call for your report.

PS: In my personal opinion, if you use a database other than access you would be doing yourself a big favor.

Access Query from multiple tables

4 Answers