How to Compare Autonumbers and Return the Larger Value, and try to Assign it to a Bound Field

[Megan]

Hi everybody-

I have 2 tables, Hearings and Rulings. Both have primary keys called,
CaseID, that are autonumbers. I don't want both tables to have the
same autonumber. For example, if Hearings has 55, then I want Rulings
to have a number greater than 55. Two or more Hearings may be entered
before a Ruling is entered. For example, Hearings with CaseIDs= 55,
56, and 57 may be entered before a Ruling or vice versa. There is no
definite order. Hearings and Rulings can be entered at any time, in
any order. It all depends on when the users enter the Hearing and/ or
Ruling information.

I can get the maximum number by using the "Max" function. Right now on
the Hearings form, I use the Max function to get the highest/ biggest
autonumber for the Hearings table, i.e. Max([CaseID]). I tried using
the DMax function: DMax(expression, domain, criteria) but it didn't
work for me. Maybe I used it wrong because I didn't enter any criteria
because I'm unsure what criteria to use. But Access Help states that
the criteria is optional. I tried DMax([CaseID], Hearings table) but
it just returned the first/ lowest record number.

Anyway, I was thinking about using the Max function in a hidden text
box on the Hearing form and add one to it to get an autonumber one
value higher than the highest CaseID in the Rulings table and then
assign it to the CaseID of that particular new Hearing. But I'm not
sure if the Max function can do that.

My query for the Hearing form retrieves info like: CaseID,
DateReceived, DecisionDate, etc.. The Ruling form retrieves similar
types of data, but there are some differences. That's why I split the
one original table, generically called "Case" into two separate
tables. One for just Hearings, one for just Rulings. I guess this was
a good idea. Anyway, the CaseID field for both tables is an
autonumber. Since there are more Hearings, the autonumber for the
Hearings table is higher, ui.e. 255. The last Ruling was like 250. So
the next 5 and on Rulings are going to be 251, 252, 253, 254, 255,
256, etc... So the same numbers are going to be used for Hearings and
Rulings.

What I'm thinking about doing is somehow (no idea yet) of comparing
the highest/ biggest CaseID autonumbers from both tables, then adding
one to it to get one number higher, then assigning whatever Hearing or
Ruling is being entered into the database that number. The queries for
both Hearings and Rulings retrieves the CaseID from their associated
queries. The data entry and data view forms are identical. The CaseIDs
are bound. Can I set a bound field equal to another field?

For example, I put a text box that retrieves the highest autonumber
from the table then adds one to it. Let's call this text box,
NewValue. Can I set the bound CaseID equal to this?

NewValue = Max([CaseID])

CaseID = NewValue and still be bound to the associated field in thed
query that retrieves CaseID from both the Hearings and Rulings tables?

But this leads me to another problem. I might have to run some kind of
query that compares the autonumbers from both tables, then returns
only the autonumber from the table that has the biggest value. Then
add one to it. Then assign it to CaseID somehow.

Does any of this make sense? It's late, and I'm beat.

Any suggestions will be a Welcome suprise!

G'Night! Thanks!

Megan

[Tom van Stiphout]

On 9 Aug 2004 20:54:31 -0700, me**************@hotmail.com (Megan)
wrote:

As has been argued here many times: you should NOT assign meaning to
autonumbers. They are unique numbers; period. This page:
http://www.mvps.org/access/general/gen0025.htm makes the same point,
but one day I'm going to be mad enough to put up a more detailed page
of my own :=)

When you say: "I don't want both tables to have the same autonumber"
you are attaching too much meaning.

Rather think about some sort of meaningful linkage. A ruling follows a
hearing. Perhaps it makes sense to have a HearingID in the Ruling
table, so you could easily establish which hearing led to which
ruling.

-Tom.

Hi everybody-

I have 2 tables, Hearings and Rulings. Both have primary keys called,
CaseID, that are autonumbers. I don't want both tables to have the
same autonumber. For example, if Hearings has 55, then I want Rulings
to have a number greater than 55. Two or more Hearings may be entered
before a Ruling is entered. For example, Hearings with CaseIDs= 55,
56, and 57 may be entered before a Ruling or vice versa. There is no
definite order. Hearings and Rulings can be entered at any time, in
any order. It all depends on when the users enter the Hearing and/ or
Ruling information.

I can get the maximum number by using the "Max" function. Right now on
the Hearings form, I use the Max function to get the highest/ biggest
autonumber for the Hearings table, i.e. Max([CaseID]). I tried using
the DMax function: DMax(expression, domain, criteria) but it didn't
work for me. Maybe I used it wrong because I didn't enter any criteria
because I'm unsure what criteria to use. But Access Help states that
the criteria is optional. I tried DMax([CaseID], Hearings table) but
it just returned the first/ lowest record number.

Anyway, I was thinking about using the Max function in a hidden text
box on the Hearing form and add one to it to get an autonumber one
value higher than the highest CaseID in the Rulings table and then
assign it to the CaseID of that particular new Hearing. But I'm not
sure if the Max function can do that.

My query for the Hearing form retrieves info like: CaseID,
DateReceived, DecisionDate, etc.. The Ruling form retrieves similar
types of data, but there are some differences. That's why I split the
one original table, generically called "Case" into two separate
tables. One for just Hearings, one for just Rulings. I guess this was
a good idea. Anyway, the CaseID field for both tables is an
autonumber. Since there are more Hearings, the autonumber for the
Hearings table is higher, ui.e. 255. The last Ruling was like 250. So
the next 5 and on Rulings are going to be 251, 252, 253, 254, 255,
256, etc... So the same numbers are going to be used for Hearings and
Rulings.

