Access - sequence number

[ad2015]

Hi,

I am using Access 2003,how can I have a sequential counter in a query. No vba codes please.


profile_id account_num
1234 11111
1234 22222
3456 33333
3456 44444

I want the output as:

profile_id account_num Sequence
1234 11111 1
1234 22222 2
3456 33333 1
3456 44444 2

many thanks
ad

[twinnyfo]

ad,

Just add an additional field to your query which refers to itself and counts the number of records that match your criteria. For the following, I am assuming the name of your query (in your first list) is called "qryProfileAccounts". I am further assuming the Field names are "ProfileID", "AccountID" and "Sequence". Of course, you will have to modify the following to account for the true names. Additionally, assumption is that these first two fields are numeric.

Add a Field to your query as such:

DCount("[AccountID]","qryProfileAccounts", _
    "[ProfileID] = " & [ProfileID] & _
    " AND [AccountID] < " & [AccountID])+1 AS Sequence

Please let me know if this works and if you need further assistance.

Hope this hepps!

[ad2015]

Hi twinny

Im getting error as incorrect syntax. Appreciate if you can suggest a way to do this in Access 2003, using basic queries.

Else, please guide me with VBA for access 2003, im really new with sql.

Thanks a lot

[twinnyfo]

Is the syntax error showing up in the Query builder? If you are building in the MS Access query builder, your field must be all on one line and will look like this:

Sequence: DCount("[AccountID]","qryProfileAccounts", "[ProfileID] = " & [ProfileID] & " AND [AccountID] < " & [AccountID])+1