Appending Unique Records Please Help

[Michael786]

I have two tables. 1 table called T_AddressHistory (Historic Data) and the other called T_Customers (Live Editable Table)

I want to append the new changes made to a the T_customers table into the T_AddressHistory.

The fields in question are CustomerID, Add1 and Postcode.

Example 1

CustomerID = 100001
Add1 = 25 Hope Street
Postcode = SE23 1HX

The above data now resides in the T_AddressHistory.

If the add1 and postcode fields were edited in the T_Customer 100001 record then i am hoping that the next time I run the query it will append a new record of

Example 2

CustomerID = 100001
Add1 = 11 Main Barn
Postcode = LL44 1HX

I am currently getting around this by creating a query via joining both tables together via the Customerid. Then under the Add1 and Postcode criteria I am enterting the "not like".. statement. Then to prevent duplicate records being added I have had to add Primary Keys on the CustomerId, Add1 and the Postcode of the T_addressHistory table. I know there must be a more correct manner of doing this query and would really appreciate your help.
Thank you.

[Scott Price]

Well, first of all, why two tables?

Can you not just leave the data in the same table if it's being updated? Or are you trying to track the changes made by customer/address?

There is a rather neat way to check for a particular record using a recordset that might be useful to you if you MUST have the two tables...

It involves passing a parameter (probably customerID in your case) into a public function that opens the recordset, sets which index to use and then seeks for the parameter. You could re-write this to append the records, or call it from an If ---Then statement (as it's currently written it returns a boolean value), or even call it from a query if you could figure out what value you want it to pass to the query.

Let me know first of all if having two tables is required (which violates several db design rules) and second of all if you want to take a look at this recordset function.

Regards,
Scott

[Michael786]

Hi Scott.
Thanks for your reply.
I have made two tables because I want to keep track of the changes a customer has made to their address. I currently run this query on a daily basis and also append the date/time.
With regards to your recordset procedure, its sounds like what I need but is it difficult to program? I am no expert in writing code as Access 97 is new to me, but if you can provide the code for me to make a module or the gudelines to make it in a design view query that would be much appreciated.
Thanks once again and look forward to hearing from you.

Regards.

Michael

[Scott Price]

Hi Scott.
Thanks for your reply.
I have made two tables because I want to keep track of the changes a customer has made to their address. I currently run this query on a daily basis and also append the date/time.
With regards to your recordset procedure, its sounds like what I need but is it difficult to program? I am no expert in writing code as Access 97 is new to me, but if you can provide the code for me to make a module or the gudelines to make it in a design view query that would be much appreciated.
Thanks once again and look forward to hearing from you.

Regards.

Michael

Hmmm... Access 97? I may have to back up on this one. I'm working with Access 2003, and there are a number of features that aren't exactly the same. Is your setup working for you now?

Let me think about this a bit, I'll be away for the weekend, but will be back Sunday evening.

Regards,
Scott

[mlcampeau]

Instead of 2 tables, you could always just have the one, with an extra field named Active with a datatype of Yes/No. Then when there is an address change, you can add the new address to the table and just make the new one active, and the old one inactive.

[FishVal]

Hi, Michael.

You may not store Address info in [T_customers] table at all. Just retrieve latest customer address added to [T_addressHistory] table. Or add to [T_customers] table field [CurrentAddress] which is FK of table [T_addressHistory] PK. With this configuration you may just add multifield index to [T_addressHistory] avoiding duplicates.

[Michael786]

Hi Thank you everyone for your replys. I was unable to answer earlier as I have been experiencing some DSL problems!.

To Scott.

We are currently using a program that was written many years ago with the back end database built in access 97. We can not upgrade this back end database to Access 2002 or 2003 because when we tried it caused all sort of bugs in the front end mde user interface. Even the original developers no longer support it. As I also have access 2003, an idea may be to create a new database in access 2003 and link to the backend tables that are in access 97. I can do all the querying and modules in access 2003 that you want me to do but any changes required will be made to the linked table that is within the Access 97 database. What do you think? Will this work and do you think I can come across any possible errors?

Thanks Scott and look forward to hearing from you.

To Fishval,

Unsure of your statement. We currently already use an access 97 built program, whenever an operator makes changes to an address of a customerID it does not store the previous address details at hand. I was hoping on running a query directly to the backend database on a regular basis into a new table I have created so that I can store customer’s previous address details.

[FishVal]

Hi, Michael.

[T_customers] table and [T_addressHistory] table are in classical 1-many relationship. So what is the reason to store address in [T_Customers].
There are two options of recognizing relevant address:

• the latest added is relevant
• explicitly store relevant address FK in [T_Customers] table

Simple tabble join will give you records with customer info including relevant address.

And again, with this configuration you may just add multifield index to [T_addressHistory] avoiding duplicates.

Does this make a sense?

Regards,
Fish

[Scott Price]

Hi Michael,

I would suggest migrating to a newer version of Access, A2003 is probably the best one at the moment.

As far as the possible errors you will/may encounter in the upgrade, I can't, unfortunately, foresee what all you might run into. However, if you do like you are mentioning and re-build the front-end with A2003 while leaving the back-end either in A97 format, or (better) converting it to A2000 format, you shouldn't run into anything insurmountable.

The suggestion FishVal made is a good one, and should get some serious consideration. It would require separating the address information from your Customers table, and leave it completely in the Address information table. This would require adding a separate field in the Address table to indicate which address is the current one. Here you might take a good look at Michelle's suggestion of putting a field named Active Yes/No data type in the Address table. Then the change to indicate a new address is as simple as adding the new, with Yes in this field, and changing the old to No.

Any other problems you run into during the upgrade process, we'll be glad to take a stab at helping you out!

Regards,
Scott