Reputation: 2789
I am trying to count how many products are associated with each of our female customers so I can use it in a later calculation.
SELECT [Customers].Customer_ID
FROM [Customers]
WHERE ((([Customers].Gender)='Female'))
GROUP BY [Customers].Customer_ID;
The above SQL gives me a list of ID's for our female customers. Now, I guess in a separate query, I need to count the number of Product ID's for each of the ID's in the above list, but I don't know where to go from here. I don't know if I can combine this step into the above query, or if I need to do something else.
I am thinking I need something like this:
DCount("Order_ID", "Orders", "Customer_ID = [**how do I make it to where
this equals the resulting Customer_ID's from the above query**]" )
Can someone please help? Am I on the right track?
If you need more info, please let me know.
Upvotes: 0
Views: 116
Reputation: 115630
Something like:
SELECT [Customers].Customer_ID
, COUNT(*) AS NumOrders
FROM [Customers]
INNER JOIN [Orders]
ON [Orders].Customer_ID = [Customers].Customer_ID
WHERE ([Customers].Gender = 'Female')
GROUP BY [Customers].Customer_ID;
The INNER JOIN
should be changed into LEFT JOIN
to include Customers without any Orders (and COUNT(*)
should be changed to COUNT(Orders.Customer_ID)
as @onedaywhen spotted):
SELECT [Customers].Customer_ID
, COUNT(Orders.Customer_ID) AS NumOrders
FROM [Customers]
LEFT JOIN [Orders]
ON [Orders].Customer_ID = [Customers].Customer_ID
WHERE ([Customers].Gender = 'Female')
GROUP BY [Customers].Customer_ID;
Note, that the above queries will give you number of Orders and not number of Products as you describe. You'll have to provide the structure of the tables (probably Orders
, or OrderDetails
if you have such a table) so we know where the information about products (and their orders) is stored.
Sidenote: as (again, deserving more than half of these points :) @onedaywhen spotted, the Customer_ID
is supposed to be the PRIMARY KEY
of table [Customers]
. If that's the case (or if it is UNIQUE
), your original query could be simplified:
SELECT [Customers].Customer_ID
FROM [Customers]
WHERE [Customers].Gender = 'Female' ;
Upvotes: 4
Reputation: 2769
Try this.
Haven't tested it since I don't got MS Access on this PC. Similar stuff work in MySQL and MS SQL so it might work :)
SELECT SUM(O.Order_ID), C.Customer_ID
FROM [Orders] AS O, [Customers] AS C
WHERE C.Customer_ID IN (
SELECT [Customers].Customer_ID
FROM [Customers]
WHERE ((([Customers].Gender)='Female'))
GROUP BY [Customers].Customer_ID);
Upvotes: 0