I have 2 tables Customer1 and Customer2 both table structures are the same except Customer1 has outdated data, I need to add additional customers and data that is unique to each customer from Customer2 to Customer1, each customer has a customer number which is also the primary key, any help would be greatly appreciated.

How about adding a field to Customer1 called Outdated, and make it a Yes/No check box, default value = No. Then filter your forms and reports to run only where Outdated = No. This way you can keep all your data in one table. This is standard.

Moving data to another table is usually only done to archive the data - move it out of your database. If you are already keeping it in one database, then just use the Outdated switch. Much simpler design (one table), no coding (moving data between tables, adding a customer back to active table), just query parameters (WHERE [Outdated] = No).

Did that help?


mmcdonal

fwebb,

mmcdonal is bang on right here, thats definately the best way to do it.
It never makes sense to have the "same" data in two tables. Always try and keep it in one.

This not only makes filtering easier, but also operations such as archiving etc.
For example, you could produce a very quick update query to mark all cases that are over 6 months old as "Outdated".

Some people prefer to KIS (Keep it Simple), but I also like to KIL (Keep it Logical) =)

Regards,
Rob

Thanks for the responses, I probably didn’t explain my issue well enough, the data in Customer1 contains standard customer info (Customer#, Name, Address, Etc.) the data in Customer2 contains the same customer information and table structure except it has additional customers and the FFL_Info field is populated with data that is not in Customer1, so what I wanted to do was add the additional customers and populate corresponding FFL_Info field in Customer1 from Customer2. I was able to do what I wanted by creating a new table named Customer3, created an append query to suck the data from Customer2 and insert it into Customer3 which it did populate, then renamed Customer1 to something else and renamed Customer3 to Customer1 and it seems to have worked. I know this was a very clumsy way to do this and I am sure there is a more elegant way to accomplish this and would appreciate any help with doing so as I will run into this again. Sorry for the long post.