Best way to sort between tables. . .

[Brambojr]

Hello,

I need some guidance on something . . . I am working on a Db where I find it necessary to often collect information from many tables (all essentially containing the same info) and put them together into one recordset.

Right now I am using a string of append queries, making my report and then deleting it all with a delete query.

Isn't there a better way?? How could I just add all this to a rst? What if I wanted to add an identifier of which table was it's source? And how would this information be passed into a report?

I really would like to do this in code and not leave some huge batch of exposed queries to be tampered with annihilating the process while warnings are off.

Can anyone help me out here? I think it's likely a simple answer, but I am still fairly new to coding.

thanks Brambojr

 

[JimHorton]

Bram,
You could use a Union query. You just need to stack up Select statements from each table on top of one another, separated by the keyword Union. If the tables have different # of fields, then just list the fields that are common among all of them--in the exact order, so they match in the select statements (it doesn't matter what order the fields are in the tables themselves).

You can base a recordset or report off of that query.
--Jim

 

[dawnd3]

The way I know of IS through a query, but it is in SQL. A Union Query will allow you to combine more than one table as long as each feild has the same type of data and the same number of fields (not necessarily named the same) Look up Union queries in the help menu and it should be pretty explainitory.

Dawn

http://www.calliopeconsulting.net

 

[Brambojr]

Wonderful!! will look it up. This is by far the fastest response I've ever recieved!

Thanks, Brambojr

 

[dawnd3]

wow and I think we both responded at the same time

http://www.calliopeconsulting.net