Sort Order - New record

[Jennifer]

Every month I upload new billing excel spreadsheets into Access in order to
manipulate it a bit. Basically I create a database that includes the month,
customer, and different values for different cost centers. Then I do a sort
of true up to where I sum all of the months I have in the database and use it
in Excel to calculate what I will bill for the next month.

^^^I do this by deleting the temp table, uploading the new spreadsheet to
the temp table, change the format of the spreadsheet to a simpler structure,
append that to a master table (one will all months) and then do queries that
eventually calculate the sum.

Anyway, I used a sort order (A simple table with customers and an
autonumber) in order to make sure the customers were in the correct order.
However, if I upload a new spreadsheet that has a new customer listed the
sort order doesn't work.

This is what happens:

Old sort order
1 Customer 1
2 Customer 2
3 Customer 3

New sort order
1 Customer 1
2 Customer 2
2 Customer NEW
3 Customer 2
3 Customer 3
4 Customer 3

And all values (in the final table with only sums) after the new customer
are empty.

Any ideas on how to fix this?

 

[KARL DEWEY]

Omit sorting on the autonumber and just sort on customer OR sort on
customer then autonumber.

 

[Jennifer]

I can't sort by the customer because its not alphabetical. My problem
doesn't have to do with the customer list being in the wrong order. Its that
when I upload a spreadsheet with a new and different customer in it (one that
wasn't in the previous months uploaded) it gives the same autonumber that is
already assigned to a customer from a previous month therefore messing up the
display of the appropriate value.

 

[KARL DEWEY]

I do this by deleting the temp table, uploading the new spreadsheet to the
temp table,
Ok, then do not delete the temp table but just delete the records and that
way the autonumber will start where it left off.