Help with report-generated from a cross tab query 1

[Deegs]

Hi,
I need help on a report-generated from a cross tab query please.

The table I am using is similar to:

ID ITEM DATE LATEST QTY
1 Pump 01/08/04 7
2 Pump 01/09/04 6
3 Pump 01/10/04 5
4 Column 01/08/04 4
5 Column 01/09/04 3
6 Column 01/10/04 3

I am using a cross tab query to transpose the data like so

Item 01/08/04 01/09/04 01/10/04
Pump 7 6 5
Column 4 3 3

To show changes to quantites over months.

However if I run a report I will have to manually add every new month because the report see’s the record as a field in my query.

REPORT
Item 01/08/04 01/09/04 01/10/04 xx/xx/xx
Pump 7 6 5
Column 4 3 3


Is there an easy way around this? I would be in debt to anyone would could help.

Regards,
Mark.

 

[dhookom]

There is a solution in faq703-5466 that does this by month. You could modify the solution to report/display by day.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[Deegs]

Thx very much...

Mark........

 

[Deegs]

Using the solution kindly supplied by Dhookom I am closer to the light at the end of the tunnel.

However I wish to report on latest number of items required by project. This means that some projects will go further in the past than others.

My dilemma is that on one project I may have relative months Mth5, Mth4,Mth3,Mth2, Mth1 and on an other project Mth3,Mth2, Mth1 only. The report states the Mth4 or greater cannot be found as a valid field name or expression.

Is there a way to kid the report or an expression tell it that if a Mth does not exist to print a “ “.

As always help is very much appreciated from you all.

Mark…..

“Life IS like a box of chocolates”

 

[dhookom]

Did you do the part in the FAQ...
[blue]
Set your queries Column Headings property to:
"Mth0", "Mth1", "Mth2", "Mth3",.., "Mth11"
[/blue]

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[Deegs]

Flashback to when I made my first Airfix plane and had bits left over. Morale always read the instructions!!!!

Thanks Duane. Prob solved.......