Sequential Numbers in a query (1 Viewer)

BGIBERT

New member
Local time
Today, 08:35
Joined
Jun 28, 2020
Messages
4
Hello to all. I am new to this forum and have a problem that I am sure has an easy fix. I have a table of invoices that contains duplicate invoices. (There should be no duplicate invoices.) I have a query that identifies only the duplicate values, and a row exists for each duplicate invoice showing the different statuses (causing the duplicate). What I need to do is create a query to:

1) Add a field to the query to generate a sequential number for each record returned. Like an autonumber in a table. See ID in below table.
2) Add a field to the query to generate a second sequential number that resets to 1 after each change in the Invoice number. (See Sequence in below table.)

To simplify this, I have described the query results I am looking for in the below in a table. ID and Sequence number are what I need to create.

IDInvoiceSequenceStatus
12222221Open
22222222Closed
33333331Open
43333332Closed
53333333<No Status Found>
 

plog

Banishment Pending
Local time
Today, 08:35
Joined
May 11, 2011
Messages
11,611
We need more information about how you expect the Status field to play a part of this. Should Open come before Closed come before Null? Or does that really matter? If it does matter what other Status values do you have and how should they fall into the sorting order?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:35
Joined
May 7, 2009
Messages
19,169
select id, invoice, dcount("1", "yourTableName", "ID <= " & [ID]) As Sequence, Status from yourTableName Order By id Asc;
 

BGIBERT

New member
Local time
Today, 08:35
Joined
Jun 28, 2020
Messages
4
select id, invoice, dcount("1", "yourTableName", "ID <= " & [ID]) As Sequence, Status from yourTableName Order By id Asc;
We need more information about how you expect the Status field to play a part of this. Should Open come before Closed come before Null? Or does that really matter? If it does matter what other Status values do you have and how should they fall into the sorting order?
Status is insignificant. Just need to
We need more information about how you expect the Status field to play a part of this. Should Open come before Closed come before Null? Or does that really matter? If it does matter what other Status values do you have and how should they fall into the sorting order?
Status is insignificant. I complicated the issue by introducing this field in my question. I just need do determine how to create a sequence of numbers that restarts at 1 for each change in invoice. I'd also like to know how to generate an ID number that increments for each row in the query results. Like autonumber in a table. See below.



IDInvoiceSequence
12222221
22222222
33333331
43333332
53333333
 

June7

AWF VIP
Local time
Today, 05:35
Joined
Mar 9, 2014
Messages
5,423
SELECT ID, Invoice, DCount("1", "yourTableName", "Invoice=" & [Invoice] & " AND ID <= " & [ID]) As GrpSeq, Status FROM yourTableName;

or

SELECT ID, Invoice, (SELECT COUNT(*)+1 FROM yourTableName WHERE T1.Invoice = Invoice AND t1.[ID]<[ID]) AS GrpSeq
FROM yourTableName AS T1;

or

build a report using Sorting & Grouping features and RunningSum property of textbox
 

Guus2005

AWF VIP
Local time
Today, 14:35
Joined
Jun 26, 2007
Messages
2,645
what you need to do is normalise your database.
All records in the invoice tabel should be unique
Make a status table and rename the invoice table to InvoiceStatus. Sort on date (you must have a datefield somewhere) and add the ID field Autonumber
Create a table Invoice with unique Invoices.

From there is should be easier to fix your problems.
 

Users who are viewing this thread

Top Bottom