Generate a demand report

[CoffeeGuru]

Hi I am trying to run a query over a single table that will show me how many stores have a range of products in stock

Access does not have a distinct count and googling has not helped much, maybe its me.

My table looks something like this:

tblSales
StoreNo UID Stock
11112 T123 13
11112 T124 10
11112 T125 5
11112 T126 0
22222 T123 11
22222 T125 1
22227 T123 3
22227 T125 22


My Qry should show
UID Stores
T123 3
T124 1
T125 3

T126 is not shown as it has <1 in stock

can anyone help me

 

[namliam]

No access doesnt have a "Count(distinct)" function, unfortunately.

Only way around it is to query a query, make one query to group on one thing first, then another to count your first query....

Though in this case I do not really see the need in your sample.... Something along the lines of....

SELECT Article, Count(StoreNo) AS Stores
FROM YourTable
WHERE Stock>0
GROUP BY Article

 

[CoffeeGuru]

in this case I do not really see the need in your sample

Well I dont understand that, You are quite correct. it must have been later than I thought last night as I could not get it to work.

Thanks for your help