Max Value - 1

[kevcar40]

Hi
I have a table that contains the name of companies and a serial
number(unique) plus othe fields
after a 3 month period a new record is created and a new serial number
assigned

what i want to do is when i open a form display the serial number of
the last record along with the current serila number(this is fine) for
that company ie.

table contains
company serial number
firm 1 123
firm 2 124
firm 3 125
firm 1 126


what i to want to do now is show the last number(prior to current
record)

so if i load the current information for firm 1
i have a text box showing showing me the previous serial number of
123

i have created a query to return the max this gives me 126
now i am stuck, i cant get the query to return the last occurrence
any ideas ?


thanks

kevin

 

[Jeff Boyce]

I'm not clear what is and is not working.

If you are using a form, based on a query, you can return the record of the
largest record number for a selected company. You can also use an unbound
control on the form to return a value that represents the overall largest
record number, plus one (using the DMax() function).

But I'm more confused about why your database needs two records re: company
#1...

 

[kevcar40]

thanks for reply
what i have is two occurances of the same record
however i have kept the unique number of each occurance to create an
audit trail
so my first occurance has the index number 123
in three months when i review the record i may or may not change some
information
however to create an audit trail and prove of record being checked i
assign a new index( for all intent a new record)126
what want to do is load the information with the index 126 and
basically i want the previous index to be shown, this will allow the
user to know the index number of the record that has been superceded

hope this clears up the confusion

thanks

kevin

 

[BruceM]

Seems like you're taking the long way around. Why not just have a series of
related records showing when the record was reviewed, and describing any
changes made if that is necessary?
However, if you must use duplicate records, why not just duplicate the
record? Not a good idea, but it could be done. There must be some other
field that can be used as a filter criteria. If there is not, you can
create one. You need some way of associating 123 with 126 anyhow. What do
the two records have in common (besides everything, if it is not changed)?