Query Help

[Guest]

I have 8 tables that each contain a field called uEmail. What I need to do
is create a query that will tell me what table does not have a matching email
in the uEmail field in the results table. Is this possible??

Dan

 

[Tom Ellison]

Dear Dan:

Yes, this should be possible.

The general approach would be to create a UNION of the 8 tables with the
column uEmail and another column I usually call Source. This Source column
will contain a unique name for each of the 8 tables from which rows are
drawn.

If you query this UNION it will tell you which Source(s) (tables) had the
desired value of uEmail. If you also create a table of the 8 Source names
you have used, you can query NOT IN against this table and see which of the
8 Sources did not have the desired value.

This method can be extended to be used against a table of uEmail values,
checking all of them against Sources.

I have given you an overview of how this can be done technically. In order
to give specific examples, I'd have to know more about your specifics.

Tom Ellison

 

[Guest]

Research the SQL specific for creating a UNION table. Create a union table
that contains all 8 of your tables. I assume that another field may alsw
uniquely identify the record? If so, then your lookup against this new
UNION table will identify any matched email address.

If you can do VBA, you could use a form text box for the email address and
on exit or on a command button click, search for a match against each table
and return a MSGBOX with success or fail data.

Also, hopefully, helpful...