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;

 

[Remou]

Do you mean the number of problems per group per week? If so, why not add a week grouping?

[tt]Week: Format([FaultDate],"yyyy ww")[/tt]

 

[netrusher]

Yes that was what I meant. Could it also be done by month?
How do I add the week grouping to my SQL Code above?

 

[Remou]

Add the 'fault date' to the grid and fill in the format you want. Group by this field. Formatting by Month is also possible. You will find quite a bit on the Format function in Help.

 

[netrusher]

How do I add this to the existing SQL or do I need to create another query? The existing SQL query is based on a date range that I would like to keep if possible.

 

[netrusher]

Below is the SQL that I currently have for this Query. How do I add what you sent to this SQL?

Thanks for your help!

SELECT WorkUnitsFaultsMainTBL.SystemGroup, Count(*) AS [Mechanical Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup;

 

[Remou]

I thought I answered this in my reply dated 2 Jan 07 8:39. What part of that reply did not suit?

 

[SkipVought]

Hi,

It concerns me that your dates seem to be store as text

[StartDateTxt], [EndDateTxt]

Is there a reson for that?

Why not REAL DATES?

What is the format of your "date" string?

Skip,

_[red][/red]

 

[netrusher]

Remou,

Well, I am sure you gave me the answer but that does not mean I understand what to do with it. I have tried to
add it to my Query in Design view must be doing something
wrong. Can you advise where and how it goes in the Query
for me?

 

[netrusher]

Skip,

[StartDateTxt], [EndDateTxt] are just the names of the
Fields on the Form. They store as dates though.

Thanks

 

[SkipVought]

Are your date string converted to real dates IN THE FORM TEXTBOX or ON UPDATE.

Could be a problem if something like...

Where...
TodaysDate Between 06/30/2006 And 04/15/2007

as the text values will not colate as expected.


Skip,

_[red][/red]

 

[netrusher]

Skip,

I really do not have a problem with my current Query. I am seeking advice as to how to create a query that will show Trends over time and would like to automate it as much as possible. I would like to how a System Group Faults have either gone up or down over a period of time.

Thanks for the help.

 

[lespaul]

I really do not have a problem with my current Query

you obviously DO have a problem with your current query since you are asking for assistance in making it do what you want.

Additionally, if you are going to be doing complex queries with calculations, you really should get away from the Query Design Grid. It is much easier to manipulate SQL statements.

SELECT WorkUnitsFaultsMainTBL.SystemGroup, [b]Format([FaultDate],"yyyy ww")[/b], Count(*) AS [Mechanical Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") AND ((WorkUnitsFaultsMainTBL.TodaysDate) Between [Forms]![WorkUnitsFaultsMainFRM]![StartDateTxt] And [Forms]![WorkUnitsFaultsMainFRM]![EndDateTxt]) AND ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, [b]Format([FaultDate],"yyyy ww")[/b];

This assumes that FaultDate is stored in the table as a Date field.

See Thread701-1258387 for more information on aggregate functions (COUNT, SUM, AVG) and how the GROUP BY clause works.

Leslie

 

[netrusher]

Miss Leslie,

I really appreciate everyone's help on this and maybe I am not stating what I am seeking correctly. My current query works just fine. As I stated earlier I am seeking to make a new query that will show trends over time. I am not using the query design grid as my original query is SQL and that is why I showed the SQL. Maybe I'll get better at explaining myself in the future. Thanks for your help Leslie, it is appreciated!

 

[Remou]

lespaul

You say "Additionally, if you are going to be doing complex queries with calculations, you really should get away from the Query Design Grid." and I am sure you are right, but do you not find the Design Grid useful for those who are learning to build query strings and learning to add additional fields to a string?

 

[lespaul]

I personally hate the query design grid, never used it and never found it useful....others may. It consistently uses the HAVING statement incorrectly and I hate all the extra parens it adds around the WHERE clause components, just makes the SQL hard to read.

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]

Miss Leslie and others:

Your Query worked great and thanks!

Now I have another request:

Below is the code I am currently using:

It produces results that look like this:

SystemGroup Week Mechanical Totals
Drive Axle 2006 49 5
Drive Axle 2006 50 9
Drive Axle 2006 51 8
Drive Train 2006 49 2
Drive Train 2006 50 2
Drive Train 2006 51 3
Electrical 2006 49 10
Electrical 2006 50 10
Electrical 2006 51 19

Is there a way to change the SQL so the Results will
look something like below?:

2006 49 2006 50 2006 51
Drive Axle 5 9 8
Drive Train 2 2 3
Electrical 10 10 19

Thanks for all assistance with this!!

SELECT WorkUnitsFaultsMainTBL.SystemGroup, Format([TodaysDate],"yyyy ww") AS Week, Count(*) AS [Mechanical Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, Format([TodaysDate],"yyyy ww");

 

[lespaul]

look into the cross tab query. It will convert your existing SQL to a TRANSFORM query and you'll get the results you're looking for.

Leslie

 

[netrusher]

Miss Leslie,

I have never understood crosstab queries but I will try again. How does it convert existing SQL to a TRANSFORM query?

 

[lespaul]

Ok, cross tabs aren't something that I do, but I'll try to get this for you. A guess:

TRANSFORM Count(*) AS [Mechanical Totals]
SELECT WorkUnitsFaultsMainTBL.SystemGroup, Format([TodaysDate],"yyyy ww") AS Week, Count(*) AS [Mechanical Totals]
FROM WorkUnitsFaultsMainTBL
WHERE (((WorkUnitsFaultsMainTBL.FaultCategory)<>"No Faults" And (WorkUnitsFaultsMainTBL.FaultCategory)<>"Cosmetic") And ((WorkUnitsFaultsMainTBL.TodaysDate) Between Forms!Queries_ReportsFRM!StartDateTxt And Forms!Queries_ReportsFRM!EndDateTxt) And ((WorkUnitsFaultsMainTBL.BuildID) In ("E010","C809","F001","C810","F187","A910","M173","M174")))
GROUP BY WorkUnitsFaultsMainTBL.SystemGroup, Format([TodaysDate],"yyyy ww")
PIVOT WorkUnitsFaultsMainTBL.SystemGroup




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