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

1) How can i do that from a Query or from VBcode !!!

2) And how to make a new table to auto index this sample alone


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

 

[Allen Browne]

There are a couple of problems with what you propose:

The first is that Access 2000 and later cannot correctly find records in a
field that contains a hyphen. The results are inconsistent, depending on
whether the field is indexed or not. Here is the Microsoft Knowledgebase
article about that:
http://support.microsoft.com/kb/271661/en-us

The second problem is that new_field breaks data normalization rules:
a) It is not atomic (it contains 2 pieces of information.)
b) The value is dependent on the customerid field, and the value is wrong if
it doesn't match.

You could solve all 3 of these problems if new_field contained only the
number at the end, i.e. not the customerid number, and not the dash. You can
still display it to look like that (e.g. on a report), with a text box that
has a Control Source of:
=[customerid] & "-" & [new_field]

If you are okay with that approach you can assign the number to the
new_field in the BeforeUpdate event procedure of your form:

If IsNull(Me.new_field) And Not IsNull(Me.customerid) Then
Me.new_field = Nz(DMax("new_field", "Table1", _
"customerid = " & Me.customerid), 0) + 1
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

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

1) How can i do that from a Query or from VBcode !!!

2) And how to make a new table to auto index this sample alone


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