UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Running Count, Access 2016    
 
   
ordnance1
post Dec 5 2018, 01:21 PM
Post#1



Posts: 567
Joined: 7-May 11



Is there any way to add a Running Count to my query? Where there is a duplicate date I would want the count to increase by one. I can not count on the user to input data in the correct sequence. This will be a base query to be used by another query to create a running sun column.

For the running sum column I was going to use the Transaction_ID, but if the user were to add something from say 12/2/2018 the Transaction_ID would be 19 and the running sum would be wrong

Attached File  Untitled_picture.png ( 30.72K )Number of downloads: 2

This post has been edited by ordnance1: Dec 5 2018, 01:22 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 5 2018, 01:33 PM
Post#2


UA Admin
Posts: 34,136
Joined: 20-June 02
From: Newcastle, WA


See if you can use this approach on Allen Browne's site.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
Larry Larsen
post Dec 5 2018, 03:25 PM
Post#3


UA Editor + Utterly Certified
Posts: 24,244
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi

Or: HOW TO CREATE A RUNNING TOTALS QUERY IN MICROSOFT ACCESS

( the way I read the post..)
thumbup.gif

--------------------
"Time...We have exactly the same number of hours per day that were given to Helen Keller, Pasteur, Michaelangelo, Mother Teresa, Leonardo da Vinci, Thomas Jefferson, and Albert Einstein"
H. Jackson Brown
Go to the top of the page
 
BruceM
post Dec 6 2018, 08:37 AM
Post#4


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


I'm curious about something. Perhaps I am missing the point entirely. It wouldn't be the first time.

QUOTE
For the running sum column I was going to use the Transaction_ID

I assume you mean you would be using the field to order the records, because I can't imagine a reason to do a running sum of the ID field value. What exactly are you trying to count?

Note that if the intent is to number each record sequentially, and the intended output is a report, you can do sequential numbering very simply using report tools alone.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 09:03 AM
Post#5


UA Admin
Posts: 34,136
Joined: 20-June 02
From: Newcastle, WA


I understood "Running Count" to mean a sequential, increasing count of records, not a sum, or total of something.

In part, I relied on this statement: "Where there is a duplicate date I would want the count to increase by one." I.e. a sequential count, not a sum or total.

Only the OP can tell us though....

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
ordnance1
post Dec 6 2018, 12:06 PM
Post#6



Posts: 567
Joined: 7-May 11



You are correct.

I should backup a bit. I am working on a project for my brother where he has to track banking transactions where he has had to step in and temporarily take over an organizations finances.

My ultimate goal is in fact a running sum query, so as transactions are entered the running sum is updated. A running sum query needs a sequential field to test against in my case that would be the Primary Key "Transaction_ID". My problem is that sometimes transactions might not be entered sequentially. So maybe you enter a transaction for 12/5/2018 and the later you realize you need to add a transaction for 12/4/2018, which would have a larger Transaction_ID. Since the running sum query is basically sorting on the Transaction_ID the results will be incorrect.

If I were to use the Transaction_Date in the running sum query it fails as soon as there is a duplicate date. So my thought was to create a running count query (sorted by date) and then use that query as the basis for the running sum query.

Hope that makes more sense.
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 12:50 PM
Post#7


UA Admin
Posts: 34,136
Joined: 20-June 02
From: Newcastle, WA


That'll work, and Allen Browne's method will do the trick.

If you need a tie-breaker for dates, though, you'll have to rely on another field, such as a sequential Primary Key, as well as the dates. Is there something available, perhaps a time field?

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
ordnance1
post Dec 6 2018, 02:44 PM
Post#8



Posts: 567
Joined: 7-May 11



I would think in the case of duplicate dates I could break the tie with the PK.

Have to head out to Seattle for my sisters surgery but will get back to this on Sunday.
This post has been edited by ordnance1: Dec 6 2018, 02:45 PM
Go to the top of the page
 
GroverParkGeorge
post Dec 6 2018, 03:24 PM
Post#9


UA Admin
Posts: 34,136
Joined: 20-June 02
From: Newcastle, WA


I hope the medical procedure goes well. Talk to you later.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Visit My Blog on Facebook
Go to the top of the page
 
BruceM
post Dec 6 2018, 03:25 PM
Post#10


UtterAccess VIP
Posts: 7,758
Joined: 24-May 10
From: Downeast Maine


QUOTE
I would think in the case of duplicate dates I could break the tie with the PK.

Indeed you could, by using it as the second sort field for instance.

Good luck to your sister. Safe travels.
Go to the top of the page
 
ordnance1
post Dec 9 2018, 03:41 PM
Post#11



Posts: 567
Joined: 7-May 11



Sorry I did not get back sooner. Everything is functioning as I hoped. Thanks for the link and everyone's time.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th December 2018 - 05:43 AM