Making a text box that takes data from two different (but similar) tables.

[IncidentalProgrammer]

The database I'm creating at work will be storing data on insurance claims, among other things. There are two different tables for claims, due to the different ways the data will be received. In the near future, another system will be complete that will be able to send me reports to import that will have all the claim data for claims going forward; that will go into and continually overwrite a table I've designed for imported claims (each claim in the new report will overwrite its existing entry upon importing, so as to update). The second table is for older claims that we will have to input manually; since old claims won't be on the new system and included in the update reports, I wanted to keep these separate so that they don't get erased when I import. (I have never imported data into a database before, so am I thinking of this wrong?)

I'm working now on a search form for users to look up all claims from both tables. I'm using the Allen Browne method, so it's a series of text boxes. The problem though is getting each text box to access the values from both tables (the claim number box needs to look for claim numbers in the import table and manual table, for example). I've tried writing an expression, but I'm getting an error. Is this possible?

[June7]

The form RecordSource can be a UNION query.

Example:

SELECT field1, field2, field3, "import" AS Source FROM [import table]
UN ION SELECT field1, field2, field3 "manual" FROM [manual table];

There is no wizard or builder for UNION query, must type into SQL View of query designer.

Data is not editable through UNION query.

[IncidentalProgrammer]

I hadn't heard of Union Queries! I'm going to try that right now. Thanks, June7!