CODEWITHSUNDEEP
sqlms-accessms-access-2007
jerry
jerry

Reputation: 2789

MS Access query trouble

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

Answers (2)

ypercubeᵀᴹ
ypercubeᵀᴹ

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

digi
digi

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

Related Questions