Combining Recordsets

  • Thread starter Thread starter David Wetmore
  • Start date Start date
D

David Wetmore

I have three recordsets, each containing a series of integers, from 3 different tables.
Can I somehow combine these three recordsets into one, or must I create a table and copy
the three recordsets into it?
 
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.
 
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.
Click to expand...

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.
 
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.
 
Thank you for your reply, and I will check out SQL union commands.
I had thought that my reference to "link tables" would have made it
clear that I had implemented an M2M relationship in the standard way.
I aplogize for not making myself clear.
 
Back
Top