Re: Combining Recordsets

The first problem I have is you M2M relationships. There really is no such
thing in a relational database. A logical M2M can exist, but to physically
resolve it, you use an intermediate table that sits between the two and is
1toM on both sides. For example, students and classes. One student can
enroll in 0 to many classes. A class may have 0 to many students enrolled.
Therefore, a M2M exsits between students and classes. That is resolved with
another table that needs only two fields, one for the Primary Key of the
student table and one for the Primary key of the class table. So each time a
student is enrolled in a class, a record is created to show the relationship.
Now from the student side, you can see which classes a student is enrolled
in and from the class side, you can see which students a are enrolled it a
class.

What I am getting at is that I suspect your relational design needs some
review.

But, based on your info, I think a Union query combining all 3 tables would
be the way to go. If you open Help in the VBA Editor and in the Table Of
contents, click on Microsoft Jet SQL Reference, Data Manipulation Language,
UNION Operation, perhaps that will give you some ideas.
--
Dave Hargis, Microsoft Access MVP


"David Wetmore" wrote:

On Wed, 16 Apr 2008 12:54:04 -0700, Klatuu <Klatuu@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

If all 3 have the same structure and data types, you could use a Union query
to combine them into one, possibly.

You question is a "how do I" question. It would be better if you describe
what you want to do and perhaps we can assist with how to accomplish it.

A good point.

I have 3 M2M relationships with the same master table. I wish to return records from
the master table which fit a minimal search on 0, 1, or 2 entries from the three M2M relationships.
The three link tables all contain a PKey and CKey field. Using these link tables I have
created three recordsets, one from each link table. Each recordset contains the PKey values selected
from that link table.

I want to combine the parent keys (PKey fields) from the 3 recordsets, eliminate duplicates and use the result in a
query on the master table.

.

Relevant Pages

  • Re: UML: extends .vs.generalize
    ... >>college student application. ... > generalization, but you and your friend seem to be talking about 2 ... > Family Member as a generalisation of student is helpful, ... It's supposed to be "Enroll Family Member". ...
    (comp.object)
  • Re: Update field on form after one field is chosen
    ... Your Enroll table doesn't have the students' FirstName, ... > The project is student registration for some college. ... > as ENG, MIS, ACT and so forth. ... I attempted to mess with SQL statements with no go (well I ...
    (microsoft.public.access.queries)
  • What does Microsoft have against Verizon?
    ... My apology in advance if this is the wrong new group. ... I run Office 2007 (Home & Student). ... I have several e-mail addresses but all are on verizon.net which means that I cannot enroll. ... Could someone explain what a "reserved domain" is and why it's so bad? ...
    (microsoft.public.officeupdate)
  • RE: Add record programmatically with values from two recordsources
    ... Wouldn't the student have to be enrolled in the class to show up as, ... "PJ Von" wrote: ... The second combo box allows the user to select the student to enroll. ... but have no idea how to accomplish ...
    (microsoft.public.access.formscoding)