Sum over group in Query...

[Sprawl]

Hi everyone. I'm having some issues creating a query i need for work. I'm able to do this on the report level with a group and footer to get this total. however due to further complexity and Queries that will call upon this one, it needs to be in the first level query i create.

What i have is a database. There are Employees, Dates Paid, timesheet dates, Pay TYPE and hours. here is an Example of a few lines of data. this is how the database is created. it creates a new record for every different pay type paid that week.

EMP#------TRXDATE------TS-DATE----TYPE-----HRS
1----------01/28/07-------01/28/07----100------44.00
1----------01/28/07-------01/21/07----100------44.00
1----------01/28/07-------01/28/07----300-------7.00
3----------01/28/07-------01/28/07----100-------1.00


what i need the query to show for this data would be

EMP#-----TRX DATE----TS-DATE-----REG(100)-OT(300)--TOTAL
1---------1/28/07------1/28/07------44--------7---------51.00
1---------1/28/07------1/21/07------44--------0---------44.00
3---------1/28/07------1/28/07-------1--------0----------1.00

what it basically is would be merging everything so that each TS-DATE would get it's own line in the Query,

any and all ideas and recommendations would be more than greatly appreciated!

 

[stallyon]

To tackle this, first I'd change the query to a make table query. this will ensure all future queries i create run quickly.

then i'd create 2 queries, one for the normal pay and then one for the overtime pay but still include fields for normal pay and overtime pay and set these to 0 so that they can be used in a union query.

then i'd create the union query containing the 2 queries above, group them by EMP#, TRXDATE and TS-DATE, sum the normal pay and overtime pay fields and then total the 2 sums at the end.

HTH