Sequential Numbering Of Multiple Records With Same ID ?

[The Brown Growler]

Hi,

I would be grateful for any help on how I can sequentially number some table records where there are multiple records with the same ID.

Using the table presented below as an example:

T_Customer_Matrix

[Customer ID], [Year], [Month]
WP-27, 2006, March
WP-27, 2006, April
WP-27, 2006, May


For [Customer ID], there may be up to 72 records each one with the same [Customer ID] but with different values for the [Year] column and different values for the [Month] column. What I hope to be able to do is to create a query that will allow add a new column to number the records per [Customer ID] sequentially with 1, 2, 3......... The records are currently sorted by [Customer ID], [Year], [Month] ascending.

Alternatively, if there is some type of update query that I can use directly on the table then I could create a new empty column in the table and populate this with whatever procedure will work.

Any help most appreciated.


Rgds
Growlos

 

[gemma-the-husky]

this is doable - but if you already have year and month columns, which you can use to sort the data (but note that months are alphabetic, and not correctly sorted without some work) - what is the point of the extra numeric id?

the problem with adding an artificail sequence - is that if you then need to insert data within a sequence, your sequence reference will become incorrect, and will need redoing anyway.


you would be better trying to construct a single ordered date field, (or a month number) rather than using a separate text field for the month, i feel.

 

[vbaInet]

It is possible in a query but what is the main purpose of doing this? Are you looking to use it in a report? It's much easier to do in a report.

By the way, is your month field properly sorting in ascending order?

 

[The Brown Growler]

Hi,

Thanks for the replies.

I have just realised I have a better field than the separate [Year] and [Month] fields. I have an actual date field named [Mth-Yr] with data in the format of:

[Mth-Yr]
01/03/2006
01/04/2006
01/05/2006

All the monthly values are set to the 1st day of the month so the revised requirement would be to sequentially number the records with the same [Customer ID] based on the ascending order of the[Mth-Yr] field.

Apologies for the confusion.


Rgds
Growlos

 

[The Brown Growler]

It is possible in a query but what is the main purpose of doing this? Are you looking to use it in a report? It's much easier to do in a report.

By the way, is your month field properly sorting in ascending order?

Yes, the [Mth-Yr] field is sorted in correct date ascending order. The work is required in connection with some other tables and queries and it is not for a report.


Thx for your reply

Rgds
Growlos

 

[vbaInet]

Something like:

SequentialNum: (Select Count(*) From [T_Customer_Matrix] As Q Where Q.[CustomerID] = [T_Customer_Matrix].[CustomerID] And Val(Format(Q.[Mth-Yr], "yyyym")) <= Val(Format([T_Customer_Matrix].[Mth-Yr], "yyyym")))

 

[The Brown Growler]

vbaInet,

Thank you for the code, I will try it out.


Rgds
Growlos

 

[boblarson]

Alternatively, if there is some type of update query that I can use directly on the table then I could create a new empty column in the table and populate this with whatever procedure will work.

This should NOT be stored. You should only use this information in a QUERY not store it in a table.