append query didn't update one field

[babs]

I have been using this append query to add in the records for payroll for the
week for years. for some reason this week it appended the 52 records but did
not put in the value for insurance for this weeks records - when I try to
reappend them now it does put the ins. value in but adds new records with new
check #s. I really just want to update only the insurance field for the
records previously put in. not sure how to say input(update or append) the
value in the ins. field for given number check number.

what am i missing?
thanks,
barb

 

[John W. Vinson]

I have been using this append query to add in the records for payroll for the
week for years. for some reason this week it appended the 52 records but did
not put in the value for insurance for this weeks records - when I try to
reappend them now it does put the ins. value in but adds new records with new
check #s. I really just want to update only the insurance field for the
records previously put in. not sure how to say input(update or append) the
value in the ins. field for given number check number.

what am i missing?
thanks,
barb

An Append query creates new records.
An Update query changes values in existing records.

It sounds like you need to join this table to some other table (your message
does not provide enough information to say) by some unique field and run an
update query, but... we cannot see your screen and we don't know your table
structure, so it's impossible to give specific instructions.

 

[babs]

Sorry for not much detail

I would LOVE to delete the appended records that did not come in right and
reappend the records but the [checknum] field generates new check numbers
each time you reappend ( autonumber) but I would like it to reset back to 1
plus the last record - how can I do this???

thanks,
barb

 

[John W. Vinson]

Sorry for not much detail

I would LOVE to delete the appended records that did not come in right and
reappend the records but the [checknum] field generates new check numbers
each time you reappend ( autonumber) but I would like it to reset back to 1
plus the last record - how can I do this???

Depends on your version of Access. Actually Autonumbers are designed for one
purpose, and one purpose ONLY: to provide a meaningless unique key. They are
*not* designed to be sequential, or gapless, and they should emphatically NOT
be used for check numbers, since a check number refers to a physical piece of
paper which the database should reference, and since (as you have now
discovered) you really cannot control the value of an Autonumber.

What you can TRY is deleting the erroneous records, and use Tools... Database
Utilites... Compact and Repair. In some versions of Access that will reset the
autonumber seed to one more than the highest existing value. That's not true
in all versions though.

I would really, really recommend using a Long Integer datatype, not an
autonumber, for your check number field, and incrementing it with code.

 

[babs]

Thanks for the advice and I know you are right that it shouldn't be used as a
check number. Unfortunately I inhereted this database and that is how is has
been used for over 5 years. any way to modify the table checknum after the
fact to increment + one of last or max.

I tried the compact in 2007 and it didn't reset them.

thanks for your help,
Barb

Sorry for not much detail

I would LOVE to delete the appended records that did not come in right and
reappend the records but the [checknum] field generates new check numbers
each time you reappend ( autonumber) but I would like it to reset back to 1
plus the last record - how can I do this???

Depends on your version of Access. Actually Autonumbers are designed for one
purpose, and one purpose ONLY: to provide a meaningless unique key. They are
*not* designed to be sequential, or gapless, and they should emphatically NOT
be used for check numbers, since a check number refers to a physical piece of
paper which the database should reference, and since (as you have now
discovered) you really cannot control the value of an Autonumber.

What you can TRY is deleting the erroneous records, and use Tools... Database
Utilites... Compact and Repair. In some versions of Access that will reset the
autonumber seed to one more than the highest existing value. That's not true
in all versions though.

I would really, really recommend using a Long Integer datatype, not an
autonumber, for your check number field, and incrementing it with code.

 

[John W. Vinson]

Thanks for the advice and I know you are right that it shouldn't be used as a
check number. Unfortunately I inhereted this database and that is how is has
been used for over 5 years. any way to modify the table checknum after the
fact to increment + one of last or max.

I tried the compact in 2007 and it didn't reset them.

I guess that's one of the versions that doesn't.

What you could do is create a new table with the same field definitions as
this one, using a Long Integer field for the datatype of this ID. Make it the
primary key, and decline Access' offer to add an autonumber field. You can
then run an Append query to migrate all the data from the existing table into
this new one. Drop all the relationships to the old table and reestablish them
to the new one.

You can use code in the Form's BeforeInsert event (or some other appropriate
event that happens when a new check is needed) to look up the highest existing
check number and increment it.