How do I make one query a superset of another

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two queries, each of which is based on different combinations of
tables. Each allows the entry of new records. The fields returned by each
query are common to both queries, but the records returned are different
(because of the different table combinations). I would like to make one
query a "superset" of the other, such that its results include any of the
records from the other query that are not already in the results of the
first. I still want to be able to add new records to this superset query,
which new records would update the underlying tables of this query just as
they do now.

Alternatively, I could have the "subset" query add records to one of the
underlying tables that determines the results of the "superset" query, such
that the superset query is sure to always include, at a minimum, the results
of the subset query + any new records that are added to its underlying tables
through other means.

I cannot figure out how to do this, but would love any help.

Thanks in advance.
 
Examine Outer Joins in the Help file. I think it's what you're striving
for. Unfortunately, I don't think the result will return an updatable
recordset. You can try setting the inconsistent updates to true, though.

Good luck,

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting
 
Thx for the tip. I actually had tried outer joins, but still could not
figure out a way to get the right results, and you're correct that in some
cases the outer join also seemed to prevent me from being able to make
updates.

I think my problem was that the dataset I was trying to achieve was really
the union of two queries, not a join at all. But, again, unions cannot be
updated by access.

But, I have now solved the problem. Turns out that the idea I suggested in
the latter half of my original post ultimately worked. I created an append
table from one of the queries (the subset) that added records to one of the
underlying tables of the second query (the superset). By using VB to do this
everytime the subset query is updated through a subform and supressing
warnings, I have achieved exactly the result I was seeking, which is that a
second subform always shows at least all the entries from the subset + any
that I have directly entered into the superset. Duplicate records in the
superset are avoided because the table to which I'm appending records has
only two fields, which together are the primary key, prompting the append
query to always throw out duplicates.

Thanks again.

Regards,
Danny
 
Back
Top