Finding only the first of an ID and displaying in a query 1

[vise]

Hi,
I have a table of company ID's, so if 10 people in company -> 1 ID. Is there a way I could create another numbering system on the side of the company ID. Example:

Comp.
ID Name Address (UniqueID) <- add in?
1 Erica 1 Willow 1
1 John 1 Willow 2
2 Rocky 3 Willow 1
3 John 4 Willow 1
3 Ryan 4 willow 2

Table is not ordered, so the ID's are everywhere.
Thanks for your help.

 

[dhookom]

Is there a reason that Erica is 1 and John is 2 and not the other way?
Is there a reason you need to do store this value in a field rather than calculate it in a query?


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[vise]

How may I calculate it in a query? I am unsure of that. There is no reason behind the number of Erica and John it could be either way. But I am quite interested on how to calculate the value in a query..

 

[PHV]

One way:
SELECT ID, Name, Address, (SELECT Count(*) FROM yourTable WHERE ID=C.ID AND Name<=C.Name) AS UniqueID
FROM yourTable As C

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vise]

Thank you for your help.
I was able to initiate this query, but I wish for it to be automatic. So it searches through the table and assigns the UniqueID by itself, I have an extensive database, 50000+ records so do it on a one by one basis would take quite some time.

 

[PHV]

Another way:
SELECT A.ID, A.Name, A.Address, Count(*) AS UniqueID
FROM yourTable As A INNER JOIN yourTable As C ON A.ID=C.ID AND A.Name>=C.Name
GROUP BY A.ID, A.Name, A.Address

Having a composite key on (ID,Name) may improve speed.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vise]

Thanks for your help. It worked wonderfully. Here's a star for you

 

[vise]

oh also, just a side question, if you don't mind. any way that the query just display the first found record on a company basis. Eg. 10 employees -> 1 company ID. Displays only the first employee found from that one company ID.

 

[PHV]

Simply this ?
SELECT A.ID, Min(A.Name) As FirstEmployee, A.Address
FROM yourTable As A
GROUP BY A.ID, A.Address

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[vise]

Hmm.. It just gives me the same table, with the Name portion replaced by FirstEmployee. Still multiple records showing for each company. Do I have to do the count query portion first?