use of autonumber

[Jesse]

Using Access 2000, I am trying to find a good way to sort a secondary
column in a table before an autonumber is assigned for each record.

We have a db into which we regularly import two data files -- a
transaction file and a details file, related via a trans_id.

These two files are already sorted (ascending) by trans_id and the
details file is _currently_ imported/appended into a table with an
extra column that is set to autonumber.

The details file has a group_id column. After each import, we would
like to have that column sorted (ascending, secondary to trans_id)
_before_ the autonumbers are generated for the newly imported rows.

Among the solutions I've considered, my current choice:

1. Create tbl_details_1 with all of the columns that are in the
details file (but without an autonumber column).

2. Create tbl_details_procsd also with all of the columns that are in
the details file + an autonumber column at the end. Create a unique
index in this table that combines trans_id and group_id (to block
duplicate records from being added).

3. Import the details file into tbl_details_1.

4. Create a "find unmatched" query -- q_sort_appnd -- for
tbl_details_1 and tbl_details_procsd that sorts on trans_id and
group_id and that appends the resulting records to tbl_details_procsd
-- where autonumbers are then generated for the appended records.

Assuming this would work, is there a more straightforward way to do
it?

Noting that I am a newbie at direct coding, modules, and such... is
there a straightforward way to get q_sort_appnd to run automatically
after every import into tbl_details_1?

(Because the details file has a different name every time (it does
have the same last 6 characters, though: "-2.txt") we have been using
the manual process for selecting and importing the file every time.
We'll solve that problem when we figure out how to write code that
says, "Look in a specified folder, select the most recent file that
ends in '-2.txt', and then import that using this import spec.)

Thanks. -- Jesse




5. , and then append it

In the details file there is a group_id column, and any single
trans_id there can be multiple group_id values. Because of the way
that the details file is generated, the group_id records for a given
trans_id can be in


some , There is one column in the details file that we would like to
have sorted before the autonumber is assigned

 

[Roger Carlson]

Sort your Append query in whatever order you want it. It will append the
data in that order and your autonumbers will be assigned in that order.

As an aside, however, if you CARE what order your autonumbers are created
in, you are using them incorrectly.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Jesse]

First, thanks. As for your aside, I figured that might be the case.
Beyond the kindness of you MVPs - taking the time to help us - you do
almost always (gently) point out such mis-uses. I used to find that
peculiar (as in "not helpful") as I read answers to others' posts and
mine. OTOH, at other newsgroups/forums I've read, if you ask the
wrong question you don't get any help at all - just fault-finding.
And, I am slowly coming around to the "db way".

BTW, as you may have suspected, I asked this question because some
data _will_ be used "flattened" along the way. - Jesse

 

[Roger Carlson]

Well, I can't speak for all the other MVPs (or indeed other regulars here
who post answers and are not MVPs), but I think I would be remiss if I
didn't try to promote good database practices. Many of the problems
presented here could have been avoided by good database design principles.
On the other hand, it doesn't do someone much good to simply point out
faults and without helping them with their immediate problem. So I try to
do both if possible. Of course, there *are* times when the problem *is* the
database design, and I won't help them to make things even worse.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L