DSUM in Query (1 Viewer)

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
I want a running total in a query. I have used a DSUM in the past but needed help with that one also. I have looked at several threads, copied and pasted and still can't get this DSUM to work. This is what the query looks like:

SELECT InfoTable.InvID, InfoTable.Level, InfoTable.Desc, InfoTable.Amt, DSum("[Amt]","[InfoTable]","InvID<='" & [InvID] & "' AND Desc'" & [Desc] & "'") AS RSum
FROM InfoTable
ORDER BY InfoTable.InvID, InfoTable.Level, InfoTable.Desc;


AMT is a number
I want a running total for each InvID/Desc
Each Desc will be unique inside each InvID

This is the data I am looking for:

InvID...........Level...........Desc............Amt..........RSum
ADH1111........1............Balance...........10.0..........10.0
ADH1111........2.............Serial1............-5.0............5.0
ADH1111........2.............Serial2............-2.0............3.0
FRA2222........1.............Balance...........99.1...........99.1
FRA2222........2..............Serial1............-6.0...........93.1
FRA2222........2..............Serial2..........-15.2...........77.9
LUM3535........1..............Balance..........3.0.............3.0
LUM3535........2..............Serial1...........-2.0............1.0
LUM3535........2..............Serial2...........-2.0...........-1.0

Any suggestions would be appreciated.
 

June7

AWF VIP
Local time
Today, 06:36
Joined
Mar 9, 2014
Messages
5,423
Need = sign for InvID and <= sign for the Desc criteria.

DSum("[Amt]","[InfoTable]","InvID='" & [InvID] & "' AND [Desc]<='" & [Desc] & "'") AS RSum

Don't see need for Level in ORDER BY.

Or build a report and use its Sorting & Grouping features and textbox RunningSum property.

Level and Desc are reserved words. Might consider changing these field names.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 14:36
Joined
Feb 19, 2013
Messages
16,553
have you tried

DSum("[Amt]","[InfoTable]","InvID='" & [InvID] & "' AND Desc<='" & [Desc] & "'")
 

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
Thanks for the quick responses, there is progress! Got some results, not quite right yet though. Looks like it is not doing the totaling in the correct order. I want to start with the inventory balance, that is why I put the levels in there, and then reduce that balance by each Serial #s usage amount.

InvID...........Level...........Desc............Am t..........RSum
ADH1111........1............Balance...........10.0 ...........3.0
ADH1111........2.............Serial1............-5.0............-5.0
ADH1111........2.............Serial2............-2.0............-7.0
FRA2222........1.............Balance...........99.1...........77.9
FRA2222........2..............Serial1............-6.0...........-6.0
FRA2222........2..............Serial2..........-15.2...........-21.2
LUM3535........1..............Balance..........3.0 .............-1.0
LUM3535........2..............Serial1...........-2.0............-2.0
LUM3535........2..............Serial2...........-2.0...........-4.0
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:36
Joined
Feb 19, 2013
Messages
16,553
looks like you have

[Desc]>='"

it should be

[Desc]<='"
 

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
Hmmm,

I do have it as [Desc]<='", It seems it is doing the sort as Serial1, Serial2, Balance.

I want Balance, Serial1 and Serial2.

I changed it to >= to see what I got - it sorted Balance, Serial2, Serial1

Do you think it matters that Balance is text and the Serial numbers are numeric?
 

June7

AWF VIP
Local time
Today, 06:36
Joined
Mar 9, 2014
Messages
5,423
Yes, numbers sort before alpha in alpha/numeric ascending sort. Would be nice to show actual values. Either have to change values used in Desc or use another field to define sort order. A unique record identifier can serve this purpose - autonumber field might serve.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:36
Joined
May 7, 2009
Messages
19,169
do you have any date field? I am sure you have, all transactions Must have.
Code:
select InvID, Level, [Desc], Amt, 
    (Select Sum(T1.Amt) From yourTable As T1 
        Where T1.InvID = yourTable.InvID And T1.dateField <= yourTable.dateField) 
            As RunningSum From yourTable Order by InvID, dateField;
 

CJ_London

Super Moderator
Staff member
Local time
Today, 14:36
Joined
Feb 19, 2013
Messages
16,553
@madcats.

So to be clear, your desc column is not populated with

"Balance"
"Serial1"
"Serial2"

as shown in your first two posts but

"Balance"
"1"
"2"

For the future I strongly recommend you should show what you actually have/want
 

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
Thanks for all the help, I will try adding a autonumber field.

I am sorry about my data, I was trying to simplify. Yes the desc field is as you show, but it is a text field.
Balance
1
2

I thought having the Balance as "Level" 1 and the Serial numbers as "Level" 2 would take care of that sorting. That is why I had the Order By Level and then Order By Desc, The Desc order would be putting the serial numbers in order.

In this case, there is not a date field. The quantities shown are the amount used in each Serial number's Bill of Material. I am trying to find out what serial numbers will be short material, (the serial numbers with a negative running total).
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 22:36
Joined
May 7, 2009
Messages
19,169
you should consider putting a date field to your table.
it may answer a lot of questions,eg. when was the last inventory taken?
when did you get the negative stock?
how about monthly report, or weekly report?
all invoice total by date?
 

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
you should consider putting a date field to your table.
it may answer a lot of questions,eg. when was the last inventory taken?
when did you get the negative stock?
how about monthly report, or weekly report?
all invoice total by date?

Arnelgp,
I actually do have dates in another report(Inventory dates and date/time the serial number/BOM passes through a station in the production line).

I am developing this report for when we export our product. We have to reduce the quantity of material that our software says we have in the country on each export. On occasion we have a real world problem(more than we would like to admit);our computer balance says we are out of material when we are exporting that material in a BOM. This throws up red flags to the government, so I am trying to catch these occurrences before they happen.

I bring in the inventory balances from the software and add the BOMs for the serial numbers we are exporting that day. If there comes up a negative item, we remove it from the BOM before processing.

Thanks again for your help.
 

June7

AWF VIP
Local time
Today, 06:36
Joined
Mar 9, 2014
Messages
5,423
Also keep in mind that in a text field 10 will sort before 2 because alpha sort rules are used - one character at a time is evaluated.

Why not use a number field and 0 represents "Balance"?
 

madcats

Registered User.
Local time
Today, 07:36
Joined
Jun 24, 2005
Messages
36
Also keep in mind that in a text field 10 will sort before 2 because alpha sort rules are used - one character at a time is evaluated.

Why not use a number field and 0 represents "Balance"?

Actually used 0Balance instead of Balance and it worked.

Thanks Again.
 

June7

AWF VIP
Local time
Today, 06:36
Joined
Mar 9, 2014
Messages
5,423
You will still have sort issue if series exceed 9. If that is a possibility and series order is important and you don't want to convert to number field, format the field so there will be fixed number of characters and use that to control sort:

Format(Val([Desc]), "0000")
 

Users who are viewing this thread

Top Bottom