Monthly Summary Problem

[BradBrad]

Hi All,

I have a form setup where a person can enter data (5 fields) and the date autopopulates with code in the form (dd/mm/yyyy).

What I need to be able to do is create a monthly summary and separate that by year.

I know about the sum function, but without some way to normalize the dates (IE - xx/01/2011 turns into January 2011), I am stuck. Maybe I'm coming at this from the wrong angle.. who knows?

Any ideas on how to do that?

I'm very quick on pushing the "Thanks button".. Any help/advice would be greatly appreciated.

 

[Galaxiom]

I would say you are coming from the wrong angle. It the date is stored in a Date/Time datatype it doesn't really matter what regional setting your computer is set for, provided you use the right functions.

I would expect you to be using the Month() and Year() functions but if you describe your problem further I am sure we will be able to sort it.

 

[BradBrad]

Alright, here is a code snippet:

SELECT [Aeration Data].[The Date], [Aeration Data].[30 Minute Settling Test], [Aeration Data].[MLSS (mg/L)], [Aeration Data].[MLVSS (mg/L)], [Aeration Data].[Ammonia In (mg/L)], [Aeration Data].[Ammonia Out (mg/L)], [Aeration Data].[Ortho-Phosphorus In (mg/L)], [Aeration Data].[Ortho-Phosphorus Out (mg/L)]
FROM [Aeration Data]
GROUP BY [Aeration Data].[The Date], [Aeration Data].[30 Minute Settling Test], [Aeration Data].[MLSS (mg/L)], [Aeration Data].[MLVSS (mg/L)], [Aeration Data].[Ammonia In (mg/L)], [Aeration Data].[Ammonia Out (mg/L)], [Aeration Data].[Ortho-Phosphorus In (mg/L)], [Aeration Data].[Ortho-Phosphorus Out (mg/L)]
HAVING ((([Aeration Data].[The Date]) Like "*" & [Choose a Month - Format: mm/yyyy] & "*"));

This gives the user a prompt to enter in the mm/yyyy to see all the individual days of data (in that month only).

What I'd like to have in another query is have the mm/yyyy converted into "Month - Year" and have the individual days data summed into a monthly total.

Let me know if this is possible! Thanks for your help so far.

 

[Taruz]

Hi.. Try this way..:

SELECT [COLOR="Red"]format([The Date],"mm\/yyyy")[/COLOR],
 [Aeration Data].[30 Minute Settling Test],
 [Aeration Data].[MLSS (mg/L)],
 [Aeration Data].[MLVSS (mg/L)], 
 [Aeration Data].[Ammonia In (mg/L)], 
 [Aeration Data].[Ammonia Out (mg/L)], 
 [Aeration Data].[Ortho-Phosphorus In (mg/L)], 
 [Aeration Data].[Ortho-Phosphorus Out (mg/L)]
FROM [Aeration Data]
GROUP BY[COLOR="red"] format([The Date],"mm\/yyyy")[/COLOR], [Aeration Data].[30 Minute Settling Test], [Aeration Data].[MLSS (mg/L)], [Aeration Data].[MLVSS (mg/L)], [Aeration Data].[Ammonia In (mg/L)], [Aeration Data].[Ammonia Out (mg/L)], [Aeration Data].[Ortho-Phosphorus In (mg/L)], [Aeration Data].[Ortho-Phosphorus Out (mg/L)]
[COLOR="red"]HAVING (((format([The Date],"mm\/yyyy")) = [Choose a Month - Format: mm/yyyy] ));[/COLOR]

Attention to the format function.!

 

[Galaxiom]

Date datatype is not a string and needs to be referred to in sql as #mm/dd/yyyy#

The best way is to generate the first and the last day of the month from the input value and then find dates between them in the query. Unfortunately this means entering the mm/yyyy twice if you are using a criteria dialog so it is better to enter this in a form and refer to the form.

The DateSerial function can be used used to generate the two dates from the mm/yyyy

It can also be done by formatting the date in the records to mm/yyyy and testing against the input but that is very much slower than using the daterange. However it is OK if you only have a small number of records.

HAVING Format([Aeration Data].[The Date],"mm/yyyy") = [Choose a Month - Format: mm/yyyy]

 

[BradBrad]

Hi Taruz - That Worked perfectly!

 

[BradBrad]

Now - I guess one more question - If I wanted to have a report that had the monthly summaries (using Taruz's function) for the year, how would I go about doing that?

It would just prompt the user to enter the year and then all the monthly summaries (with the values summed) would appear.

 

[Taruz]

Hi Brad..

In this structure, by entering year, grouped receive monthly summaries..:

select format([The Date],"mm\/yyyy"), 
[MLSS (mg/L)]
from [Aeration Data]
where year([The Date])=[Choose a Year - Format: yyyy]
group by format([The Date],"mm\/yyyy"), [MLSS (mg/L)]

 

[BradBrad]

You're a magician. Thanks so much for your help!