elliottbenzle
asked on
Can I create a recordset with entries from two different tables?
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
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
ASKER
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.