Design a Query to Count 1

[SanAntonioSpurFan]

I have a table called Scan Sales that contains the following fields: UPC, StoreID, WeekID, Units Sold

I want to create a query that will count the number of weeks a UPC has sold and also count the number of stores the upc sold in.

Data looks like this:
UPC, StoreID, WeekID, Units
001, 50, 19000, 5
001, 50, 19001, 2
001, 50, 19002, 3
001, 75, 19000, 7
001, 75, 19001, 3
001, 75, 19002, 5

I would want the query to produce the following:
UPC, Store Count, Weeks Scaned, Units Sold
001, 2, 3, 25

 

[scriverb]

Give this SQL a try:

Select A.UPC, Count(A.StoreID) as StoreCount, Count(A.WeekID) as WeeksScanned, Sum(A.[Units Sold]) as UnitsSold 
FROM [Scan Sales] as A 
Group By A.UPC 
Order By A.UPC;

Post back if you have any questions.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SanAntonioSpurFan]

Bob,

Thanks for the reply....The query does not produce the correct values for WeeksScanned and StoreCount.

Based on Example above, results shoud be as follows:
UPC, Store Count, Weeks Scaned, Units Sold
001, 2, 3, 25

I get the following when I run the query above:
UPC, Store Count, Weeks Scaned, Units Sold
001, 6, 6, 25

Any clue?

 

[scriverb]

Modify the Group By clause to the following:

Group By A.UPC, A.StoreID, A.WeekID

Give that a try and post back with the results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[SanAntonioSpurFan]

I tried it and got the following:

upc StoreCount WeeksScanned UnitsSold
1 1 1 5
1 1 1 2
1 1 1 3
1 1 1 7
1 1 1 3
1 1 1 5

Again, I need the query to return in this case 1 record that should read:
1, 2, 3, 25 as results...

 

[scriverb]

SpursFan: This is a little different and I know how to do it with multiple saved queries. But, you have to work on them one column at a time.

EXAMPLE:
Name and Save as [RED]qryUPC[/RED]

SELECT 1 AS UPC_uniquenumbers
FROM [Scan Sales] AS A
GROUP BY A.UPC;

Name and Save as [red]qryUPCcount[/red]

SELECT Sum(qryUPC.UPC_uniquenumbers) AS UPC
FROM qryUPC;

Now when you run the second query you have the count of unique UPC codes from your file. We need to duplicate these queries for the WeekID and StoreID. Just change the table names and field names to be more appropriate and save as a name that is appropriate for the output.

The Count of the Sales Units can be done with the following:

SELECT Sum([Scan Sales]![Unit]) AS UnitsSold
FROM [Scan Sales];

Now in a final query bring the recordsets from all of the queries in and just display the values for UPC, StoreCount, WeekCount, and UnitsSold. You will have a single row with the data that you requested.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]