Count Distinct

[accessprogramer]

I'm stuck... I have 2 tables linked with a 1 to many relationship by NAME. Tables look like this....

TBL-STORE

NAME ADDRESS
Walmart 12 Nowhere
Target 14 Somewhere
Lowes 10 Anywhere
Sears 16 Nowhere

TBL-PURCHASES

NAME [NUMBER OF ITEMS] [PURCHASE DATE]
Walmart 4 1/4/14
Walmart 2 1/5/14
Target 0 1/5/14
Lowes 3 1/5/14
Sears 1 1/5/14
Sears -1 (returned to store) 1/6/14


I want a DISTINCT COUNT of [NAME] if Total[NUMBER OF ITEMS]>0, so the correct number will be 2 in the example.

This is one way I've tried to write the SQL, but I keep getting an error...

SELECT Count([TBL-STORE].[NAME])
FROM [TBL-STORE] INNER JOIN [TBL-PURCHASES] ON [TBL-STORE].[NAME] = [TBL-PURCHASES].[NAME]
WHERE ([TBL-PURCHASES].[NUMBER OF ITEMS]>1)
HAVING ([TBL-STORE].[NAME])="DISTINCT");

I also tried an Count(IiF( and can't get that to work either...

Any ideas? THANKS!

 

[plog]

First, STORE is a red herring--this table is not necessary in determining what you need. Also, this is going to take a sub-query because you need the aggregate results of an aggregate query. Before that though, I advise some cleanup of your tables:

1. You shouldn't link tables by text fields. Each of the records in STORE should have an autonumber primary key (StoreID). Then, instead of a NAME field in PURCHASES, you would have a foreign key to that primary key (StoreID).

2. NAME is a poor choice for a field (or table for that matter) because it is a reserved word in Access and can make coding and writing queries more difficult. You should instead prefix it with what the name is of (i.e. 'StoreName').

3. When naming objects (fields, tables, forms, etc.) you should only use underscores and alphanumeric characters. Spaces and other characters make coding and writing queries more difficult. You should remove the spaces from your field names.

Now, for that subquery. You should build a query on Purchases to get the sum of all purchases of a store. Using my new fields I outlined above, this would be that query:

SELECT StoreID, SUM(NumberOfItems) AS TotalItems
FROM PURCHASES
GROUP BY StoreID
HAVING SUM(NumberOfItems)>0;

Then you use that query in another query where you simply count the records in the one above.

 

[jdraw]

In addition to plog's advice and recommendations, I would suggest you work through this tutorial to better understand table design, keys (primary and foreign) and relationships.

 

[accessprogramer]

Thanks so much for your suggestions! Works beautifully!