Query Help - Normalize (Time Zero) Items and Total

[bs0d]

I'm not much of a query guy and I'd like some help on determining the best approach to achieve what I'm after.

Suppose I have a table of products, and a table of sales by date. Older products have longer sales histories, new products have shorter sales histories.

I would like to show the total product sales where day 1 is the same for all items, and so on. I think this is called a time zero or normalized view.

So assume we had this data:

id       date      item       sales
1         1/1       441         10
2         1/2       441         15
3         5/6       789         05
4         5/7       789         08
5         5/8       789         13
6         3/4       529         03
7         3/5       529         14
8         3/6       529         25
9         3/7       529         35

Then my desired output would look like this:

Day     SUMofSales       CountofItem
1               18                   3
2               37                   3
3               38                   2
4               35                   1

 

[bs0d]

Added trackback in alternate forum.

 

[Joe4]

This one is a little tricky. The first thing I did was look up how to rank in SQL, and found some posts like these, and created the ranking query for each record.
ACC2000: How to Rank Records Within a Query
RANK in MS Access | Useful Gyaan

Then, I created the ranking query using your data (assume table name was Table1):

SELECT Table1.id, Table1.Date, Table1.item, Table1.sales, (SELECT COUNT(Table1A.date)
FROM [Table1] as Table1A
WHERE [Table1A].[date]<=[Table1].[date]
AND [Table1A].[item]=[Table1].[item]) AS [Day]
FROM Table1
ORDER BY Table1.[date];

Note that this uses two references to Table1, one which I gave the alias Table1A.
I then saved the query above with the name Query1. This now shows all your data, and a column which ranks each item by their day.

We can now use this query in another query, specifically an Aggregate (Totals) Query to get your SUM and counts like this:

SELECT Query1.Day, Sum(Query1.sales) AS SumOfsales, Count(Query1.item) AS CountOfitem
FROM Query1
GROUP BY Query1.Day;

This gives me the exact expected results you posted.

 

[Joe4]

Added trackback in alternate forum.

Do you mean that you also posted this in another forum elsewhere? If so, please post a link to it (see rule #10 here: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html)

 

[bs0d]

Yes, link: Query Help - Normalize (Time Zero) Items and Total

Thanks Joe4. Currently studying your answer

Should there be an inner join at any point since there is a product table, and a table of sales? The only info on products in the sales table is the product id. For example, product types are in the product table. So if I wanted to only show the desired out put for products where type = "red", does this overly complicate the structure?

 

[Joe4]

Should there be an inner join at any point since there is a product table, and a table of sales? The only info on products in the sales table is the product id. For example, product types are in the product table. So if I wanted to only show the desired out put for products where type = "red", does this overly complicate the structure?

I was going off the assumption that your first screen print showed the data in a single table. If it is the result of a query instead, simply replace the "Table1" reference with your query reference.

 

[bs0d]

I was able to add an INNER JOIN by item # (to join the products and sales tables), then add any conditionals in the WHERE clause. ---all in the 1st query as you mentioned.

The 2nd query being unchanged.

This is a handy query, and seems to execute very fast. Thanks again (SOLVED)

 

[Joe4]

Your welcome. Glad it all works out for you!