Combine data from 2 fields into 1

[Guest]

Hello:
Is there a way to combine the data from 2 fields into one. For example, I
want to keep track of company name changes so I have a table of companies and
a table with the history of the company including name changes. I want to
search by old names and current names. Is there a way to combine the company
id and current name from the company table and company id and old name from
the history table into one table to do a search on by either name? The
company id and the name field would be the primary key for the search table
so that the company id can be repeated in the id field and the name field
column would contain old and current names. Not sure if I am going about this
the right way.
Thanks,
J.

 

[Graham Mandeno]

Hi Jade

You can create a UNION query like this:

Select [company id], [company name] from [company table]
UNION
Select [company id], [old name] from [history table]
ORDER BY [company name]

Type this into the SQL view window of a new query - you can't create a union
query in the query design grid.

Then you can look up the query by company name and retrieve the ID.

 

[Guest]

Perfect! Thanks Graham.
J.

Hi Jade

You can create a UNION query like this:

Select [company id], [company name] from [company table]
UNION
Select [company id], [old name] from [history table]
ORDER BY [company name]

Type this into the SQL view window of a new query - you can't create a union
query in the query design grid.

Then you can look up the query by company name and retrieve the ID.
--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hello:
Is there a way to combine the data from 2 fields into one. For example, I
want to keep track of company name changes so I have a table of companies
and
a table with the history of the company including name changes. I want to
search by old names and current names. Is there a way to combine the
company
id and current name from the company table and company id and old name
from
the history table into one table to do a search on by either name? The
company id and the name field would be the primary key for the search
table
so that the company id can be repeated in the id field and the name field
column would contain old and current names. Not sure if I am going about
this
the right way.
Thanks,
J.