Hello, is there an easy way to have a query do a running sum or an auto increment in a field?

itsquad
itsquad used Ask the Experts™
on
Hello, is there an easy way to have a query do a running sum on a field?

Table1
Value      Account_No
1              44-5056450
1              JD-51675265
1              0155550006

I wrote the Dsum like this:
AutoValue: Dsum("Value"."Table1","[Account_No] ="& [Account_No])

and the error returns "Data Type mismatch in criteria expression."

Would like to see:
Table1
Value      Account_No
1              44-5056450
2              JD-51675265
3              0155550006

Just trying to get a field to do an Auto Increment.

Any suggestions?

Thanks, Kevin


In the DSum funtion, I tried
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
tried this instead?

Dsum("Value"."Table1","[Account_No] ='"& replace([Account_No],"'","''") & "'")

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
A running sum or sequential numbering?  If the latter see Gustav Brock's article https://www.experts-exchange.com/articles/33069/Sequential-Rows-in-Microsoft-Access.html
itsquadSystems Admin

Author

Commented:
Hi Ryan, I tried that, and it didn't work... there are 404 records and your line produced duplicate 1's , 2's etc...
Hi Daniel, I looked at this and it's probably my answer, but I lack a bit of skill weaving it into my select query, perhaps you can help?

I believe the code below is what I need to incorporate into my select query:
SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

How do I get my code to play nice with the above code?

