Group By Date

[mrssevans]

I need to take a field that has data such as 1/13/03 and group it on a report.
Such as I would like all the January 02 information under Jan 02 with all the corresponding information summed up. I have a query with all the information summed but it won't group on the month instead of the month, date and year. Any suggestions?

 

[mission2java_78]

Create a calculated field which extracts just the month. Then group by this field.

Jon

 

[mrssevans]

Great idea. I will try that

 

[mrssevans]

Here is the problem I am running into now. In the query if I don't have the date field in there I am getting an error message of "data type mismatch" and if I do leave it I cannot get it to group and sum correctly. Here is the calculation I used.
WORK: Left([DATE],InStr([DATE],"/")-1) & "/" & Right([DATE],2)

Please help

 

[mission2java_78]

You should seperate this into two queries not one..Calculated fields created dynamically can blow up.

Heres what you should do...One query just gets the month..I would NOT get the month as text..I would get it as a single number
1-12

1 corresponding to jan and 12 to dec.

Then your second query should use this queries results as another field.

On your report you use the combined query...and group by this number in ascending order.

Jon

 

[mrssevans]

I tried just putting the month and not even doing the year, but I keep getting a data type mismatch error because some of the dates are null.

 

[mission2java_78]

Great...the null problem...null is so difficult. Why are these nulls?

Do you want to include null rrecords? If so do the following

=IIF(IsNull([YourDateField]), 0, YourCalculatedMonth))

Jon

 

[mrssevans]

Thank you that worked!!

 

[mission2java_78]

Anytime...glad it worked out for ya.

Jon

 

[mrssevans]

Here is what I ended up using: Format([DATE],"mmmm"), but I cannot get it to sort correctly. Does anyone know what happened?

 

[mission2java_78]

Here is what I ended up using: Format([DATE],"mmmm"), but I cannot get it to sort correctly. Does anyone know what happened?

Use the suggestion Pat made..about DatePart function...

Jon

 

[mrssevans]

Ok, still not fixed. I would like it to group the dates into the month. It is splitting out and telling me for every date I have what month it is in. I have it in a query with most fields set to sum or avg. I tried datepart() and it just thought it was a string. Any suggestions?

 

[mission2java_78]

Ok, still not fixed. I would like it to group the dates into the month. It is splitting out and telling me for every date I have what month it is in. I have it in a query with most fields set to sum or avg. I tried datepart() and it just thought it was a string. Any suggestions?

Use several different queries...group BY that field.
I've done the same as you...if you want tomorrow morning ill post an example.

Jon

 

[mrssevans]

That would be great if you could do that. Thanks, Sam

 

[mission2java_78]

That would be great if you could do that. Thanks, Sam

 

[mrssevans]

That example is exactly where I am at also, so can we do this, I would like to group everything in January under one "January". Just like you can do in excel basically under a pivot table. Maybe that is my solution is to just use a pivot table. What do you think.

 

[mission2java_78]

That example is exactly where I am at also, so can we do this, I would like to group everything in January under one "January". Just like you can do in excel basically under a pivot table. Maybe that is my solution is to just use a pivot table. What do you think.

You mean you dont care about the year? Just group by the month? If so then of course..just take out the group by YEAR.

Jon

 

[mrssevans]

I apologize I do care about the year also but I would like it to look like this:

January '02 (Sum of some numbers)
February '02 (Sum of some number)
...


Possible?

 

[mrssevans]

You are a genius. Thanks!

 

[mission2java_78]

genius i highly doubt that.
helpful maybe

Glad its working,
Jon