What I'm thinking about doing is somehow (no idea yet) of comparing
the highest/ biggest CaseID autonumbers from both tables, then adding
one to it to get one number higher, then assigning whatever Hearing or
Ruling is being entered into the database that number. The queries for
both Hearings and Rulings retrieves the CaseID from their associated
queries. The data entry and data view forms are identical. The CaseIDs
are bound. Can I set a bound field equal to another field?

For example, I put a text box that retrieves the highest autonumber
from the table then adds one to it. Let's call this text box,
NewValue. Can I set the bound CaseID equal to this?

NewValue = Max([CaseID])

CaseID = NewValue and still be bound to the associated field in thed
query that retrieves CaseID from both the Hearings and Rulings tables?

But this leads me to another problem. I might have to run some kind of
query that compares the autonumbers from both tables, then returns
only the autonumber from the table that has the biggest value. Then
add one to it. Then assign it to CaseID somehow.

Does any of this make sense? It's late, and I'm beat.

Any suggestions will be a Welcome suprise!

G'Night! Thanks!

Megan

[Jack MacDonald]

I agree with Tom's point about "meaningless" autonumbers -- that's the
best approach. However, if you are intent on exposing the autonumbers
to the users, a different database structure may accomplish your
goals.

Create a new table with a single, autonumber field called CaseID.
Change the field type of the two, existing CaseID fields to
LongInteger, and Index the fields without duplicates. In the Relation
windows, relate both of the existing tables to the new caseID table.
You have now created two One-to-One relationships. Base all your input
forms on queries that include CaseID table plus one of the other
tables.

When you create a new record in CaseID and begin to populate fields in
the subordinate tables, a matching CaseID value will be placed into
the subordinate tables automatically.

On Mon, 09 Aug 2004 22:21:57 -0700, Tom van Stiphout
<no*************@cox.net> wrote:

On 9 Aug 2004 20:54:31 -0700, me**************@hotmail.com (Megan)
wrote:

As has been argued here many times: you should NOT assign meaning to
autonumbers. They are unique numbers; period. This page:
http://www.mvps.org/access/general/gen0025.htm makes the same point,
but one day I'm going to be mad enough to put up a more detailed page
of my own :=)

When you say: "I don't want both tables to have the same autonumber"
you are attaching too much meaning.

Rather think about some sort of meaningful linkage. A ruling follows a
hearing. Perhaps it makes sense to have a HearingID in the Ruling
table, so you could easily establish which hearing led to which
ruling.

-Tom.

Hi everybody-

I have 2 tables, Hearings and Rulings. Both have primary keys called,
CaseID, that are autonumbers. I don't want both tables to have the
same autonumber. For example, if Hearings has 55, then I want Rulings
to have a number greater than 55. Two or more Hearings may be entered
before a Ruling is entered. For example, Hearings with CaseIDs= 55,
56, and 57 may be entered before a Ruling or vice versa. There is no
definite order. Hearings and Rulings can be entered at any time, in
any order. It all depends on when the users enter the Hearing and/ or
Ruling information.

I can get the maximum number by using the "Max" function. Right now on
the Hearings form, I use the Max function to get the highest/ biggest
autonumber for the Hearings table, i.e. Max([CaseID]). I tried using
the DMax function: DMax(expression, domain, criteria) but it didn't
work for me. Maybe I used it wrong because I didn't enter any criteria
because I'm unsure what criteria to use. But Access Help states that
the criteria is optional. I tried DMax([CaseID], Hearings table) but
it just returned the first/ lowest record number.

Anyway, I was thinking about using the Max function in a hidden text
box on the Hearing form and add one to it to get an autonumber one
value higher than the highest CaseID in the Rulings table and then
assign it to the CaseID of that particular new Hearing. But I'm not
sure if the Max function can do that.

My query for the Hearing form retrieves info like: CaseID,
DateReceived, DecisionDate, etc.. The Ruling form retrieves similar
types of data, but there are some differences. That's why I split the
one original table, generically called "Case" into two separate
tables. One for just Hearings, one for just Rulings. I guess this was
a good idea. Anyway, the CaseID field for both tables is an
autonumber. Since there are more Hearings, the autonumber for the
Hearings table is higher, ui.e. 255. The last Ruling was like 250. So
the next 5 and on Rulings are going to be 251, 252, 253, 254, 255,
256, etc... So the same numbers are going to be used for Hearings and
Rulings.

What I'm thinking about doing is somehow (no idea yet) of comparing
the highest/ biggest CaseID autonumbers from both tables, then adding
one to it to get one number higher, then assigning whatever Hearing or
Ruling is being entered into the database that number. The queries for
both Hearings and Rulings retrieves the CaseID from their associated
queries. The data entry and data view forms are identical. The CaseIDs
are bound. Can I set a bound field equal to another field?

For example, I put a text box that retrieves the highest autonumber
from the table then adds one to it. Let's call this text box,
NewValue. Can I set the bound CaseID equal to this?

NewValue = Max([CaseID])

CaseID = NewValue and still be bound to the associated field in thed
query that retrieves CaseID from both the Hearings and Rulings tables?

But this leads me to another problem. I might have to run some kind of
query that compares the autonumbers from both tables, then returns
only the autonumber from the table that has the biggest value. Then
add one to it. Then assign it to CaseID somehow.

Does any of this make sense? It's late, and I'm beat.

Any suggestions will be a Welcome suprise!

G'Night! Thanks!

Megan

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security