- Joined
- Apr 2, 2012
- Messages
- 2
- Reaction score
- 0
Service Totals DataFirst NameLast NameProgram CodeService CodeCountOfProgram CodeMinutesSUMOFMEASUREMichaelSmithInhouseAA190 minutes1 hrs. 30 minutes.MichaelSmithInhouseMeeting245 minutes0 hrs. 45 minutes.MichaelSmithInhouseOC AA190 minutes1 hrs. 30 minutes.MichaelSmithInhouseOC Group160 minutes1 hrs. 0 minutes.MichaelSmithOutide SrvGroup160 minutes1 hrs. 0 minutes.MichaelSmithOutide SrvOC Group160 minutes1 hrs. 0 minutes.StephenHenryInhouseAA490 minutes1 hrs. 30 minutes.StephenHenryOutide SrvGroup160 minutes1 hrs. 0 minutes.StephenHenryOutide SrvMeeting145 minutes0 hrs. 45 minutes.
SELECT [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code], [Service DATA Qry].[Service Code], Count([Service DATA Qry].[Program Code]) AS [CountOfProgram Code], [Unit of Measure] & " minutes" AS Minutes, Int([Unit of Measure]/60) & " hrs. " & ([Unit of Measure] Mod 60 & " minutes.") AS SUMOFMEASURE
FROM [Service DATA Qry]
GROUP BY [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code], [Service DATA Qry].[Service Code], [Unit of Measure] & " minutes", Int([Unit of Measure]/60) & " hrs. " & ([Unit of Measure] Mod 60 & " minutes.")
ORDER BY [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code];
Above is a sample of my Test Data and the SQL of the query that created the display. Here is my problem: I need the counted [CountofProgram Code] to be multipied by Minutes and the field SumofMeasure reflect that multiplication. i.e
MichaelSmithInhouseMeeting245 minutes0 hrs. 45 minutes
2 * 45 minutes = 1 hrs. 30 minutes
Since the fields are calculated in the query i can't get this to work. and i am clueless to how to do it in VBA. ANY help would be appreciated
SELECT [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code], [Service DATA Qry].[Service Code], Count([Service DATA Qry].[Program Code]) AS [CountOfProgram Code], [Unit of Measure] & " minutes" AS Minutes, Int([Unit of Measure]/60) & " hrs. " & ([Unit of Measure] Mod 60 & " minutes.") AS SUMOFMEASURE
FROM [Service DATA Qry]
GROUP BY [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code], [Service DATA Qry].[Service Code], [Unit of Measure] & " minutes", Int([Unit of Measure]/60) & " hrs. " & ([Unit of Measure] Mod 60 & " minutes.")
ORDER BY [Service DATA Qry].[First Name], [Service DATA Qry].[Last Name], [Service DATA Qry].[Program Code];
Above is a sample of my Test Data and the SQL of the query that created the display. Here is my problem: I need the counted [CountofProgram Code] to be multipied by Minutes and the field SumofMeasure reflect that multiplication. i.e
MichaelSmithInhouseMeeting245 minutes0 hrs. 45 minutes
2 * 45 minutes = 1 hrs. 30 minutes
Since the fields are calculated in the query i can't get this to work. and i am clueless to how to do it in VBA. ANY help would be appreciated