MS Access query trouble

Question

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.

GROUP BY [Customers].Customer_ID suggests that Customer_ID is not unique in the Customers table. Is that really true? — onedaywhen
– onedaywhen, Commented Aug 16, 2011 at 14:02

ypercubeᵀᴹ · Accepted Answer · 2011-08-16 19:46:25Z

4

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

edited Aug 16, 2011 at 19:46

answered Aug 16, 2011 at 13:06

ypercubeᵀᴹ

116k19 gold badges181 silver badges249 bronze badges

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

1 Comment

onedaywhen Over a year ago

For your outer join suggestion presumably COUNT(*) should be changed to COUNT(Orders.Customer_ID), otherwise a customer with zero orders would have the same count as a customer with one order.

digi · Accepted Answer · 2011-08-16 13:13:45Z

0

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

answered Aug 16, 2011 at 13:13

digi

2,7891 gold badge15 silver badges18 bronze badges

Collectives™ on Stack Overflow

MS Access query trouble

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related