Date Limit in Crosstab from Union query

  • Thread starter Thread starter Molasses26
  • Start date Start date
M

Molasses26

Hello! I have a union query that links together multiple tables and it works
like a charm, however when I try to run a crosstab query based on it and
limit it to a specified date range it does not seem to recognize that they
ARE dates so I still get everything. Some of the dates are entered as date
and time and some of them are just the date, in case that matters.

Any help would be greatly appreciated!

Union Query:
SELECT "AMIEst" as Source, [Site] as IDNo, Upld_Dt, Review_Dt,Analyst from
[tblReport_AMIEstimatedRds]
UNION SELECT "E32Min", [SAA],Upld_Dt, Review_Dt, Analyst from
[tblReport_E32MinBills]
UNION SELECT "HighkWh", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_HighkWh]
UNION SELECT "MtrTestCC",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_CC]
UNION SELECT "MtrTest3Pct",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_gt3Pct]
UNION SELECT "ResHighkW", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_ResAcctsHighkW]
UNION SELECT "SAAsNotBlld",[Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_SAAsNotBilled]
UNION SELECT "Audit/Report",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType=2 and Auditor not in( "Z00000","X00000"))
UNION SELECT "Random/Coach",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType<>2 and Auditor not in( "Z00000","X00000"))
ORDER BY Review_Dt;

Crosstab query:
TRANSFORM Count(qry_AnalystReportStats.IDNo) AS CountOfIDNo
SELECT qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
FROM qry_AnalystReportStats
WHERE (((qry_AnalystReportStats.Review_Dt)>#08/01/2009#))
GROUP BY qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
PIVOT qry_AnalystReportStats.Source;
 
PERFECT!
Thank you so much!

KARL DEWEY said:
Try this --
WHERE ((CVDate(qry_AnalystReportStats.Review_Dt)>#08/01/2009#))

--
Build a little, test a little.


Molasses26 said:
Hello! I have a union query that links together multiple tables and it works
like a charm, however when I try to run a crosstab query based on it and
limit it to a specified date range it does not seem to recognize that they
ARE dates so I still get everything. Some of the dates are entered as date
and time and some of them are just the date, in case that matters.

Any help would be greatly appreciated!

Union Query:
SELECT "AMIEst" as Source, [Site] as IDNo, Upld_Dt, Review_Dt,Analyst from
[tblReport_AMIEstimatedRds]
UNION SELECT "E32Min", [SAA],Upld_Dt, Review_Dt, Analyst from
[tblReport_E32MinBills]
UNION SELECT "HighkWh", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_HighkWh]
UNION SELECT "MtrTestCC",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_CC]
UNION SELECT "MtrTest3Pct",[Site],Upld_Dt, Review_Dt, Analyst from
[tblReport_MeterTest_gt3Pct]
UNION SELECT "ResHighkW", [Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_ResAcctsHighkW]
UNION SELECT "SAAsNotBlld",[Site],Upld_Dt, Review_Dt,Analyst from
[tblReport_SAAsNotBilled]
UNION SELECT "Audit/Report",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType=2 and Auditor not in( "Z00000","X00000"))
UNION SELECT "Random/Coach",[ItemNbr],EntryDate,[Review Date],Auditor from
[tblData] WHERE (ReviewType<>2 and Auditor not in( "Z00000","X00000"))
ORDER BY Review_Dt;

Crosstab query:
TRANSFORM Count(qry_AnalystReportStats.IDNo) AS CountOfIDNo
SELECT qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
FROM qry_AnalystReportStats
WHERE (((qry_AnalystReportStats.Review_Dt)>#08/01/2009#))
GROUP BY qry_AnalystReportStats.Analyst, qry_AnalystReportStats.Review_Dt
PIVOT qry_AnalystReportStats.Source;
Click to expand...
Click to expand...
 
Back
Top