Finding Unmatched Records from Denormalized Database

[JoeM]

I am working with an ancient software package that is not very well designed (not even close to being normalized). All the data is held in DBF files.
I need to recreate some reporting that we have using Access. The reporting is a bit complex, so I am trying to make it as efficient as possible, so as not to bog down the process too much.

Here is the current situation.
There is a table that lists a Company ID and then twenty different possible "plan" fields (let's call it Table1).
There is another table that lists all the "plans" that the company actually has (let's call that Table2).
I need to find all the Codes in Table 2 that do not appear in Table 1.

Here is an example of Table1 (header row and one data record):

CO ID	PR1	PR2	PR3	PR4	PR5	PR6	PR7	PR8	PR9	PR10	PT1	PT2	PT3	PT4	PT5	PT6	PT7	PT8	PT9	PT10
ABC123	AB	AD	AZ	DX	DZ	RM					NS	NZ	FB	FD

Here is an example of Table2 (header row and 13 data records):

CODES
AB
AC
AD
AZ
DX
DZ
FB
FC
FD
NS
NZ
RM
RN

So, in my query, I should return three records, AC, FC, and RN.

If the Table1 was normalized, this would be very easy (an Unmatched Query). Now, I could use record sets and VBA and loop through Table1 to create a new temporary table (which is normalized), and use that in an Unmatched Query. But I was wondering (and hoping) if there is a more efficient way to simply use dynamic queries without any need for temporary tables.

Any ideas?

[pbaldy]

My first thought is a UNION query on table 1 to "normalize" it, then the unmatched query comparing that to table 2..

[JoeM]

My first thought is a UNION query on table 1 to "normalize" it, then the unmatched query comparing that to table 2.

Do you mean have a query with 19 UNION statements in it?
Or is there a simpler way?

[pbaldy]

I think one way or the other you want to normalize that data. You can either "use record sets and VBA and loop through Table1 to create a new temporary table (which is normalized)", or create a UNION query that yes, would have a SELECT clause for each of the "PR" fields in the table. Either way it's a bit of work, which is not unexpected when you have to work around a non-normalized design. I think the difference is that the UNION query is more of a one-time setup. If you create a temporary table, you would need to rerun the process as the data changes. The UNION query is a dynamic re-representation of the data. In other words, as the data in table 1 changes, the UNION query automatically reflects the changes (presuming no new fields of course).

[JoeM]

Thanks Paul. Just wanted to make sure I wasn't overlooking a simpler option. I couldn't think of one myself, but sometimes I "can't see the forest for the trees".

[pbaldy]

You may be overlooking one, but if so we both are. There are a lot of trees around here though.