Inserting Autonumber field in Make-tabel query

[118600]

Hi Programmers,

I have tried to read FAQs but i could not find my question's answer.

I want to create one more table out of an existing table by using Make-table query. in this new table(in the query) i want to add Autonumber field as well.
Benefit: Whenever there will be a record added in the old table there will be a changed in new table as well with new generated autonumber.


Anyone can help me regarding this problem ?

Thanks in Advance.

 

[ZmrAbdulla]

What is the purpose of keeping the same record in two tables?

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[118600]

Thanks Zameer,

Its purpose is to keep the original data table in a side and make changes in a new table.

Thats why in this new table autonumber will be an important option.

 

[pbrodsky]

If it's a one-time need, just make a new copy of the table as the old version and keep using the original.

 

[118600]

Actually its a question my boss have asked me, i have told him to use count( ) function or set the field as autonumber. but he is interested in a query to get autonumber in Make-Table query. So that updating a table by running this query will not effect the Data in the new table.

Any chance to get this functionality ?

 

[traingamer]

If you have an autonumber key in your first table, just make the second table key a Long Integer. That way you can copy records without worrying about synching the autonumber fields. (IF your first table does not have an autonumber key, ignore this message. )

traingamer

 

[118600]

Thanks Traingamer,
But the first table does not have an autonumber key

 

[ZmrAbdulla]

Then make a new table with autonumber other fields and use an append query to append the records. You have to distinguish the newest records with some criteria.

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me

 

[willir]

118600

As suggested by Zameer, just add the Autonumber field to the "new" table, and make it the primary key. When you add the records to the new table using an Append Query, the AutoNumber will generate a new number.

...However, I am puzzled by this request.

Benefit: Whenever there will be a record added in the old table there will be a changed in new table as well with new generated autonumber.

Perhaps this is symantics, but the autonumber will ONLY be generated in the new table. And how will you link the old table to the new table? And how will you maintain your links with the new table to any of the related tables the old table is linked / related to?

Note that a common issue is that those new to Access want the Autonumber to always be sequential with no missed records. There are several issues here...
- The purpose of autonumber to provide a simple way on uniquely identifying each record. The sequential nature is only a cosmetic thing -- the autonumber should not have any meaning for the actual record. If the identifying "number" is to have meaning, then you should use another mechanism

- Autonumbers are generated when you start the OnInsert event. It therefore has two problems -- the user can cancel out of the process of entering a number, and if two people are entering a record at the same time, there is a chance that both users will grab the same number.

- What happens when you delete a record or purge a lot of records? The DCount will fail, and trying to maintain the sequential numbers with autonumber will also experience problems.

You will probably have more questions.
Richard

 

[ZmrAbdulla]

There is only a way in my mind with a criteria of date record added. Then in the new append query you can specify the criteria like MasterTable.DateAdded > Dmax("AppendingTable","DateAdded") or something like that.

Zameer Abdulla
[sub]Jack of Visual Basic Programming, Master in Dining & Sleeping[/sub]
Visit Me