Need a sequential number for each record returned by query in access.

[sburch032870]

So I have a database that issues transmittal documents for each client. Each client has multiple projects that I manage. My DB has a master transmittal number (Primary Key) that keeps the records in order for the client. What I really need to do is from the single Primary Key (here it is the master transmittal number) that no matter what other records contain, the query which will pull by project, should return any records issued for that project ordered by the Primary Key (here ID) but then adding a sequential number to a field based on the records returned in the query.

This way if I issue only 5 transmittals to project 611 out of 1000 transmittals to other projects it will sequence the query records first in order of the Primary Key then insert a sequential number into a query-number field which I will use on the report and form side as the sequential transmittal number for that project by formatting it in either the form or report. That way I have a master sequential number for the document type issued for each client, but then when I have to answer a subpoena, I can pull the appropriate records with sequential numbering and they do not think that I am skipping records because the transmittal number is from the master list where only a few records are relevant or were issued for this project.

I have put together a sample database that the has the basics of what the main system has in it. The only real issue is that I need this code/macro/whatever to be portable so that I can just drop it into the next client DB if possible. If not I could build the client DB around a template transmittal DB that was working this way. Link --- http://www.sburch.com/query/Transmittal System.accdb

I also uploaded a printed transmittal with some notes to better display what I am in the end needed to end up with. http://www.sburch.com/query/Transmittal Numbering Project Page 001.jpg

Screen of TRX Table http://www.sburch.com/query/TRX Table.png

I have been searching for an answer to this for a year now, I have spent hours looking at every possible idea but none seem to ever work properly, that may be me implementing them incorrectly (very likely) but I am at a loss and could really use some help. Since I have had so much luck from all of the nice people here withe Excel questions, my wife suggest that I post this here, I did not even know that there was an Access forum!!!!



Thank you for reviewing this....there is a bunch of other fields all in the same table for simplicity, I am not an expert on any level and when I turn these files over for a claim, I do not want to have to explain a bunch of queries are the main reasons.

Table name = "Tbl - {TRX} Trx Data"

TRX.0001	8010	Sample Company 1	First Last	11/2/2014
TRX.0002	8017	Sample Company 1	First Last	11/4/2014

<caption>
</caption> <thead>
[TH="bgcolor: #c0c0c0"] TLogID
[/TH]
[TH="bgcolor: #c0c0c0"] ProjectNo
[/TH]
[TH="bgcolor: #c0c0c0"] ToName [/TH]
[TH="bgcolor: #c0c0c0"] Attn
[/TH]
[TH="bgcolor: #c0c0c0"] TDate
[/TH]

</thead> <tbody>
</tbody> <tfoot></tfoot>

 

[sburch032870]

One thing that may help make this more clear is I am looking to have this from a project select query.

TRX.0002	8017	1	Sample Company 1	1213 Street

<caption> Copy Of Qry - {TRX} Trx Print </caption> <thead>
[TH="bgcolor: #c0c0c0"] TLogID [/TH]
[TH="bgcolor: #c0c0c0"] ProjectNo [/TH]
[TH="bgcolor: #c0c0c0"] TRXNoForProject [/TH]
[TH="bgcolor: #c0c0c0"] ToName [/TH]
[TH="bgcolor: #c0c0c0"] Address [/TH]

</thead> <tbody>
</tbody> <tfoot></tfoot>