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

[rpeare]

I don't see the correlation between the two sets of data.

You have more than 4 dates of service (I assume the date includes the year as well)
You have only 3 items
your ID is unique

So exactly how are you summarizing this?

[bs0d]

Sum by first date, regardless of the date. So time 1 is the first day for each item (10+5+3). Time 2 is the 2nd day for 3 items (15 + 8 + 14). Time 3 only contains 2 items (13 + 25)

[June7]

I don't see a single query accomplishing this alone with this data as is. Getting the first and last records of each item would be simple enough but everything in between would not. Need a sequence number that can be used as grouping criteria.

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

[rpeare]

I still don't see how you're grouping just by the data given, if the date is day/month or month/day (it doesn't matter) there's no way of determining by the remaining data what goes in what bucket that I can see

Items grouped under day 1 have the dates 1/1, 5/6 and 3/4 they do not share an item number if you are arbitrarily starting your counting from 1, then 1, 2, then 1,2 , 3 just adding 1 to the maximum of the sequence every time you begin a new cycle that makes absolutely no sense to me how this would be useful?

[bs0d]

June7 is on the right track, but a query can accomplish this (two queries actually). Joe4 @ MrExcel gets the Gold star. I also posted in that forum, but didn't see a spot to link.

Here is his solution, which I have also confirmed to work: http://www.mrexcel.com/forum/microso...ml#post3867728

One query ranks the records, and the 2nd query aggregates.

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

then

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

[June7]

This can also be done with DCount() but domain aggregate functions can be slow performers in query so the nested subquery maybe better approach. You should even be able to nest the first query into the second and have one long SQL statement.

[bs0d]

You should even be able to nest the first query into the second and have one long SQL statement.

This sounds very interesting. How might that code look? I'm actually doing this with VBA in Excel, so it maybe easier in the long run to just have one query.

[June7]

Just copy/paste the sql of the first query into the second so the result is like:

SELECT Query1.Day, Sum(Query1.sales) AS SumOfsales, Count(Query1.item) AS CountOfitem
FROM (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]) AS Query1
GROUP BY Query1.Day;

[bs0d]

But if you're still using "Query1" references, you still need Query1 saved in the database, right?

[June7]

No. Query1 is an alias for the nested SQL statement. Just like Table1A is an alias within the query. Table1A doesn't exist anywhere else.

The Query1 object can be deleted.

[bs0d]

Ok thanks, I'll check that out. Thanks for the tip. If it doesn't slow anything down I'll probably use that approach.