Adding an Id to a table

[Annita]

Hi all,

I have a problem. I would like to add an Id to a table that I have with Names and Dates. The names and dates are ordered but I would like to add a number using a query, but I don't know how. Can you please help me?

What I have:




What I need:


Many thanks!!!!

[pbaldy]

Generally this isn't the type of thing you want to save in a table, since it will change. I assume that when Peter comes/calls again the new record will be number 1, and the existing number 1 becomes 2, etc.

On a report you could use the Running Sum property to achieve this. In a query, you'd either use DCount() or a subquery. With either, you'd have a criteria that matched the name and counted dates greater than or equal to the current record.

[rpeare]

You're not adding an ID you're just adding a sequential number to each group of data (in your case names sorted by dates in descending order). Can I ask why you want to do this in a table? As Pbaldy pointed out if you intend, the next time peter has a record to assign it a value of 1 and increment all his existing records by 1 is an extremely cumbersome way to handle your data. If you can explain what you're trying to do as an end result we may be able to offer a better solution.

[Annita]

Got the solution from another forum, but just in case anyone else needs it...

SELECT Name_c, Date_Id, DCount("Name_c","myTable","[Name_c]='" & [Name_c] & "' AND [Date_Id]<=#" & Format([Date_Id],"mm/dd/yyyy") & "#") AS Num_by_Name
FROM myTable

[pbaldy]

Looks a lot like:

In a query, you'd...use DCount()....you'd have a criteria that matched the name and counted dates greater than or equal to the current record.

[Annita]

Yeah! Thanks pbaldy, but I needed some example code