CROSS TAB WOES 1

[netrusher]

I have created a cross tab query and I have a problem. The query will not recognize the date fields I have on a form. Any suggestion? Also I would like the dates to display by Week or Month I tried the format in criteria (yyyy ww) but it did not work either (mismatch). Also one of the fields in my query FaultCategory is just there so I can filter by criteria. I do not want it to show up in the query. Is there a way to hid that field? There is no check box to turn off like in normal queires

TRANSFORM Count(WorkUnitsFaultsMainTBL.Problem) AS CountOfProblem
SELECT WorkUnitsFaultsMainTBL.FaultCategory, WorkUnitsFaultsMainTBL.SystemGroup
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![Queries_ReportsFRM]![StartDateTxt] And [Forms]![Queries_ReportsFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.FaultCategory)="mechanical"))
GROUP BY WorkUnitsFaultsMainTBL.FaultCategory, WorkUnitsFaultsMainTBL.SystemGroup
PIVOT WorkUnitsFaultsMainTBL.TodaysDate;

 

[netrusher]

The date fields on my form are not tied to a table. They are just calendars so I can select a start date and a end date. This could be a problem but I would like to be able to have a start date and end date for my crosstabl query.

 

[netrusher]

I found out that I had to add specific parameters to the Parameter Query dropdown and it works.

 

[lespaul]

have a star for solving your own problem!

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[netrusher]

Thanks but someone else tipped me off to the Parameter Query.