Add sequence number and restart for each emplid

[8172]

emplid SeqNbr
22222 1
22222 2
33333 1
33333 2
33333 3
55555 1

I have a query which has emplids. I need to add a sequence number that restarts numbering for each emplid. See example of the goal above.

Thanks!

 

[dhookom]

This question would be more appropriate in MS: Access Queries. However, you must define what field or fields determine the order. Why would one record have a SeqNbr of 1 and another of 2?

There should be some examples of how to do this in the query FAQs. If not, come back with your table name and significant fields with data types.

Duane
Hook'D on Access
MS Access MVP

 

[8172]

I don't think it is possible in a query so I was thinking that a module would be necessary.

The query pulls data from a table and adds some additional default fields. The query name is PG_COMM.
The main fields are:
EMPLID
EARN_CD
EARN_AMT
EARN_BEGIN_DT
EARN_END_DT

I need to add the sequence field and am open to doing this in anyway. In the end I will need to export the final either query or table to a .csv file as it will be uploaded to another system.

 

[MajP]

Yes it is definitely possible in a query. That is the way it should be done as Dhookum points out.

 

[dhookom]

Again, [tt][red]you must define what field or fields determine the order[/red][/tt]. Is the EARN_BEGIN_DT the sequencing field? If so, your query might look like:

SELECT PG_COMM.EMPLID,Count(PG_COMM_1.EMPLID) AS SeqNbr
FROM PG_COMM LEFT JOIN PG_COMM AS PG_COMM_1 ON PG_COMM.EMPLID = PG_COMM_1.EMPLID
WHERE PG_COMM.EARN_BEGIN_DT<=PG_COMM_1.EARN_BEGIN_DT
GROUP BY PG_COMM.EMPLID
ORDER BY PG_COMM.EMPLID;

Duane
Hook'D on Access
MS Access MVP