Cross-tab query YTD

[stremchem]

Hello Everyone! This is my first time visiting this forum...

I've come to you with a question that has been stumping me for awhile. I've searched and searched on this topic to no avail. My CEO is asking for certain information over the past five years, but in each year, he only wants the YTD information, not that entire year's information. He wants to compare the current YTD to past years.

I've tried to use the DateSerial function, but it doesn't seem to work well in a cross-tab query. So for example, I want to run a report based on invoice dates through July 31st of each of the past five years.

Any advice is appreciated. Thank you!

-Mike

[weekend00]

I think you need to provide more detail about you data format and/or table structure.
Is there once record a day for each day and you just want the record with date as 7/31? or you need the summary from 1/1 to 7/31 of each year?

[stremchem]

I apologize for the lack of data. We're looking to count the # of units sold for some particular item #'s. The crosstab query sums the total # of units sold each calendar year. Unfortunately, when we compare # of units YTD in 2010 to past years, it's inaccurate because the past years' totals are for the entire year.

As of now the query lists each year (2002 to 2010) and returns total # of units sold for each item in that particular year.

Is this helpful?

[weekend00]

create a query to do the total first, then do transform using this query as data source.

for example:
Assume you table is like this tbl(dDate datetime, units long, Item, text)
Create query "GetTotal":
SELECT Year(ddate) AS yyear, Sum(tbl.units) AS total, tbl.item
FROM tbl
WHERE (((Format([ddate],"mmdd"))<=Format(Now(),"mmdd")))
GROUP BY Year(ddate), tbl.item;


you may want to change the text in red to "0731" to get total befoe and on 7/31

Transform query YTDTotal:

TRANSFORM Sum(GetTotal.total) AS SumOftotal
SELECT GetTotal.item
FROM GetTotal
GROUP BY GetTotal.item
PIVOT GetTotal.yyear;

[stremchem]

I'm not as good with straight out SQL, but here is what this query shows as of right now:

TRANSFORM Sum([tbl_Collaborator Report Info].[#UnitsInclKits]) AS [SumOf#UnitsInclKits]
SELECT [tbl_Collaborator Report Info].[ESH CoField], [tbl_Collaborator Report Info].PLINID, [tbl_Collaborator Report Info].ITMDESC, [tbl_Collaborator Report Info].CODEfromSBT, [tbl_Cat Properties from ChemCatMgr].introdate, [tbl_Collaborator Report Info].CategoryItem, Sum([tbl_Collaborator Report Info].[#UnitsInclKits]) AS [Total Of #UnitsInclKits]
FROM [tbl_Collaborator Report Info] LEFT JOIN [tbl_Cat Properties from ChemCatMgr] ON [tbl_Collaborator Report Info].PLINID = [tbl_Cat Properties from ChemCatMgr].catalogitemnumber
GROUP BY [tbl_Collaborator Report Info].[ESH CoField], [tbl_Collaborator Report Info].PLINID, [tbl_Collaborator Report Info].ITMDESC, [tbl_Collaborator Report Info].CODEfromSBT, [tbl_Cat Properties from ChemCatMgr].introdate, [tbl_Collaborator Report Info].CategoryItem
PIVOT Format([invdte],"yyyy");

The very bottom line which shows "PIVOT Format([invdte],"yyyy");" - is this what is populating the entire year's worth of data? Would that be the line that I edit so that it only calculates data from the beginning of the year through a particular month's end? (for example, 01/01/05 through 07/31/05)? Please see cross-tab query results below...



I'd like for the total # of units only to sum through 07/31/05 instead of all of 2005. Is this possible?

Thank you!

[weekend00]

You may want to try to add a where clause( in red) in you query as following:

TRANSFORM Sum([tbl_Collaborator Report Info].[#UnitsInclKits]) AS [SumOf#UnitsInclKits]
SELECT [tbl_Collaborator Report Info].[ESH CoField], [tbl_Collaborator Report Info].PLINID, [tbl_Collaborator Report Info].ITMDESC, [tbl_Collaborator Report Info].CODEfromSBT, [tbl_Cat Properties from ChemCatMgr].introdate, [tbl_Collaborator Report Info].CategoryItem, Sum([tbl_Collaborator Report Info].[#UnitsInclKits]) AS [Total Of #UnitsInclKits]
FROM [tbl_Collaborator Report Info] LEFT JOIN [tbl_Cat Properties from ChemCatMgr] ON [tbl_Collaborator Report Info].PLINID = [tbl_Cat Properties from ChemCatMgr].catalogitemnumber where Format([invdte],"mmdd"))<=Format(Now,"mmdd")
GROUP BY [tbl_Collaborator Report Info].[ESH CoField], [tbl_Collaborator Report Info].PLINID, [tbl_Collaborator Report Info].ITMDESC, [tbl_Collaborator Report Info].CODEfromSBT, [tbl_Cat Properties from ChemCatMgr].introdate, [tbl_Collaborator Report Info].CategoryItem
PIVOT Format([invdte],"yyyy");

[stremchem]

Thank you! That seems to work fantastic! My question is, what dates is this pulling exactly?

[weekend00]

look at: Format(Now,"mmdd")
Now is current date, the date you run the query. if you want a fix date, just replace Format(Now,"mmdd") with a date in "mmdd" format such as "0731" for July 31.

[stremchem]

Thank you. I found that taking out the = sign in the where clause eliminates any invoice dates in the current month. This is perfect! Thanks again!

where Format([invdte],"mmdd"))<=Format(Now,"mmdd"

[weekend00]

Glad to help.