table questions

[RCI]

I have two separate tables within the same database that
both have a check box to indicate if the record is to be
added to the mailing list. I want to somehow (a query I
am assuming) create the mailing list by pulling every
record from each table with the box checked, but am having
trouble. Any suggestions?

 

[Allen Browne]

Try a UNION query.

Access cannot generate these graphically for you, but you can generate the 2
queries to give the same output fields, and then switch them to SQL View
(View menu). Replace the semicolon at the end of one with the word UNION,
and then paste in the SQL from the other. The result will look something
like this:

SELECT ClientID, SomeField FROM tblClient WHERE MyYesNo = True
UNION
SELECT Field1, Field2 FROM AnotherTable WHERE IsActive = True;

For the longer term, it may be worth considering if you could use one table
instead of the two, and add an extra field to distinguish between the
records.

 

[Van T. Dinh]

Check Access Help on the UNION Query.

 

[Lynn Trapp]

Mark,
What you suggested won't work, because there is no join between TableA and
TableB. Instead, it will produce what is called a Cartesian Product. If
there are 5 records in TableA with the checkbox marked and 3 in TableB with
the checkbox marked, the result of the query will be 15 records, not 8. He
should use the Union query suggested by Alan and Van

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm