Solved

Can I create a recordset with entries from two different tables?

Posted on 2006-11-29
4
136 Views
Last Modified: 2012-05-05
I want to create a recordset which contains entries from two seperate tables one called "funthings" and one called "entertainment"
In Both tables there is a field named "best" and if this field is true then I want the record to appear in the record set. Is there a way to do this in the dreamweaver recordset or if not is there a way to create the query in Access?

I want to combine these queries:

SELECT *
FROM funthings                                          
WHERE best = MMColParam

SELECT *
FROM entertainment
WHERE best = MMColParam
0
Comment
Question by:elliottbenzle
  • 2
4 Comments
 
LVL 25

Expert Comment

by:Rouchie
ID: 18044047
Presuming both tables contain exactly the same columns, you can do it like this:

SELECT *
FROM funthings                                          
WHERE best = MMColParam
UNION
SELECT *
FROM entertainment
WHERE best = MMColParam

But if the columns are different you need a temporary table variable (not sure if access supports this), which pulls the correct data in first.
0
 
LVL 4

Author Comment

by:elliottbenzle
ID: 18046972
I already tried to use the UNION command but the tables are not the same. Is there a way to build a query which cycles through both tables, then selects all the records where 'best = true' and builds a new table which assigns a new ID (key auto number) for each entry? I got around this by using two seperate recordsets, one for each table, but ideally I would like all entries to be in the same recordset so that I can Order them by the date instead of having to use two repeat regions which on the page displays as the date reverting back to the most recent date at the beginning of the second recordset and looks disorganized. If there is no easy way to make this combo recordset then I can just use the two recordsets that I currently have. If you have any suggestions let me know.
0
 
LVL 25

Accepted Solution

by:
Rouchie earned 250 total points
ID: 18047541
When you join 2 recordsets into one, the data needs to be the same type in each column for both tables.  If it isn't in your example, then you can do one of the following:

 1. Make a table variable that uses only the COMMON column data types for each recordset.  Insert the data from both tables into the table variable then select from this variable to give you 1 final recordset.  I don't know if Access supports the table variable functionality.  You might have to check the help file.

 2. Use 2 completely seperate recordsets as you propose - much easier!
0
 
LVL 70

Assisted Solution

by:Jason C. Levine
Jason C. Levine earned 250 total points
ID: 18055604
Hi elliott,

During the planning phase, you should be asking yourself if you will need to link tables together and if so, you need to assign the primary key from Table A to the matching record or records in Table B.  That one step will save you tons of headaches in the future.

In regards to looping through and selecting all records where best=true, you can't really do that.  There is a way to link the tables together with a SQL that looks like:

SELECT tableA.*, tableB.* WHERE tableA.best = tableB.best

But the problem there is that every record in tableA will join to every record in tableB that matches.  Instead of a one-to-one match, you are defining a many-to-many.

Rouchie is correct that in this situation, you should just stick to two different recordsets, unless there is some variable that can link the two tables together.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
website template 12 333
Dreamweaver CC + PhoneGap on Windows PC for IOS 1 240
Site contents shift off page in IE 32 129
Adobe Acrobat Pro: Is this a known bug? 9 105
For those who don't know, Adobe Dreamweaver is a popular commercial web editor that enables you to design, build and manage complex websites. The editor is a WYSIWYG (What You See Is What You Get) web editor, which means that you can create your web…
I still run into .cgi files every now and then. In some instances, I actually prefer the simplicity of a .cgi script to other options. Since I use DreamWeaver extensively, what I needed was a way to open .cgi scripts in Dreamweaver. And I wanted to …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question