SELECT [Rent Charges Preview Export].ORDER_DATE, dbo_CUST.CATEGORY_CODE, 1 AS AutoValue, "E562" AS Crossing_Entry_Account, dbo_CUST.CUST_CODE, 1 AS Document_Balance, DateSerial(Year(Date()),Month(Date()),0) AS Document_Date, DatePart("m",[Document_Date]) & Right(DatePart("yyyy",[Document_Date]),2) & "APF" AS Document_Number, "OA" AS Terms, "A131" AS AR_Control_ACCT, "Auto Pay Fee" AS Doc_Alias, 9 AS Doc_Type, DateSerial(Year(Date()),Month(Date())+1,0) AS Due_Date, "SUP" AS Sales_Rep
FROM [Rent Charges Preview Export] INNER JOIN dbo_CUST ON [Rent Charges Preview Export].CUST_CODE = dbo_CUST.CUST_CODE
WHERE ((([Rent Charges Preview Export].ORDER_DATE)>#8/14/2016#) AND ((dbo_CUST.CATEGORY_CODE)="AUTOPAY"));

Thanks, Kevin
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

itsquadSystems Admin

Author

Commented:
I ended up googling Running Sum and got it!  Here's the article: https://theaccessbuddy.wordpress.com/2014/05/04/using-ms-access-to-create-a-running-total-or-a-cumulative-sum-part-1-of-2/

Document_Number: DatePart("m",[Document_Date]) & Right(DatePart("yyyy",[Document_Date]),2) & "APF" & DSum("[Value1]","Rent Charges Preview Export","[Rent Charges Preview Export].[ORDER_NO]>='" & [Rent Charges Preview Export].[ORDER_NO] & "'")

Thanks for helping me out with this though!!  :D
itsquadSystems Admin

Author

Commented:
DSum("[Value1]","Rent Charges Preview Export","[Rent Charges Preview Export].[ORDER_NO]>='" & [Rent Charges Preview Export].[ORDER_NO] & "'")
itsquadSystems Admin

Author

Commented:
Ok, so now that worked for that situation, I'm now back to square one with trying to create a unique Number per record... so still need some help with that...

I believe the code below is what I need to incorporate into my select query:
SELECT RowNumber(CStr([ID])) AS RowID, *
FROM SomeTable
WHERE (RowNumber(CStr([ID])) <> RowNumber("","",True));

How do I get my code to play nice with the above code?

SELECT [Rent Charges Preview Export].ORDER_DATE, dbo_CUST.CATEGORY_CODE, 1 AS AutoValue, "E562" AS Crossing_Entry_Account, dbo_CUST.CUST_CODE, 1 AS Document_Balance, DateSerial(Year(Date()),Month(Date()),0) AS Document_Date, DatePart("m",[Document_Date]) & Right(DatePart("yyyy",[Document_Date]),2) & "APF" AS Document_Number, "OA" AS Terms, "A131" AS AR_Control_ACCT, "Auto Pay Fee" AS Doc_Alias, 9 AS Doc_Type, DateSerial(Year(Date()),Month(Date())+1,0) AS Due_Date, "SUP" AS Sales_Rep
FROM [Rent Charges Preview Export] INNER JOIN dbo_CUST ON [Rent Charges Preview Export].CUST_CODE = dbo_CUST.CUST_CODE
WHERE ((([Rent Charges Preview Export].ORDER_DATE)>#8/14/2016#) AND ((dbo_CUST.CATEGORY_CODE)="AUTOPAY"));

Thanks, Kevin
Distinguished Expert 2017

Commented:
There are a lot of posts on this topic and many recommend DSum() which is the worst possible solution.  Keep in mind that this is a trivial problem when producing a report or running a VBA code loop because both processes are sequential. One record is processed and then another from the first record to the last.  The input file must be sorted in the sequence you want the numbers to be generated.  However, queries are quite different since SQL is based on set operations.  That makes generating a sequence number or running sum on the  fly very inefficient so it is generally just too slow for recordsets larger than a few thousand rows.  Additionally, in order to produce the correct results, you MUST have a unique identifier to to use to control the sequence.

Please tell us exactly what problem you are trying to solve so we can offer other alternatives.  For example, is this a one time requirement or will the number always be generated dynamically?  Is it a problem that RecordA gets a different number each time the query runs?
itsquadSystems Admin

Author

Commented:
Thanks Pat! I exactly need a different number per record, does not need to be in order or sequence, but would like to keep to 4 character max, the most number of records I'm dealing with would be 1,200 at a time.  

I'm making an XLS spreadsheet, exported from Access, to be imported into our Accounting Software.
These are all invoices that are being imported, so each charge needs a unique document #, yet be grouped
as similar type invoices.

I use the date as part of the document # and an abbreviation of the type of invoice being imported.
So an example would be 1018APF____  so that's "Oct. 2018 Auto Pay Fee" to the bookkeepers.
But then I need (up to 4 digits) random unique numbers so it would look something like this...
1018APF2077

Thanks!
itsquadSystems Admin

Author

Commented:
PS - this is a once a month need for 8 different xls import files.
Distinguished Expert 2017

Commented:
What is the point of a unique number that you don't store in your table?  I would generate the number when each record is added.  To do that, you would use a dMax() in your form's BeforeUpdate event to obtain the next valid number.

NewSeqNum = Nz(dMax("SeqNum"), "yourtable", "somefield = " & Me.SomeField), 0) +1

"SomeField" is the field or possibly multiple fields that you are using for a group.

Now if someone calls and asks about record 123 from groupx, I can have a conversation with them.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Assuming that you have one or more fields in your table which can be used to sort the data, I find a query that looks like the following will work well.
SELECT T.DateField, T.Field1, T.Field2, T.Field3, T.Field4, Count(t1.Field1) as SeqNum
FROM yourTable as T
INNER JOIN yourTable as T1 on T1.DateTimeField <= T.DateTimeField 
GROUP BY T.DateTimeField, T.Field1, T.Field2, T.Field3, T.Field4

Open in new window

Notice that the join clause here uses a <= criteria, which can only be entered in the SQL view.  What this does, is create a recordset which contains each record in the table, joined to each record which preceeds it in the table (I prefer to use DateTime fields for this by Identify fields work even better), something like:

Assume your table only contains one field with values (3, 4, 7, 8)  The query to join this would look like:
SELECT T.Field1, T1.Field1, Count(T2.Field1) as SeqNum
FROM yourTable as T
INNER JOIN yourTable as T1 on T1.Field1 <= T.Field1
ORDER BY T.Field1, T1.Field1

Open in new window

This would result in records that look like:
3             3            1
4             3            1
4             4            2
7             3            1
7             4            2
7             7            3
8             3            1
8             4            2
8             7            3
8             8            4

But when you drop the T1.Field1 from the select statement, and add a group by clause on all of the fields from T, as below:
SELECT T.Field1, T1.Field1, Count(T2.Field1) as SeqNum
FROM yourTable as T
INNER JOIN yourTable as T1 on T1.Field1 <= T.Field1
Group By T.Field1

Open in new window

You get:
3            1
4            2
7            3
8            4

But again, this depends on you having one or more fields which you can use in the join that define a unique sort order.  If you have duplicates, say in your date field, then you must identify a secondary column, maybe an account number or something like that which would define ensure that you don't have any duplicates, because this will screw up your numbering scheme.

if you cannot define a precise set of fields to sort by, then I would simply export the data to a temp (staging) table which contains an additional numeric field, which you can then loop through and add the values 1-n.  I would then run my export to Excel from that staging table.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial