How create custom unique ID

[Jessicahawk]

I have a similar question to this post https://www.accessforums.net/forms/h...say-39570.html but the route I took was a temporary table as an autonumber and append to my table that I need sequential numbering.

Although I've figured this out I have multiple queries that I need the data to pull from but would like only 1 table to update all my data. I need for the query to pull the data from the table but each query needs to start at 1.

[June7]

Textbox in report has a RunningSum property that can be used to generate a sequential value on-the-fly.

Generating a sequential number in queries maybe not so easy. Review http://allenbrowne.com/ranking.html

Also, another example, requires a unique identifier in the dataset - ID or date or whatever:
SELECT ID, DCount("*","tablename","ID<" & [ID])+1 AS NewID
FROM tablename
GROUP BY ID;


What is the reason for each query starting at 1?

[Jessicahawk]

I thought the report function would be be easiest for a non novice user like me and it worked however I'm trying to design a template that is uploaded to an accounting system. The running numbers will not work for all records because I have two different entry types, a debit and a credit.

I need my results to look similar to this:

1 00-10000 1,000
2 00-10003 2,000
3 00-10004 3,000
1 00-30000 1,000
2. 00-30000 2,000
3. 00-30000 3,000

I've figured out the formatting for the debit and credit entry but the sequential numbering seems to be much more difficult.

This is just one template that I need. The other template I need to concate (sp) two fields which is currently being done in a query but this also needs to be sequential numbering. While using one table for my data is not working because I have different queries that has rule sets that pull out certain data. So if I have a record in my table assigned an auto number and it's in my query as number 1 and the query is skipping the next record (2) it's assigning the number that is in the data table.

[Jessicahawk]

Also, the reason I have multiple queries is because of different companies I need to upload. Just to produce the 2 templates I need, I'm using a total of 4 queries, one for a debit side and another for a credit side.

[June7]

I understood enough of that to know you have a complicated situation. Best I can do is offer an example from my data.

Original data

ID	FAAID	District
1	6K8	TOK
2	ADK	SW
3	ADQ	PEN
4	AFE	SE
5	AKA	SW
6	AKN	SW
7	AKW	SE
8	ANC	N/A
9	ANI	SW
10	BCV	ANC

SELECT Airports.District, Airports.FAAID, Airports.ID, DCount("*","Airports","ID<" & [ID] & " AND District='" & [District] & "'")+1 AS NewGroupNum
FROM Airports
GROUP BY Airports.District, Airports.FAAID, Airports.ID
ORDER BY Airports.District, Airports.FAAID;

Query Results

District	FAAID	ID	NewGroupNum
ANC	BCV	10	1
N/A	ANC	8	1
PEN	ADQ	3	1
SE	AFE	4	1
SE	AKW	7	2
SW	ADK	2	1
SW	AKA	5	2
SW	AKN	6	3
SW	ANI	9	4
TOK	6K8	1	1