Limiting the data in a chart to 12 months 1

[irethedo]

I have a chart on a report which I would like to report a rolling average of some First Pass Yield data. This works fine but I am getting more data than I want and wish to limit the data in the chart to 12 months from the most current month and the previous 11 months.

The SPL code for my chart is as follows:

SELECT (Format([Date],"mmm"" '""yy")) AS Expr1, (Sum([Pass Quantity])/(Sum([Pass Quantity])+Sum([Fail Quantity]))*100) AS [FPY by Operation]
FROM [Rolling FPY qry]
GROUP BY (Format([Date],"mmm"" '""yy")), (Year([Date])*12+Month([Date])-1)
ORDER BY (Year([Date])*12+Month([Date])-1);

in the actual query that I am pulling the data from the tables with I am limiting the time frame of the data to an end date provided from a field on a form but I am accepting any dates up until that date with the following criteria in the date field of my query:

<=[forms]![print or view reports]![EndDate]

How might I limit the starting date ranges to be within 12 months of my end date by changing either the criteria in my query or the SQL code for the chart?

 

[dhookom]

Try set the criteria to:

Between DateAdd("yyyy",-1,Forms![Print or view reports]![EndDate]) AND Forms![Print or view reports]![EndDate]

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]

 

[irethedo]

Thanks Duane-

This works great but for another report, How would I modify this to use today's date instead of the end date on my form?

Between DateAdd("yyyy",-1,Forms![Print or view reports]![EndDate]) AND Forms![Print or view reports]![EndDate]

 

[dhookom]

Try this:

   Between DateAdd("yyyy",-1,Date()) AND Date()

I would think this would limit the flexibility of your report but if the filter will never change, this is probably valuable to you.

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]

 

[irethedo]

Thanks Duane-

I tried this and this works if I have the actual date in my chart sql but instead I have:

SELECT (Format([date],"mmm"" '""yy")) AS Expr1, Sum([Metrics qry].Rework) AS [Total Rework Hours]
FROM [Metrics qry]
GROUP BY (Format([date],"mmm"" '""yy")), (Year([date])*12+Month([date])-1)
ORDER BY (Year([date])*12+Month([date])-1);

If I add the Date field in my chart SQL, then I get
too many records instead of the sum of each month as is desired and per the sql above... But then I get a sum of every month available not just the current plus the previous 11 months...

SELECT (Format([date],"mmm"" '""yy")) AS Expr1, Sum([Metrics qry].Rework) AS [Total Rework Hours], [Metrics qry].Date
FROM [Metrics qry]
GROUP BY (Format([date],"mmm"" '""yy")), (Year([date])*12+Month([date])-1), [Metrics qry].Date
HAVING ((([Metrics qry].Date) Between DateAdd("yyyy",-1,Date()) And Date()))
ORDER BY (Year([date])*12+Month([date])-1);

How can I modify the first sql to only display the sums of each month and to only include the current month (using the current date) and the previous 11 months?


Thanks for your help!

 

[dhookom]

To filter on a field, don't put it in the HAVING. It should be in the WHERE clause:

SELECT (Format([date],"mmm"" '""yy")) AS Expr1, Sum([Metrics qry].Rework) AS [Total Rework Hours], [Metrics qry].Date
FROM [Metrics qry]
WHERE [Date] Between DateAdd("yyyy",-1,Date()) And Date()
GROUP BY (Format([date],"mmm"" '""yy")), (Year([date])*12+Month([date])-1), [Metrics qry].Date
ORDER BY (Year([date])*12+Month([date])-1);

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]

 

[irethedo]

There appears to a couple of problems with this...

1. If I include the date field then I get too many rework records instead of the sum of the rework for each month.

SELECT (Format([date],"mmm"" '""yy")) AS Expr1, Sum([Metrics qry].Rework) AS [Total Rework Hours]
FROM [Metrics qry]
GROUP BY (Format([date],"mmm"" '""yy")), (Year([date])*12+Month([date])-1)
ORDER BY (Year([date])*12+Month([date])-1);

Gives me 20 records, Jan 06 - Aug 07 (one for each month with a total sum) And :

SELECT (Format([date],"mmm"" '""yy")) AS Expr1, Sum([Metrics qry].Rework) AS [Total Rework Hours], [Metrics qry].Date
FROM [Metrics qry]
GROUP BY (Format([date],"mmm"" '""yy")), (Year([date])*12+Month([date])-1), [Metrics qry].Date
ORDER BY (Year([date])*12+Month([date])-1);

Gives me 385 records, several records for each month Jan 06 - Aug 07 and no totals

2. Not having the correct format for the "DateAdd("yyyy",-1,Date()) AND Date()" causes this not to work because the format in the spl is "Jan 06" (etc.) and not whatever format the DAteAdd is in... Is there a way to force the date add criteria to match the "mmm yy" format?

 

[dhookom]

I forgot to delete your Date field from your orignal sql. Try:

SELECT Format([date],"mmm"" '""yy") AS mmmyy, Sum([Metrics qry].Rework) AS [Total Rework Hours]
FROM [Metrics qry]
WHERE [Date] Between DateAdd("yyyy",-1,Date()) And Date()
GROUP BY Format([date],"mmm"" '""yy")
ORDER BY Year([date])*12+Month([date])-1;

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]

 

[irethedo]

Thanks Duane-

But this SPL code chokes with the following error message:

You tried to execute a query that does not include the specified expression 'Year([Date])*12+Month([Date])-1' as part of an aggregate function

 

[dhookom]

You might want to learn how to create queries so you can fix my typos ;-)

SELECT Format([date],"mmm"" '""yy") AS mmmyy, Sum([Metrics qry].Rework) AS [Total Rework Hours]
FROM [Metrics qry]
WHERE [Date] Between DateAdd("yyyy",-1,Date()) And Date()
GROUP BY Format([date],"mmm"" '""yy"),Year([date])*12+Month([date])-1
ORDER BY Year([date])*12+Month([date])-1;

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]

 

[irethedo]

Thanks Duane-

I tried adding that

,Year([date])*12+Month([date])-1"

to the line:

GROUP BY Format([date],"mmm"" '""yy")

but it didn't work for me for some reason but you last stab at it works well...

Thank you!