Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-13-2019, 02:09 PM   #1
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
DSUM in Query

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............Am t..........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.

madcats is offline   Reply With Quote
Old 08-13-2019, 02:48 PM   #2
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,144
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: DSUM in Query

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by June7; 08-13-2019 at 03:02 PM.
June7 is offline   Reply With Quote
The Following User Says Thank You to June7 For This Useful Post:
madcats (08-14-2019)
Old 08-13-2019, 02:49 PM   #3
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: DSUM in Query

have you tried

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

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
madcats (08-14-2019)
Old 08-13-2019, 04:46 PM   #4
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: DSUM in Query

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
madcats is offline   Reply With Quote
Old 08-13-2019, 04:53 PM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: DSUM in Query

looks like you have

[Desc]>='"

it should be

[Desc]<='"
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-13-2019, 05:12 PM   #6
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: DSUM in Query

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?
madcats is offline   Reply With Quote
Old 08-13-2019, 06:34 PM   #7
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,144
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: DSUM in Query

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.

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-13-2019, 07:33 PM   #8
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DSUM in Query

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;
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
madcats (08-14-2019)
Old 08-14-2019, 12:17 AM   #9
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,132
Thanks: 40
Thanked 3,611 Times in 3,486 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: DSUM in Query

@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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 08-14-2019, 05:02 AM   #10
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: DSUM in Query

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 by madcats; 08-14-2019 at 05:38 AM.
madcats is offline   Reply With Quote
Old 08-14-2019, 07:26 AM   #11
arnelgp
Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 7,966
Thanks: 64
Thanked 2,536 Times in 2,436 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: DSUM in Query

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?
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-14-2019, 07:54 AM   #12
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: DSUM in Query

Quote:
Originally Posted by arnelgp View Post
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.
madcats is offline   Reply With Quote
Old 08-14-2019, 11:11 AM   #13
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,144
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: DSUM in Query

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"?
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-14-2019, 03:01 PM   #14
madcats
Newly Registered User
 
Join Date: Jun 2005
Posts: 36
Thanks: 9
Thanked 1 Time in 1 Post
madcats is on a distinguished road
Re: DSUM in Query

Quote:
Originally Posted by June7 View Post
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.
madcats is offline   Reply With Quote
Old 08-14-2019, 04:16 PM   #15
June7
Newly Registered User
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,144
Thanks: 0
Thanked 503 Times in 499 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: DSUM in Query

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")

__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using Dsum in a VBA query grenee Modules & VBA 5 02-22-2018 02:37 AM
DSum in query HELP Giovi Queries 1 02-21-2017 06:07 PM
Can't DSUM in Query with Yes/No mikebrewer Queries 2 11-24-2014 06:56 AM
DSUM on query. Fifa Forms 5 07-15-2009 10:39 AM
Using DSum in Query jlcford General 4 06-30-2009 07:04 AM




All times are GMT -8. The time now is 12:25 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World