Inserting New Unique Sequence Numbers Without AutoSeq

[Maxximvs]

G'day

Having a day where the brain noodle will not comprehend so I though I'd post the question.

I have a table (called Vendors) which has two fields (Sequence, a unique number but not marked as a Primary Key, though it basically is one, and VendorName, the name of the Vendor) and once a month I get a list from the shipping dept of new vendors. This list comes in Excel, so I import the sheet into Access and run an Insert Query to place the new Vendors into the Vendors Table (See below)

Sequence - VendorName
16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
- LALA Company
- BABA Company

The issue here is the new LALA and BABA Company Inserted has no sequence number, and must now obtain the next number (25 and 26). It can be done after the insert if it makes it easier or doen while doing the insert - but my head just isn't getting it today.

This I know isn't tough, so no pointing and giggling at me please but any help would be great :>

Thanks in advance

[Stwange]

G'day

Having a day where the brain noodle will not comprehend so I though I'd post the question.

I have a table (called Vendors) which has two fields (Sequence, a unique number but not marked as a Primary Key, though it basically is one, and VendorName, the name of the Vendor) and once a month I get a list from the shipping dept of new vendors. This list comes in Excel, so I import the sheet into Access and run an Insert Query to place the new Vendors into the Vendors Table (See below)

Sequence - VendorName
16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
- LALA Company
- BABA Company

The issue here is the new LALA and BABA Company Inserted has no sequence number, and must now obtain the next number (25 and 26). It can be done after the insert if it makes it easier or doen while doing the insert - but my head just isn't getting it today.

This I know isn't tough, so no pointing and giggling at me please but any help would be great :>

Thanks in advance

I'm not going to bother writing the whole update thing, because you might have it set out completely different, but:
DMax("Sequence","Vendors") + 1 should do the trick.

[Maxximvs]

Ah, that's what I thought, and when I place that in my Append Query (which is inserting my new records into the Vendors table, just created an expression to insert the DMax into the Sequence Table) I get:

16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
25 - LALA Company
25 - BABA Company
25 - etc...

The recordset doesn't refresh on each append, so DMAX doesn't see 25 on the next entry and then +1 for 26 (and so on...), so my new 10 records all get 25 as their Sequence number (24+1)...

I know, very painful... :>

[missinglinq]

I have absolutley no experience in importing data in this matter, hopefully someone else here will have, but the hack provided by stwange, which is pretty basic for this sort of thing, will only work, I think, if the new records are written to the table one at a time, with each one saved. Otherwise, DMax() is going to continue going to the last saved record, and only the first record of the group append will have a correct Sequence Number.

Linq ;0)>

[Stwange]

I have absolutley no experience in importing data in this matter, hopefully someone else here will have, but the hack provided by stwange, which is pretty basic for this sort of thing, will only work, I think, if the new records are written to the table one at a time, with each one saved. Otherwise, DMax() is going to continue going to the last saved record, and only the first record of the group append will have a correct Sequence Number.

Linq ;0)>

It might be better to either use MAX inside an SQL query (or even count?), and keep running this query, or use the DMax and store this in a variable, then increment it. I'm not sure what you mean by not saving records though - does this only happen periodically?

[Maxximvs]

It might be better to either use MAX inside an SQL query (or even count?), and keep running this query, or use the DMax and store this in a variable, then increment it. I'm not sure what you mean by not saving records though - does this only happen periodically?

No, Max or DMax will not work because of how an Append Query works. You have 4 records and you are about to append 4 more. When the 4 are added, it does not add the first record, then saves the record and refreshes the query, then add the second record, and so on. The query runs to find 4 records to add, and inserts them all at once then refreshes with a save. DMax returns with 25 and because it is now ready to insert all 4 records at once, it puts 25 in all 4 records then inserts them.

The other bummer part is you cannot switch to SQL view and then write a neat little statement to Insert the records one at a time because Access Queries can only process commands, not Declarations or Variables (or anything outside of SQL commands) - which means now you probably have to go to a VB Module to open the table, retrieve the records and insert them one at a time into the other table with a Looping Number for the sequence issue, but I haven't found any good code for that.

Thanks for the ideas though, I at least know I haven't missed anything so far to try :>

[missinglinq]

Maybe loop thru the records and run the append query once for each record? Would depend, I'd think, on the number of records we're talking about.

Linq ;0)>

[FishVal]

Why not simply set [Sequence] field type to Autonumber?

[missinglinq]

To put it simply, as an old Access hand once said, "Autonumbers aren't fit for human consumption!" There are too many things that Access does to them to make them reliable for anything except Access' own behind the scenes activities. Gaps in them are caused by a variety of actions. They can be reset by some activities. They really never should be used for anything that a user will interact with. While we haven't been told their purpose here, I think it's safe to assume that they have some vital purpose, since they're one of only two fields in the table.

Linq ;0)>

[FishVal]

