Access Query from multiple tables

Question

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!

Cetin Basoz · Accepted Answer · 2018-05-10 11:55:50Z

1

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;

answered May 10, 2018 at 11:55

Cetin Basoz

24k3 gold badges34 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Ans Over a year ago

This works but is bringing me all the clients even if they don't have any orders made. Would it be possible for it to bring me only the clients that have orders? I have seen in other places where they say that ms-access doesn't support the count distinct function.

Cetin Basoz Over a year ago

You don't need distinct here. You can simply add a where clause to filter out clients with no orders: ... from Client c where exists (select * from Order where c.ClientId = Order.FKClientID)

Ans Over a year ago

I would like to make this query into a report and be able to filter by the timestamp date so that it brings me orders made from x date to y date. Wouldnt the where clause cause conflict?

Cetin Basoz Over a year ago

@Ans, nope it wouldn't cause conflict you can add the clause in your where with "and" - remember you may need to add that to multiple places based on your needs (you may even might want to base your on Order rather than Client).

Ans Over a year ago

Thanks for all the help so far. Could you guide me as how I could add the field timestamp and OrderStatus to the query so I could later filter by it on the report. I have some trouble understanding the syntax of how to add fields to complex queries when they have nested selects etc.

|

Gordon Linoff · Accepted Answer · 2018-05-10 11:29:00Z

0

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;

answered May 10, 2018 at 11:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Ans Over a year ago

Your solution brought only those clients that had Orders made which was what I was looking for. Quick question is there any way I could add the total amount of dollars that the client has spent overall for all of his orders. I know I can do something like (od.Quantity * od.Cost) and that would give me the total spent on an order but not total spent on all orders for a client.

Ans Over a year ago

I managed to get the total spent on all orders by adding the following: SELECT c.[Name], COUNT() As NumOrders, SUM(o.NumItems) As NumItems, Sum(o.TotalCost) AS TOTALCOST FROM Client as c INNER JOIN (SELECT o.OrderID, o.FKClientID, COUNT() As NumItems, sum([quantity]*[cost]) AS TotalCost 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;

LM358 · Accepted Answer · 2018-05-10 15:04:11Z

0

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];

edited May 10, 2018 at 15:04

answered May 10, 2018 at 9:50

LM358

1411 silver badge9 bronze badges

Comments

Cetin Basoz · Accepted Answer · 2018-05-10 16:41:22Z

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.

Collectives™ on Stack Overflow

Access Query from multiple tables

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

6 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related