Append Query To Table

[Gary]

I have 6 tables which have identical fields and they all have a Primary Key
which is AutoNumber.

I have a final table which has the same fields and also has a Primary Key
which is AutoNumber.

The problem I have is when I use an AppendQuery to append each of the 6
tables to the final table, sometimes the order of the table changes than from
the individual tables. The append query has all of the fields except for
the primary key

When I look at the individual 6 tables, they all look correct, but after the
Do -Loop of the 6 append tables, once in a while the order is different.

Can someone please help me correct this issue!!!


Thank You,

Gary

 

[Stefan Hoffmann]

hi Garry,

The problem I have is when I use an AppendQuery to append each of the 6
tables to the final table, sometimes the order of the table changes than from
the individual tables. The append query has all of the fields except for
the primary key
When I look at the individual 6 tables, they all look correct, but after the
Do -Loop of the 6 append tables, once in a while the order is different.

You cannot expect an order based on an auto-number to be consistent. And
more important: to be meaningful. And last, but not least, how should be
the order if two records from two different tables have the same
auto-number value?

Using a sorted union query should preserve the order, but you have to
test it:

INSERT INTO combinedTable (fieldlist)
SELECT U.* FROM
(
SELECT 1 AS [Order], fieldlist FROM table1
UNION ALL
SELECT 2 AS [Order], fieldlist FROM table2
UNION ALL
SELECT 3 AS [Order], fieldlist FROM table3
...
) U
ORDER BY U.[autonumberField], U.[Order]

or

ORDER BY U.[Order], U.[autonumberField]


mfG
--> stefan <--

 

[Dale Fye]

Relational database tables do not have an "order", unless you define one.
Generally you don't worry about what the sequence is of the data in the
table, you use a query to sort it in whatever order you want.

What order do you want them to be in? Why did you have 6 tables to begin
with?

If you really want to be able to sort the records in this final table based
on their position in the original tables, you could add fields in tbl_final
for original table_sequence, and original_ID. Then, in addition to all of
the other fields you are importing, you could import a numeric value for the
table_sequence, and the ID (autonumber field) from the original table.

Another method would be to use an OrderBy clause in each of your append
queries to sort the records in the 6 original tables before you append them
to the final. It would look something like:

INSERT INTO tbl_final (field2, field3, field4, field5)
SELECT field2, field3, field4, field5
FROM originalTable1
ORDER BY ID

If you do this for each of your original tables then the final "order"
should be what you are looking for.

 

[John Spencer]

Records in a table do not have an order. You impose the order by using a query
with an ORDER BY clause.

One way to handle your problem is to add an additional field to the FINAL
Table (Field: MySpecialOrder - Text).

I would populate that field with a letter from A to F plus a formatted value
of the autonumber field in each of the source tables. My append queries would
look something like the following.

INSERT Into SomeTable(Field1, Field2, Field3, MySpecialOrder)
SELECT FieldA, FieldB, FieldC, Format(AutonumberField,"\A0000000000")
FROM TheFirstTable

INSERT Into SomeTable(Field1, Field2, Field3, MySpecialOrder)
SELECT FieldA, FieldB, FieldC, Format(AutonumberField,"\B0000000000")
FROM TheSecondTable

....

Another way would be to use a union query to combine all the records and not
bother importing them into yet another table. Or Save the Union query and use
it to populate your combined table.

SELECT Field1, Field2, Field3, "A" as TableSource, AutonumberField
FROM TheFirstTable
UNION ALL
SELECT Field1, Field2, Field3, "B" as TableSource, AutonumberField
FROM TheSecondTable
UNION ALL
SELECT Field1, Field2, Field3, "C" as TableSource, AutonumberField
FROM TheThirdTable
ORDER BY TableSource, AutoNumberField


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County