Solved

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

Posted on 2006-11-29
4
137 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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 article is very specific and is only intended to help if you are installing Dreamweaver 8 in a Windows 7 environment with Office 2007 installed.   I'm not sure why Microsoft tends to release OS' that should not be released but they do.  Windows…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

828 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