Single query which returns all of the data in a single record source

[Hugo1]

I'm trying to use SQL to merge the data from several tables into one temporary table, and then link in Crystal.

Is it possible to create a single MS Access query which returns all of the data in a single record source for Crystal's use.

The output should be something akin to what a UNION ALL query would produce, including the table name as a column called datasource or some such.

I'm not sure how to do so in Access.

 

[PHV]

I guess that any saved query should be considered as a table/view by Crystal ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Golom]

Maybe. Queries of the form

Select * From tblA <Some Join Type> tblB <Some Join Type> tblC ...

Will return records consisting of fields drawn from the tables involved in the joins.

Alternatively

Select * From tblA
UNION ALL
Select * From tblB
etc.

Will merge the records from the tables into a single table (assuming that the tables have the same structure.)

Question is ... What makes sense in your situation?

 

[Hugo1]

Thanks v. much Golom and PHV. I'll try what you've suggested and see how i get on!

 

[Hugo1]

i've used the query:
Select * From tblA
UNION ALL
Select * From tblB
which has worked and merged the date and value fields for all sensor tables into one table.

I'm not sure how to link this table to each sensor table.

Hope that makes sense!

 

[Hugo1]

Are there metadata tables in Access that list the tables?

 

[Golom]

If you did just this

Select * From tblA
UNION ALL
Select * From tblB

then you didn't create a table ... you created a query which looks like a table but it is generated only when the query runs (i.e. it isn't stored anywhere other than as the SQL above.)

If you saved it as a query called (for example) myQuery then you could refer to it with SQL of the form

Select fld1, fld2, fld3, ...

From SomeTable X INNER JOIN [COLOR=blue]myQuery[/color] A
     ON X.fld1 = Q.fld1

Where ....

 

[Hugo1]

thanks Golom,

I'll try this