Cross Tab Query

[Gmen via AccessMonster.com]

I have a access database with SQL backend. The table labproofs has charges
listed by report date, with the below query, I can run a cross tabquery with
the end date selected from a form (monthly report), and it gives me the 12
month data from the end date selected. However, the problem I face is for
example if the end date selected is feb 28th, I get the data for previous
months as Jan 28, Dec 28, Nov 28 ......, I want data for the month end
numbers like feb 28, Jan 31, Dec 31....., I am not sure what I am doing wrong
here.

Please help.

Gmen

PARAMETERS [Forms]![Monthly Reports]![EndDate] DateTime;
TRANSFORM Sum(LABPROOFS.Charges) AS SumOfCharges
SELECT ClientMasterFile.manager, ClientMasterFile.repname, ClientMasterFile.
ContactID
FROM ClientMasterFile INNER JOIN LABPROOFS ON ClientMasterFile.acctNum =
LABPROOFS.Account
WHERE (((LABPROOFS.Transtype)="4010"))
GROUP BY ClientMasterFile.manager, ClientMasterFile.repname, ClientMasterFile.
ContactID
PIVOT "Mth" & Format(DateDiff("m",[ReportDate],Forms![Monthly Reports]!
EndDate)) In ("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8",
"Mth9","Mth10","Mth11","Mth12");

 

[KARL DEWEY]

I get the data for previous months as Jan 28, Dec 28, Nov 28 ......,
Not when I run the query.
Are you saying the data is from Dec 29 through Jan 29 instead of 1 Dec
through 31 Dec?

 

[gm6243 via AccessMonster.com]

Hi Karl,

Thanks for you response.

Feb 28 numbers come out okay, Jan numbers run from 29th Dec to Jan 28, Dec
numbers run from Nov 29 to Dec 28 and so on.......

Not when I run the query.
Are you saying the data is from Dec 29 through Jan 29 instead of 1 Dec
through 31 Dec?

I have a access database with SQL backend. The table labproofs has charges
listed by report date, with the below query, I can run a cross tabquery with

[quoted text clipped - 21 lines]

EndDate)) In ("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8",
"Mth9","Mth10","Mth11","Mth12");

 

[KARL DEWEY]

Not that way for me. I assume the you enter 2/28/2010 into your form --
Forms![Monthly Reports]!EndDate

2/28/2010 will yield data in Mth0 column from Feb.
2/28/2010 will yield data in Mth1 column from Jan.
2/28/2010 will yield data in Mth2 column from Dec.
etc......

Is this not what you are getting? Try replacing a data entry for Jan 30
in your table with something like 999999 to see which column it falls.

--
Build a little, test a little.

Hi Karl,

Thanks for you response.

Feb 28 numbers come out okay, Jan numbers run from 29th Dec to Jan 28, Dec
numbers run from Nov 29 to Dec 28 and so on.......

I get the data for previous months as Jan 28, Dec 28, Nov 28 ......,

Not when I run the query.
Are you saying the data is from Dec 29 through Jan 29 instead of 1 Dec
through 31 Dec?

I have a access database with SQL backend. The table labproofs has charges
listed by report date, with the below query, I can run a cross tabquery with

[quoted text clipped - 21 lines]

EndDate)) In ("Mth0","Mth1","Mth2","Mth3","Mth4","Mth5","Mth6","Mth7","Mth8",
"Mth9","Mth10","Mth11","Mth12");

--



.

 

[gm6243 via AccessMonster.com]

Hi Karl,

Thank you, It was my bad, I must have put some wrong date and ran two
different reports. I used your suggestion to see where the 999999 landed and
it did in Jan, proving that the query i was using was correct.
Sorry for the inconvenience. Appreciate all your help and advice.

Gmen

Not that way for me. I assume the you enter 2/28/2010 into your form --
Forms![Monthly Reports]!EndDate

2/28/2010 will yield data in Mth0 column from Feb.
2/28/2010 will yield data in Mth1 column from Jan.
2/28/2010 will yield data in Mth2 column from Dec.
etc......

Is this not what you are getting? Try replacing a data entry for Jan 30
in your table with something like 999999 to see which column it falls.

Hi Karl,

[quoted text clipped - 13 lines]