Adding monthly records to quarterly

[moniong]

Hi to all,

I'm in making query in Access I have different records that contain
basically all numerical fields and it is imperative as part of the
functionality of this database that I add the fields that correspond
to each record. Here's what my table looks like:

Year/Mo Field1 Filed2 Field3
200801 100 200 300
200802 400 500 600
200803 700 800 900

The result I'm looking for is this:

Year/Qtr Field1 Field2 Field3
2008Q01 1200 1500 1800

How do I do this in query?

thanks,
moniong

 

[Arvin Meyer [MVP]]

The easiest way is to build a query, with a function in a standard module,
and an alias column in the query:

Function YQ(strDate As String) As String
On Error Resume Next
Select Case Right(strDate,2)
Case 01, 02, 03
YQ = Left(strDate,4) & "Q01"
Case 04, 05, 06
YQ = Left(strDate,4) & "Q02"
Case 07, 08, 09
YQ = Left(strDate,4) & "Q03"
Case 10, 11, 12
YQ = Left(strDate,4) & "Q04"
End Select
End Function

Year/Qtr: YQ([Year/Mo])

Then use an Aggregate (Totals) query to get your results:

SELECT [Year/Qtr], Sum(Field1) AS SumOfField1, Sum(Field2) AS SumOfField2,
Sum(Field3) AS SumOfField3
FROM MyTable
GROUP BY MyTable.[Year/Qtr];

 

[John W. Vinson]

Hi to all,

I'm in making query in Access I have different records that contain
basically all numerical fields and it is imperative as part of the
functionality of this database that I add the fields that correspond
to each record. Here's what my table looks like:

Year/Mo Field1 Filed2 Field3
200801 100 200 300
200802 400 500 600
200803 700 800 900

The result I'm looking for is this:

Year/Qtr Field1 Field2 Field3
2008Q01 1200 1500 1800

How do I do this in query?

thanks,
moniong

With a Totals query. You'll need to calculate the quarter since it does not
exist in your data. Your use of a number (long integer??) to store Year/Mo is
probably not a good idea; you're losing all of Access' powerful date/time
field handling capabilities.

What you might be able to do with the existing value is a rather complicated
function to dynamically calculate the quarter, but it would really be MUCH
simpler if you had a date/time field with the first day of each month; you
could then simply use the DatePart("q", [Year/Mo]) function to calculate the
quarter.

With your existing fields, try the following untested air code:

SELECT [Year/Mo] \ 100 & "Q0" & 1 + ([Year/Mo] MOD 100 - 1) \ 3 AS [Year/Qtr],
Sum(Field1) AS SumOfField1, Sum([Field2]) AS SumOfField2, Sum([Field3] As
SumOfField3 GROUP BY [Year/Mo] \ 100 & "Q0" & 1 + ([Year/Mo] MOD 100 - 1) \ 3
AS [Year/Qtr];

You REALLY REALLY should consider normalizing your data. You don't say what
Field1, Field2 etc. are, but it appears that you have a number of different
measurements recorded every month. The correct table structure has one row per
measurement, e.g. fields like MeasurementType, MeasurementDate (don't use Date
as a fieldname, nor should you use / in fieldnames), Amount. The report you
want can be generated using a Crosstab query.