auto index

[actros]

l have a table in access 2003
with this fields

id = auto number
customerid = mumber
cars = text

and i want to make a field who have the data:
customerid and "-" and if the customerid change the index of the cars
who have
Who can i do that from Query or from VBcode !!!

Thanks , sorry for my English..

sample:
id customerid cars
new_field
-------------------------------------------------------------------------------------------
1 101 xxxx 101-1
2 102 xxxx 102-1
3 102 xxxx 102-2
4 102 xxxx 102-3
5 103 xxxx 103-1
6 103 xxxx 103-2
7 104 xxxx 104-1
8 104 xxxx 104-2
9 104 xxxx 104-3
10 104 xxxx 104-4
11 105 xxxx 105-1
12 106 xxxx 106-1

 

[Michel Walsh]

Hi,




SELECT a.id, a.customerID, LAST(a.cars), a.customerID & "-" & COUNT(*)

FROM myTable AS a INNER JOIN myTable AS b
ON a.customerID = b.customerID
AND a.id >= b.id

GROUP BY a.id, a.customerID





Hoping it may help,
Vanderghast, Access MVP