Crosstab query problem

[bubba100]

I hate crosstab queries. We have 50 that are dynamtic, they use another query to determine the column headings. All work fine except for 4, they are for a related subject so it might be one of the underlying queries that are causing me the problem. The crosstab will open with the correct headings but is empty. The underlying open and populate with the information.

Here is the crosstab:

TRANSFORM Sum(qrytblOdometersSample1.[Audited Miles]) AS [The Value]
SELECT qrytblOdometersSample1.Jurisdiction
FROM qryAudYrsExport LEFT OUTER JOIN qrytblOdometersSample1 ON qryAudYrsExport.AudYrs = qrytblOdometersSample1.[BegDate By Quarter]
GROUP BY qrytblOdometersSample1.Jurisdiction
PIVOT qryAudYrsExport.AudYrs;

Here are the 2 underlying queries.

SELECT DISTINCT Left([Period],4) AS AudYrs
FROM tblPeriod, tblRegistrantName
WHERE (((Left([Period],4) & "/" & Right([Period],1))>=Left([AudStart],4)));


and

SELECT DISTINCTROW Format$([tblOdometers].[BegDate]," yyyy") AS [BegDate By Quarter], tblOdometers.Jurisdiction, Sum(tblOdometers.[Reported Miles]) AS [Sum Of Reported Miles], Sum([EndOdom]-[BegOdom]) AS [Audited Miles]
FROM tblEquipment INNER JOIN tblOdometers ON tblEquipment.Unit = tblOdometers.Unit
GROUP BY Format$([tblOdometers].[BegDate]," yyyy"), tblOdometers.Jurisdiction, tblEquipment.FuelType, tblEquipment.[IFTA/MC]
HAVING (((Format$([tblOdometers].[BegDate]," yyyy"))<>1998) AND ((tblEquipment.FuelType)="DIE" Or (tblEquipment.FuelType)="XXX") AND ((tblEquipment.[IFTA/MC])="Y"))
ORDER BY tblOdometers.Jurisdiction;

Any ideas are GREATLY appreciated!! Thanks

 

[dhookom]

Is there a reason why you use all these format$() with a space before the yyyy?

If BegDate is a date field, why don't you just use Year(tblOdometers.BegDate)?

Why do you name the column "BegDate By Quarter" when the column returns the year?

I would remove all the "HAVING..." and place these criteria in a "WHERE...".

You might want to enter all possible years in the column headings property of the crosstab.


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]

 

[bubba100]

Duane-- thanks for pointing out some of the faults that I have to deal with. I took over the responsibility for this db. The person that started the development left NO documentation so what I know I had to "figure out". There is a problem with naming as you pointed out but there are over 300 queries and I can't start fixing them without understanding how they are interrelated. (FYI- that person came back and is now my supervisor)

The BegDate is a date field 1/1/2002 what I need is by year 2002.

The reason why I had to rewrite many crosstab queries to be dynamtic is that we use the 3 previous years (2002,2003,2004) and every year I would have to "fix" those queries to pick up a year and drop a year (a real PAIN)there are about 40 of these.

 

[dhookom]

There should not be a need to re-write crosstabs each year. All you should have to do is create a column heading that uses:
ColHead: "Y" & DateDiff("yyyy",[BegDate],Date())
Set the Column Headings property to:
Column Headings: "Y0","Y1","Y2","Y3"
The Y0 column will be this year while Y1 will be last year.

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]

 

[bubba100]

Duane-- Thanks, I'll try your suggestion later today.