OT: Access tables - setting value of a new record by looking up

Yahoo Archive
1
Thanks for the advice - Unfortunately I need to retain most of the
original semi-sequential numbering systems, as they are being used to
provide serial numbers, drawing and various other record, so I'll try
the DMax approach.



Where I can, I intend to simplify things by dropping the old numbering
systems (often alpha-numeric composite strings) for a simplified numeric
system!



Best Regards,



Paul Whittle

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Mark Wonsil
Sent: 20 June 2007 14:58
To: vantage@yahoogroups.com
Subject: RE: [Vantage] OT: Access tables - setting value of a new record
by looking up the value in another table



> Have you considered using two different keys? When converting data
from
> an old system to Access I've often set up two keys. The old key which
> is just an indexed field and then a new key that is the autonumbered
> primary key. It keeps me from having to manually control a sequencial
> key.

I agree with Lon. But if you really need to manage the sequential key,
lookup
up the domain function DMax. It will return the highest value in a
column for
a given domain (in this case a table).

Mark W.



Click here
<https://www.mailcontrol.com/sr/wQw0zmjPoHdJTZGyOCrrhg==
cHamQ+aImTboPCR72D4roNxkpMmBjNKqfZAhmtVAt9CuFfG33Kr+KZbQpsjW0vvCnseIHKJG
n+37UOipLZ29vmsFyzPVhOMsi5Hd8kyb0M!Eb7OhLqlPEJH+Mkn9qdZ82NEjZn+ZIkaVq6ay
GAfZwswZDoU8ahx5p8BC!PV0QeXSl+LmMraunYW3> to report this email as spam.



[Non-text portions of this message have been removed]
2
Help!



I am somewhat inexperienced in doing much with Access other than using
it to querying data sets to create multitudes of reports, and I am
trying to convert a bunch of databases from Lotus Approach to MS Access
2003. Originally I thought I would be able to use Autonumber to create
the sequential numbering of the database entries, but subsequently I
have realised that the historical format of the key data has changed
over the years, and we also have the odd gap in the numbering sequence,
so nothing totally sequential will work!.



I can foresee that the solution is to have a separate table holding the
value of the last record, and when creating a new record, to read this
table, add 1 to it, write this into the appropriate field of the new
record, and then to update the separate table with the revised number.
This has the advantage of being able to import the old data as it is,
and to ensure that all new records are truly sequential.



It would seem to me that the default field value should therefore be the
reference table field value + 1, but I can't make this work, and
similarly I can't fathom the syntax of using set value as an event to
write the number back.



Can any of you Access gurus point me in the right direction? I think I
am missing something very fundamental!



Kind Regards,

Paul Whittle
Business Process Manager
Jiskoot Ltd

Goods Station Road

Tunbridge Wells

Kent TN1 2DJ

UK
Switchboard: +44 (0) 1892 518000
Direct: +44 (0) 1892 779975
Fax: +44 (0) 1892 518100

www.jiskoot.com <http://www.jiskoot.com/>





This message has been scanned for viruses by BlackSpider MailControl - www.blackspider.com

This email transmission is confidential and intended solely for the person or organisation to whom it is addressed. If you are not the intended recipient,you must not copy, distribute or disseminate the information, or take any action in reliance on it. Any views expressed in this message are those of the individual sender, except where the sender specifically states them to be the views of an organisation or employer. If you have received this message in error, please do not open any attachment, but notify the sender(above) and delete this message from your system. Please rely on your own virus check; no responsibility is taken by the sender for any damage arising out of any bug or virus infection.

Jiskoot Limited
Reg. Office: 2 Bloomsbury Street, London WC1B 3ST
Reg. No.: 1834943 England



[Non-text portions of this message have been removed]
3
Have you considered using two different keys? When converting data from
an old system to Access I've often set up two keys. The old key which
is just an indexed field and then a new key that is the autonumbered
primary key. It keeps me from having to manually control a sequencial
key.



Lon A. Wiksell

ROM Corporation

816-318-8000



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Paul Whittle
Sent: Wednesday, June 20, 2007 8:23 AM
To: vantage@yahoogroups.com
Subject: [Vantage] OT: Access tables - setting value of a new record by
looking up the value in another table



Help!

I am somewhat inexperienced in doing much with Access other than using
it to querying data sets to create multitudes of reports, and I am
trying to convert a bunch of databases from Lotus Approach to MS Access
2003. Originally I thought I would be able to use Autonumber to create
the sequential numbering of the database entries, but subsequently I
have realised that the historical format of the key data has changed
over the years, and we also have the odd gap in the numbering sequence,
so nothing totally sequential will work!.

I can foresee that the solution is to have a separate table holding the
value of the last record, and when creating a new record, to read this
table, add 1 to it, write this into the appropriate field of the new
record, and then to update the separate table with the revised number.
This has the advantage of being able to import the old data as it is,
and to ensure that all new records are truly sequential.

It would seem to me that the default field value should therefore be the
reference table field value + 1, but I can't make this work, and
similarly I can't fathom the syntax of using set value as an event to
write the number back.

Can any of you Access gurus point me in the right direction? I think I
am missing something very fundamental!

Kind Regards,

Paul Whittle
Business Process Manager
Jiskoot Ltd

Goods Station Road

Tunbridge Wells

Kent TN1 2DJ

UK
Switchboard: +44 (0) 1892 518000
Direct: +44 (0) 1892 779975
Fax: +44 (0) 1892 518100

www.jiskoot.com <http://www.jiskoot.com/ <http://www.jiskoot.com/> >

This message has been scanned for viruses by BlackSpider MailControl -
www.blackspider.com

This email transmission is confidential and intended solely for the
person or organisation to whom it is addressed. If you are not the
intended recipient,you must not copy, distribute or disseminate the
information, or take any action in reliance on it. Any views expressed
in this message are those of the individual sender, except where the
sender specifically states them to be the views of an organisation or
employer. If you have received this message in error, please do not open
any attachment, but notify the sender(above) and delete this message
from your system. Please rely on your own virus check; no responsibility
is taken by the sender for any damage arising out of any bug or virus
infection.

Jiskoot Limited
Reg. Office: 2 Bloomsbury Street, London WC1B 3ST
Reg. No.: 1834943 England


[Non-text portions of this message have been removed]





[Non-text portions of this message have been removed]
4
> Have you considered using two different keys? When converting data from
> an old system to Access I've often set up two keys. The old key which
> is just an indexed field and then a new key that is the autonumbered
> primary key. It keeps me from having to manually control a sequencial
> key.

I agree with Lon. But if you really need to manage the sequential key, lookup
up the domain function DMax. It will return the highest value in a column for
a given domain (in this case a table).

Mark W.