Grouping by Company and Month in one query??

[GSINC]

I am trying to get the amount that each group has spent on a monthly basis but also grouped on the GRPNO. This is how I am currently getting the information below.


SELECT CLAIMS.GRPNO, Sum([CLAIMS]![PRVPMT]+[CLAIMS]![EEPMT]) AS [AMOUNT]
FROM CLAIMS
WHERE (((CLAIMS.PDDT) Between 1-1-2004 And 1-31-2004))
GROUP BY CLAIMS.GRPNO;

GRPNO PDDT PRVPMT EEPMT
685 2/9/2005 1994.24 20.02
684 5/6/2004 1992 11.2
388 6/2/2004 1991.65 1.1
387 5/31/2005 1984.8 15.21
713 4/2/2004 1984 88.85
323 6/23/2004 1977.6 65
770 11/15/2004 1969.41 87.12
82 3/10/2005 1962.41 36.57

Then I collect the information into a table that looks like this:

I am trying to find the portion of this SQL that fill in the questions.

GRPNO Jan2004 Feb2004 Mar2004 Apr2004 etc...
196 7938.55 ??????? ???????
673 6883.55 ??????? ???????
82 4615.47 ??????? ???????
666 4517.43 ??????? ???????
756 4291.08 ??????? ???????
706 3815.74 ??????? ???????

Anythoughts...

 

[Remou]

Hi
The above looks like a crosstab query, but maybe I am missing some vital point

 

[LittleSmudge]

I've got to agree with Remou

CrossTab looks like the right way to go.

Something like

TRANSPOSE Sum(PRVPMT + EEPMT) AS AMOUNT
SELECT GRPNO FROM CLAIMS
WHERE (PDDT Between 1-1-2004 And 1-31-2004)
PIVOT Month(PDDT)

G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[GSINC]

I have run it as a crosstab in the past however. I need to populate the results of that crosstab into a table. Is that possible???

 

[LittleSmudge]

Why ?

As it is a calculatable result when why on earth would you ever consider storing the data yet again into a table ?



Other problems:
In the format you have it in your fields will be DATES which is bad news because you'll have to keep changing the design of the target table as time progresses.







G LS
spsinkNOJUNK@yahoo.co.uk
Remove the NOJUNK to use.

 

[GSINC]

I already have code which works on the format that I am trying to acheive.

Heres the code.

Option Compare Database
Dim cn As ADODB.Connection
Dim rs, rs1, rs2, rsmnth, rsgen As Recordset
Dim Mnth, Yr, Counter, calender As Integer
Dim q1, q2, q3, q4, temp As String



Private Sub cmdcalc_Click()
Label2.Visible = True
MsgBox " This process will take few minutes. Please wait "
Set cn = CurrentProject.Connection
Set rs = cn.Execute("SELECT [Group Number] FROM Contacts ORDER BY [Group Number]")
Do While rs.EOF = False
Set rs1 = cn.Execute("select [Renewal Date] from contacts where [group number] = " & rs.Fields(0) & " ")
Mnth = Month(rs1.Fields(0))
Yr = Year(rs1.Fields(0))
Yr = Yr - 1
calender = 1
q1 = 0
q2 = 0
q3 = 0
q4 = 0
temp = 0
Do While calender < 13
'If Yr = 2004 Then
Set rsmnth = cn.Execute("Select Mnth from LookUPmONTH Where ivalue = " & Mnth & " ")
On Error Resume Next
Set rsgen = cn.Execute("Select [" & rsmnth.Fields(0) & "" & Yr & "] from contacts where [group number] = " & rs.Fields(0) & " ")
If IsNull(rsgen.Fields(0)) = True Or rsgen.Fields(0) = " & rsmnth.Fields(0) & "" & Yr & " Then
temp = 0
Else
temp = rsgen.Fields(0)
End If
'End If
If calender < 4 Then
q1 = q1 + temp
temp = 0
ElseIf calender > 3 And calender < 7 Then
q2 = q2 + temp
temp = 0
ElseIf calender > 6 And calender < 10 Then
q3 = q3 + temp
temp = 0
ElseIf calender > 9 And calender < 13 Then
q4 = q4 + temp
temp = 0
End If
If Mnth = 12 Then
Mnth = 1
Yr = Yr + 1
Else
Mnth = Mnth + 1
End If
calender = calender + 1
Loop
Set rs2 = cn.Execute(" UPDATE CONTACT SET [QUARTER 1TH] = " & q1 & ", [QUARTER 2TH] = " & q2 & ",[QUARTER 3TH] = " & q3 & ", [QUARTER 4TH] = " & q4 & " WHERE GRPNO =" & rs.Fields(0) & " ")
rs.MoveNext
Loop
Label2.Visible = False
MsgBox "Finished updating quarter values"
End Sub

 

[lespaul]

Again, why bother to store this information when you can run a query to get it on the fly?

Your code example shows updating quarterly information, is that what you are ulimately after? Just change the PIVOT to the quarter instead of the month. If you are trying to make a report of this, then you don't even need the pivot, you can do that in the report formatting.

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[GSINC]

The problem with that is, each group has a different calender year one group might have a calender that runs June to June or September to September. So I needed a away to look at the amount spent by quarter based on each groups calender year. I am sure that a better way exsists to acheive this, I do not know what that is. So I am looking to populate the table in the manner shown above. Anythoughts?