Query Help please

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Could someone tell me how I would write a query to merge three tables
into one, selecting alternating records from each table. The data in the
tables is all of the same type.

In other words.

TableA TableB TableC
row a1 b1 c1
a2 b2 c2
. . .
. . .

Resulting table

TableResults
a1
b1
c1
a2
b2
c2
 
Could someone tell me how I would write a query to merge three tables
into one, selecting alternating records from each table. The data in the
tables is all of the same type.
Click to expand...

Since tables have no order, what you request is not possible in a
Query. What if the three tables had different numbers of records?
What's "alternating" when you run out?

If you don't care about the order, an append query based on a UNION
query would do the trick.

John W. Vinson[MVP]
 
John said:
Since tables have no order, what you request is not possible in a
Query. What if the three tables had different numbers of records?
What's "alternating" when you run out?

If you don't care about the order, an append query based on a UNION
query would do the trick.

John W. Vinson[MVP]
Click to expand...
Unfortunately the order is the important part. The tables do not have
the same number of records, though I hope there was a way to end the
select when one of the three ran out of records. I'm am unclear about
what you mean when you say the 'table have no order' do you mean index?

Paul
 
Records are stored like things in a bucket, i.e. no inherent order for for
efficiency, Records are retrieved the way the database engine consider the
quickest / most efficient and not necessarily the same way the Records are
saved. If you need to present the Records retrieved in a certain order, you
must specify the order in the ORDER BY clause of your Query.
 
Unfortunately the order is the important part. The tables do not have
the same number of records, though I hope there was a way to end the
select when one of the three ran out of records. I'm am unclear about
what you mean when you say the 'table have no order' do you mean index?
Click to expand...

A Table is - logically speaking - an unordered "heap" of records. This
isn't an Access limitation; it's a fundamental principle of relational
database design. You can use an Index to make querying the table with
an Order By clause faster and more efficient; and as a practical
matter the table will be *presented* to view in Primary Key order -
but you have absolutely no control over the order in which records are
stored on disk; if you create a query joining the three tables,
without an Order By clause, then you'll get the records in whatever
order the query optimizer finds most convenient. It might be in the
order you see them in the table datasheet, but it probably won't!

What is the actual structure of these tables? What is the Primary Key
of each table? What field (WITHIN THE TABLE) defines the desired
sequential order? Or, if the data comes from outside Access, whence?
Is there any defined order in this source?

I think what you'll need is some VBA code opening three recordsets to
read the three tables, and a fourth to write data out - but even these
will need *something* to control the order. A Table recordset will
(probably) be returned in Primary Key order but if you have no Primary
Key you may be out of luck!

John W. Vinson[MVP]
 
Back
Top