Combine records from 2 tables in combo box lookup

[AvGuy]

I have two customer tables with identical fields. One is for current customers and one is for outdated customer records mainly old names or addresses that have been superseded by new info in the current customers table. Both tables have CustomerID's that identify the customer like this:

Current Customer Table:
CustomerID 1234
CustomerName New Name
Customer Address 1234 New Street

Old Customer Table:
CustomerID 1234
CustomerName Old Name
Customer Address 1234 Old Street
CustomerID 1234
CustomerName Really Old Name
Customer Address 1234 Really Old Street

With this structure I can easily find the new name/address from an old name/address which is why I set it up this way.

Now I want to combine the listings from both tables in a combo box for a user to select a customer. I want to include the old data along with the new data because the user may not know the customer has changed addresses or names.

So - - any thoughts on constructing a datasource for the combo box? Of course I can create a temporary table, run 2 queries to dump records into the temp table and use a query on that table, but that solution is butt ugly and I'd like to find something a little more elegant.

AvGuy

 

[bosk00]

I think if you can use a union all query for the combo datasource and you should be all set.

The union all will combine the entries from the 2 tables

 

[AvGuy]

Good thinking!

Forgot all about those things.