To put it simply, as an old Access hand once said, "Autonumbers aren't fit for human consumption!" There are too many things that Access does to them to make them reliable for anything except Access' own behind the scenes activities. Gaps in them are caused by a variety of actions. They can be reset by some activities. They really never should be used for anything that a user will interact with. While we haven't been told their purpose here, I think it's safe to assume that they have some vital purpose, since they're one of only two fields in the table.

Linq ;0)>

Hi, Linq.

All problems related to Autonumber fields will occur with programatically autonumbered fields as well. And actually even more.
So if you are not able to prevent raping, just relax and try to enjoy it. LOL

[missinglinq]

All problems related to Autonumber fields will occur with programatically autonumbered fields as well. And actually even more.

Not true! Autonumbers cannot be changed or modified, in any way, by the user or VBA code. If you have a record that has an autonumber field and you start, for instance, to enter a new record, then change your mind and dump it, Access has already assigned that record an autonumber, and that autonumber is now gone, never to be seen again! If you're using a standard hack like Stwange suggested, that number doesn't get assigned until the record is actually saved, and so dumping the record doesn't create a gap in the numbers. It is true that "rolling your own" incremental numbers have to handled with care, in a multi-user environment, but it can be done.

Linq ;0)>

[FishVal]

Not true! Autonumbers cannot be changed or modified, in any way, by the user or VBA code. If you have a record that has an autonumber field and you start, for instance, to enter a new record, then change your mind and dump it, Access has already assigned that record an autonumber, and that autonumber is now gone, never to be seen again! If you're using a standard hack like Stwange suggested, that number doesn't get assigned until the record is actually saved, and so dumping the record doesn't create a gap in the numbers. It is true that "rolling your own" incremental numbers have to handled with care, in a multi-user environment, but it can be done.

Linq ;0)>

Ok. Sorry. Your reasons are quite acceptable in a case when manually autonumbered field is not PK. In a case when it is PK (BTW I use Autonumber for this purpose only and vice versa) manually autonumbering should be avoided. "Gaps" ? Big deal, referrential integrity is more important. Actually "gaps" are the price one should pay to avoid reassigning of FKs to a PK generated after old PK was deleted.
If somebody want to enumerate records it easily could be done dynamically with a query.

Regards,

Fish

[hyperpau]

Ok. Sorry. Your reasons are quite acceptable in a case when manually autonumbered field is not PK. In a case when it is PK (BTW I use Autonumber for this purpose only and vice versa) manually autonumbering should be avoided. "Gaps" ? Big deal, referrential integrity is more important. Actually "gaps" are the price one should pay to avoid reassigning of FKs to a PK generated after old PK was deleted.
If somebody want to enumerate records it easily could be done dynamically with a query.

Regards,

Fish

What you can do is dump that autonumber field and put your own customized incrementing field using vba of the oncurrent event of the form.
The only disadvantage is that the autnumber only populates if you do the entry of the form and not if you enter directly the data into the table.

There is one post here titled Autonumber where I gave an example of how to do this.

[Maxximvs]

Ok, All Solved - Here's How I did it...

So when importing that Vendors Excel Spreadsheet - one of the steps always asks if you would like to add a Sequence Number (an AutoNumber) to your new Table about to be created. The first time I imported I said 'no' cause I couldn't 'see' a use for this number for my records or any appends (the numbers not starting at 1 didn't match up) so we ended up with this:

16 - ZZZ Company
23 - ABC Company
24 - XYZ Company

Of course the challenge is the limited ability of Queries in Access to then have an Append Query pick up your last number and run with it, which is why when I would do my append it would do this (using earlier info on just Max + 1):

16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
25 - LALA Company
25 - BABA Company

Access of course process the Query as a whole before inserting the records at once, so all the Sequence Numbers would = 25 (Max of 24 + 1)

But after a weekend of rest (see, I knew I just needed rest and couldn't see it at the time) - I realised how I could use autonumber - not to use for my records - but as a multiplier for my current Sequence Counter. I re-imported the Vendors table and this time I said 'yes' to the add the Sequence, this now give me the new inported vendors table below:

Auto-Sequence-Vendor

1 - 16 - ZZZ Company
2 - 23 - ABC Company
3 - 24 - XYZ Company

Now I run my append query giving me the following, but missing the Sequence:

1 - 16 - ZZZ Company
2 - 23 - ABC Company
3 - 24 - XYZ Company
4 - - LALA Company
5 - - BABA Company

Now I run an Update Query Updating Sequence with Auto+(Max-Count) where Sequence Is Null and I get:

1 - 16 - ZZZ Company
2 - 23 - ABC Company
3 - 24 - XYZ Company
4 - 25 - LALA Company
5 - 26 - BABA Company

And finally my next query just dumps the unneeded column of Auto (not really needed, but just makes it clean for me to export back out to others):

16 - ZZZ Company
23 - ABC Company
24 - XYZ Company
25 - LALA Company
26 - BABA Company

It can be done, just needs 2 queries (the Append and then Update) instead of just the Append.

Thanks for everyone's ideas.

[missinglinq]

And thank you for posting your solution! This will allow others to benefit from your experience, should they come upon this post while researching similar problems!

Linq ;0)>