Trends over time 2

[netrusher]

I currently have a totals query (see code below) that shows me total based on a range of dates. The results look like this.

System Group Mechanical Totals
Battery Box 5
Brake Cable 6
Cowl 8

What I would like to be able to do with a query is to show a trend for each system group over time:

Let's say Week one Battery Box = 5
Week Two Battery Box = 3
Week Three Battery Box =1 etc etc. This would show the trend of Mechanical Totals (errors) over time for each System Group. This could be also by month or week. Any suggestions on how to accomplish with a Query?

SELECT SystemGroup, Count(*) AS [Mechanical Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults") And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And ([TodaysDate] Between Forms!WorkUnitsFaultsMainFRM!StartDateTxt And Forms!WorkUnitsFaultsMainFRM!EndDateTxt)
GROUP BY SystemGroup;

 

[netrusher]

Miss Leslie,

Thansk,

I get a duplicate alias error with the code for Mechanical Totals. I changed the name on one of them but I keep getting the following error after that:

The Microsoft Jet database engine does not recognize
'Forms!Queries_ReportsFRM!StartDateTxt' as a valid name or expression.

I have enquired and was told that you can use date range from a form but this will not take it. Any more suggestions?

 

[SkipVought]

Convert the TEXT to a REAL DATE.

Skip,

_[red][/red]

 

[netrusher]

YEA! I found out that I needed to add specific Parameters to the Parameter Query dropdown to call dates from a form. It worked.

Thanks for all of your help!!

 

[netrusher]

To let everyone know, below is the SQL I ended up with. Then I had to add to
the Paremeter Query Dropdown:

Of course I still do not understand all of this but it works and I am trying to learn!

[Forms]![Queries_ReportsFRM]![StartDateTxt] Date/Time
[Forms]![Queries_ReportsFRM]![EndDateTxt] Date/Time

Again, thanks for all the help!!

PARAMETERS [Forms]![Queries_ReportsFRM]![StartDateTxt] DateTime, [Forms]![Queries_ReportsFRM]![EndDateTxt] DateTime;
TRANSFORM Sum(TrendOneThree.[Mechanical Totals]) AS [SumOfMechanical Totals]
SELECT TrendOneThree.SystemGroup
FROM TrendOneThree
GROUP BY TrendOneThree.SystemGroup
PIVOT TrendOneThree.Week;