Subtotal in Query Using Expression Builder

[pnpez]

I have a query that I need to do a subtotal in, but so far can only figure out how to do a running sum. I can't do this in a report because of other things running. The running sum would be fine if I could figure out how to only show the last total amount of the group, and NOT each record's total. Does anyone know how to do this in an expression builder? Here's what I have thus far for my running sum:

Cum Tot after Sale: Val(DSum("[License Fees with Emp].[Commissionable LF]","License Fees with Emp","[ID] <= " & [License Fees with Emp].[ID] & " and [License Fees with Emp].[Employee #] = " & [License Fees with Emp].[Employee #] & " and [License Fees with Emp].[Type Numb] = " & [License Fees with Emp].[Type Numb] & " and [License Fees with Emp].[LFT Numb] = " & [License Fees with Emp].[LFT Numb])).

There's an additional field called "Hold", that contains either the number's 1 or 2. In another field I just want to subtotal my amounts by all records that contain only number 1, by the group [Type Numb].

If anyone has a better way I'm all ears.

Thanks

 

[Alansidman]

Look at this video tutorial. It should give you an idea on how to approach this issue.

http://www.datapigtechnologies.com/flashfiles/Unionquery.html

 

[pnpez]

Says "Website not yet available"

 

[Alansidman]

Opens ok for me. Are you in a work environment that restricts your access to sites? If so, then you may need to talk to your IT administration. If somewhere else, you may need to contact your ISP.

 

[pnpez]

For what ever reason clicking on the address you posted produced much better results than copying and pasting the shortcut. Anyway, the video was quite helpful, so I just need to apply it to what I'm trying to do. Yikes!!!

Thanks very much.

 

[pnpez]

Hey Alan,

This doesn't seem to be working on multiple groupings. For instance, if you add
a field named "Gender" to your query, so if you have REP, Month, Gender, FY01, FY02 and you want to subtotal on the REP and just the males, I can't get that to work.
Any suggestions?

Thanks

 

[Alansidman]

Suggest you post a copy of your db so that we can see the whole picture. Make sure to compact and repair, remove confidential data and replace with dummy data. We do not need to see all data but only a sample that can be tested. Be sure to indicate what the expected results would be.

Alan