Adding auto-numbered field screws up Table order

[Carl]

Each of my two tables has the same data except the last
five fields which are different. I'm simply trying to
combine the tables for a Report by joining the two tables
in a Query.

Unfortunately, the order of the tables is different, and I
need to use an auto-numbered field/Primary key because
there are duplicates of similar data, but Access re-sorts
the data in an uncomprehensible way when adding an
autonumbered field. The use of this auto-numbered field
as a Primary key or not, seems to make no difference, but
I'm not sure.

More specifically, I have a Customer Code (CC) field and a
policy number (PN) field that are common to both Tables.
In some cases there are multiple instances of the same CC
and PN, so there are not unique records except the un-
common data in the two tables. The data was exported from
client software and multiple customer records are
generated whenever more than one policy number is
present. The good news is that both tables have the same
number of records and can be matched up by sorting and
saving the tables. This seems to provide good enough
accuracy for my purposes since the exporting seems to keep
the PN record order the same in both Tables when a similar
CC is found, even though some CC's are in a different
order in the Tables. Since I need to use something like a
autonumbered field to provide unique records and have it
report correctly, incorrect sort order messes up the
data. If I import and have Access assign a primary key,
wrong data shows up since the sort order is wrong. If I
sort the Tables so they match, save them, then add a
autonumbered field, Access somehow automatically resorts
the Table before adding the auto-numbered field, even
though it is quite close. Help!

Example:
Table Order CC PN Unique Table Data
Table A SmithJ 100 $123.00
Table A SmithJ 100 $456.00
Table B SmithJ 100 Lowest Dollar Amt
Table B SmithJ 100 Highest Dollar Amt

Adding auto-numbered field might automatically switch the
last two fields, so that Query results show:
SmithJ 100 $123.00 Highest Dollar Amt
SmithJ 100 $456.00 Lowest Dollar Amt

 

[Steve Schapel]

Carl,

Assuming I understand you correctly, I think you will achieve your
purpose by making a new blank table, with fields corresponding to the
fields in the imported data, plus your AutoNumber field. Then, make a
query, as you have already done, based on the imported table, and sorted
in the required way, and then make it into an Append Query and copy the
data into the blank table. The autonumber values should then reflect
the order you want.

 

[Carl]

I'll try that, but before I do ... it seems like a lot of
extra steps particularly if I have to do this on a semi-
annual basis or explain it to someone else. Also I don't
yet know how to work with an Append Query.

You would think Access would not mess with the order when
adding an autonumber field. The data is fine and in the
correct order when exported ... it's just Access that
seems to mess with that order when adding the
autonumbering, and I can manually resort and save in
Access to get the correct order. It may be that your
suggestion is the easiest way and I'll try it, but do you
know of other database programs that are more
straightforward?

It seems that everytime I try to do simple or common
things, like finding/isolating/deleting duplicates when
merging databases, Access has a multi-step/non-user
friendly way to accomplish it. Another example: If you
set criteria for different fields in the same Query, the
Query lines are duplicated and editing the query becomes
cumbersome. I don't mean this to be a complaint board and
certainly not to you, but I suspect you know something
about different database products.

Sorry for the lengthy note ... Carl

 

[Steve Schapel]

Carl,

Append Queries are very simple, and part of the standard methodology
toolkit for manipulating data. You are trying to do a very unusual
thing, so to me it is not surprising that there is no 1-step process to
complete it. Access normally assumes a unique identifier in any set of
data, which you do not have in your imported data, which to me reflects
a fault in the imported data and not a fault with Access. Nevertheless,
it is worth noting that data in a table has no "order", so a database
program can't "mess with" the order. Put a bunch of apples in a bag,
and then tip them out again, you might be lucky and have them drop in
the order you put them in, but you wouldn't guarantee it.

As regards your comment about "query lines are duplicated", to be honest
I am not sure what you are referring to, but if you would care to post
back with some more details and examples, I would be happy to respond.
In my opinion, Access's query designer is one of the great features.

I know of no desktop database application tool with the equivalent power
of Access, which is more straightforward. Access is certainly a long
way from perfect, but as far as I know, it is the best by a long way.

 

[John Vinson]

Unfortunately, the order of the tables is different, and I
need to use an auto-numbered field/Primary key because
there are duplicates of similar data, but Access re-sorts
the data in an uncomprehensible way when adding an
autonumbered field.

Part of the problem (and I'm treading on thin ice here, disagreeing
with Steve!) is that you're assuming that a table HAS an order. It
doesn't. It's an unordered "Bag" of data! Tables will often be
presented for view in sequential order of the table's Primary Key, but
if you use a Query joining this table to some other table, or use the
table as the recordsource of a Report, or a variety of other uses,
you'll see the records in whatever sequence the query optimizer finds
most efficient.

If you want to sort records in a particular order you must - no
option! - use a Query rather than a table, and the Query must have an
Order By clause sorting the records by specified fields in the table;
or you must set the Order By property of a Form, or the Sorting and
Grouping property of a Report to control the sequence. You *cannot*
count on a table having ANY predictable order.

 

[Steve Schapel]

John,

If you read my responses carefully, I think you will find that, as is
our wont, we are in perfect agreement on all points. ;-)