Solved

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

Posted on 2006-11-29
4
135 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
CSS HTML Text Alignment 13 416
Coldfusion connection with xampp2 5 219
Website Design 13 107
Adobe Acrobat Pro top display text upside down? 5 82
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…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

744 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now