how to include an Autonumber field in a make query

[Guest]

I am writing a report in Access XP which requires sales results to be
compared year on year. Each year table is sorted high to low and needs a
sequential number always starting from 1. The final report will compare
performance year on year. For example customer 1 current year may be cusomer
5 last year. Prior year infomation will remain fixed and using an append
query into a newly defined table does not present a problem. Current year
information can change each month. Use of the append query into a previously
cleared table does not work as the cleared table 'remembers' the last number
assigned and carries on when new data is appended. Is it possible to include
the autonumber function via a make table query?

 

[Joseph Meehan]

I am writing a report in Access XP which requires sales results to be
compared year on year. Each year table is sorted high to low and needs a
sequential number always starting from 1. The final report will compare
performance year on year. For example customer 1 current year may be
cusomer
5 last year. Prior year infomation will remain fixed and using an append
query into a newly defined table does not present a problem. Current year
information can change each month. Use of the append query into a
previously
cleared table does not work as the cleared table 'remembers' the last
number
assigned and carries on when new data is appended. Is it possible to
include
the autonumber function via a make table query?

I suggest you may not want to use Autonumber for that use. Autonumbers
are designed to provide unique numbers. It in not designed to provide
numbers in order and for a number of reasons may not do so. As a result
using them in any application where the user sees the numbers is likely to
end up with confusion.

There are other ways of providing the numbers you want depending on the
particual application.

 

[Steve Schapel]

Walter,

I am not 100% clear on what you are doing. But using an Autonumber
field in these sorts of cases is generally a fine way to provide
numerical sequencing. As regards "the cleared table 'remembers' the
last number assigned", this will not be the case if the database is
compacted between the time it is cleared, and the next append. I don't
know of a way to include an Autonumber field in a Make-Table Query, but
another approach may be to use a CREATE TABLE query to make a new table
with the fields you require, and then an Append Query to fire your data
into it.

 

[david epsom dot com dot au]

assigned and carries on when new data is appended. Is it possible to

the autonumber function via a make table query?

Yes, an ordinary make table query from the query wizard does that:

SELECT Table1.* INTO Table2 FROM Table1;

(david)

 

[Steve Schapel]

David,

My understanding is that Walter wants an Autonumber field in Table2 that
is not in Table1.

 

[Guest]

Steve your idea of compacting the database after clearing the table works
perfectly. I have now made the various tables for different years and append
the data via append queries. When I want to update I run a delete table
query, then compact the database and finally append the the new data. Next
trick is to include the command "compact database" via a macro. Any
suggestions. Thanks for the help so far. Walter

 

[Steve Schapel]

Walter,

You can't compact a database using a macro. If you have Access 2000 or
later, there is a 'Compact on Close' option under the
Tools|Options|General menu. If you are using a split database, and your
table is in a backend file, you can use a VBA procedure within the
frontend to compact the backend database. Another approach to these
types of temporary "holding" data manipulation tables is to use code to
create a temporary database for the purpose. There is some information
about this at http://www.granite.ab.ca/access/temptables.htm

 

[Guest]

Hi Steve, I have managed through the various responses to use the following
pocedure which works perfectly via a macro: I set up a blank table including
the autonumber field. Then using a make table query wich uses the blank table
as a source I create the table I wish to use for the rest of the process. I
then append the required data which has been extracted from the database via
an append query. Next time around, when the data has changed, I simply run a
macro which sets warnings to 'no', and then runs the 'make table query' ,
which effectively deletes and then re-establishes a new table. This is then
followed by running the 'append query' which inputs the new data. The
Autonumber then always starts from 1.
Thank you all for helping me to get to this stage

 

[Steve Schapel]

Congratulations, Walter, this sounds like a good solution.