Solved

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

Posted on 2006-11-29
4
139 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

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 …
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…

717 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