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:

        Name_c   Date_Id       Peter   08/08/2011       Peter   07/07/2011       Peter   05/06/2011       Bill   08/08/2011       Bill   06/06/2011       John   05/06/2011       John   02/04/2011       John   11/11/2010       Mary   07/08/2011       Mary   03/08/2011       Mary   07/07/2011       Mary   10/10/2010       Paul   03/08/2011       Paul   05/05/2011       Adam   08/07/2011

h**p://img849.imageshack.us/img849/2491/58944325.jpg

What I need:

Name_c Date_Id Num_by_name Peter 08/08/2011 1 Peter 07/07/2011 2 Peter 05/06/2011 3 Bill 08/08/2011 1 Bill 06/06/2011 2 John 05/06/2011 1 John 02/04/2011 2 John 11/11/2010 3 Mary 07/08/2011 1 Mary 03/08/2011 2 Mary 07/07/2011 3 Mary 10/10/2010 4 Paul 03/08/2011 1 Paul 05/05/2011 2 Adam 08/07/2011 1

h**p://img703.imageshack.us/img703/9956/43080150.jpg

Many thanks!!!!

 

[plog]

First:

The names and dates are ordered

Is not a valid statement when you are talking about a table. Data only has an order when you explicitly state that order in a query by using the ORDER BY clause. Just because it appears to when you view it, data in a table can not be counted on to have any order.

Second, what is the purpose of this ID you want to add? Generally, an ID field is only used to internally reference unique rows of data. This means that whatever number a record receives (1 or 2 or 578) means nothing other than you can use 1 or 2 or 578 to specifically refernce that record of data.

What do you hope to accomplish with this ID field you want?

 

[Annita]

Thanks plog for your answer.

The ORDER BY has two sorts, first by name (ascending) and then by date (descending) (SQL: ORDER BY Tabla1.Name_c, Tabla1.Date_Id DESC;)

The purpose of the query that I need is to "rank", per person, the different dates I have...

 

[stopher]

Hi Annita

Do you want a query or do you want a column in a table filled in?

Here's how to do it with a query:

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

Just change myTable to the name of your source table.

Note that I had to use the Format() function to force the date to be formatted in US format (since I'm not in the US).

If you want to update a column in a table then it's probably best to do it using recordsets in VBA as the above isn't terribly efficient. But you could use the above as an update query if speed is not an issue.

Probably best not to store the ranking anyway as it will change as records change.

hth
Chris

 

[Annita]

Many, many thanks Stopher!!!!

That's exactly what I wanted!!!! Thank you!!!