?
Solved

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

Posted on 2006-11-29
4
Medium Priority
?
140 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 1000 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 1000 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

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

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…
Adobe Dreamweaver CS5 is a WYSIWYG web page editor that has advanced HTML, CSS, and Javascript rendering functionality and is probably the most well-known HTML editor available. Much of Dreamweaver's appeal centers around the Design View interfac…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

762 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