Incrementing With DMAX

[GriffyNJ]

Hello:

So ive read quite a bit of forums that talk about how you can use DMAX to increment instead of using autonumber. Simple Question: where do you put the dmax function in your design view of the table?

I have a desire to create my own autonumber that increments from 1 all the way to whatever for a serious of records. I tried Dmax in a query, because i'm using queries to populate a new table, but it adds 1 to all of the records so I end with 1 (default value being 0 in my table design view)

Thanks for your help in advance.

 

[boblarson]

Simple answer - you don't put it anywhere in the table. And nobody should be adding data in the tables or queries. You put the code on a form and use that for input.

 

[GriffyNJ]

Ok let me explain more of what i'm trying to accomplish.

This database I have is not for users to input data. What I have is a table of data(pre existing). I have a serious of queries that update and switch and replace and alter the data in this table to a specific format to which I need it. I finally have a Update query that gives me the final product that I need so that I can export updated table to another database (SQL). I work for government, so I kno it's probably breaking all the laws that govern good database management, but unfortunately, this what it has to be. The database is management by someone else, so I don't have complete rights to alter the original database with good database management skills. I just get to work with a copy of it. So what I need to know is how can either through my queries or through my design view in the table, have a field that automatically increments for every record that I add via my final Update Query? Autonumber doesn't work well because I need something that starts back at 1 if I erase and start over. So what can I do, and where exactly can I put the code. in a query, in my table? Auto increment without using autonumber, used in the table design, or query, is my foreseeable goal.

Thanks again in advance

 

[lj1602]

Not sure you can achieve what you want within your table, but you could use a combination of a make table query and an append query to create a copy of your final product after the last update. You would then export the copy rather than the final product table.

Create a copy of your final product table with no data (“CopyFinalProduct”) in design mode, but add an extra auto number field (even if you have an existing auto number field in the final product table). Make sure you get the data types exactly matched in “CopyFinalProduct” to avoid any errors later on.

Create a make table query that creates a new table ("NewFinalProduct") based on “CopyFinalProduct”. This will set up a blank table, with the new auto number field set to increment from 1.

Before setting up the append query for the first time, run the make table so that there's an empty "NewFinalProduct" table sat in your database.

Create an append query to copy all of the data in your "FinalProduct" table to "NewFinalProduct". When you run this, all of your data will be copied across, but as you have an extra auto number field in "NewFinalProduct" this will increment from 1.

If you subsequently add more data to "FinalProduct" or you’re done with it and want to start over, delete "NewFinalProduct” and rerun the make table and append queries. Doing so will make sure you always have a field sequentially incremented from 1 to n records in the table that you export.

 

[gemma-the-husky]

to get best control, i would store the last used document number in ANOTHER table. then read this, increment it to get the current number, and save it again.

doing it this way, means you can reseed the starting number as you require.