Suffix to data

[Brutal]

Hello

I have an address database and each record starts with a customer code, the problem is that more than one address can exist for only one code. I need to add some data to the end of each customer code to identify the address ie

aaa001, john doe, 1 street.
aaa001, john doe, 99 road.

to

aaa001.00, john doe, 1 street.
aaa001.01, john doe, 99 road.

Thats the kinda thing, but I don't know how to count how many codes are the same then add the correct suffix 00, 01, 02 etc.
Any ideas would be greatfully received.

Thanks

Brutal

 

[The_Doc_Man]

First, I would not make a "true" suffix. Instead, I would take your customer code, add a field to it that is the qualifier you want, and make the combined fields the primary key. Synthesizing a key makes your task harder than it needs to be.

Now, the way to identify duplications?

When doing the append of the record, make the value of the qualifier field be defined like this

[Qualifier] = DCount( "[Qualifier]", "MyTable", "[CustCode] = """ & [CustCode] & """"

Lots of quotes there, count them carefully. You can use DCount in an Append query without resorting to VBA, so it should be OK. Be sure to read up on DCount before you actually use it. The DCount function should be in the help files under that name.

First time you use code AAA001, the count will be 0.
Next time, it will be 1. Then 2, etc.

 

[Pat Hartman]

Multiple addresses are stored by many systems. The usual technique is to include an AddressTypeCode. So as The_Doc_Man said, use two columns as the primary key. But I would make the second one type rather than a sequence number. An AddressTypeCode could be Home, Work, Mailling, Billing, Shipping, etc.

 

[Brutal]

Que?

Hi
Thanks for the quick responses.
I am getting some results from what you have said to me but my understanding is far to low level for me to get very far.

1. For the purposes of this exercise I really need to have it in one field as I am going to export the data into another piece of software that will only accept 1 field.

2. What is a qualifier? I assumed it to be a column in the table I named suffix.

3. Do I enter the function into the field area of the append query, and append to the suffix field?

4. Why is [qualifier] at the begining of the function and what does """ & [custcode] & """") mean at the end of the function.

5. When this works could I join the code field and suffix field together for my export.

Thankyou very much for your help

 

[Pat Hartman]

1. No, you don't need to store the suffix in the same field as the customer code. You can concatinate the two fields on export by exporting a query rather than the table.
Select fld1, fld2, CustomerId & AddrSuffix, as CustAddr, fld3, etc.
From YourTable;
2. "Qualifier" in this case is the highest numeric suffix presently assigned.
3. The function goes in the BeforeUpdate event of the form used to add addresses.
4. """ & [custcode] & """") - is being used to concatenate the actual customer code so that the DCount() function can find the appropriate record. However, for this purpose, DMax() is a better choice. DMax() will give you the highest value for the field named "Qualifier". The DCount() function will count the number of values and will fail to return a usable value if deletes are allowed. For example, if you add suffixes 01, 02, 03 and then delete 02, Dcount will return 2 and when you add 1 to that value, you will get 3. However a record with that suffix already exists so the insert will fail. Using DMax() the answer would be 3 which is the highest of the two values 01 and 03. Adding 1 to that will give you 4 which should be the next available suffix.
5. Yes, see the example in question 1.