Auto Numbering in Form

[JP via AccessMonster.com]

I need to find a way to display in a form text box the highest numerical
value entered in any one of four fields for the last record entered.

For example: If
Field_1 is 0001
Field_2 is 0008
Field_3 is 0005
Field_4 is 0004

When the form is opened, the textbox would display the number 0008. The
textbox should display the highest of the four fields of the last record.

 

[Jeff Boyce]

Define "last". The way Access stores records and the way you conceptualize
"last" may not match up.

Comparing "across" is something you do in a spreadsheet. It's rarely
appropriate in a well-normalized relational database, like Access.

And why? You've described a "how" (get max of 'last' record's 4 fields).
If you'll describe a bit more about what having that (and what those
represent) will allow you to do, the newsgroup readers may be able to offer
more specific suggestions to meet your underlying business need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[JP via AccessMonster.com]

The four Fields represent specific numbers used in customer billing. When the
user opens the form the new record may or not require this specific number,
but the user is a where of the highest of numbers. What I left out before was
that the textbox should display highest of the four fields when they are
actually used. In other words, several records may pass before a value or
values are entered, so the textbox should keep track of this number.

Thank you for your help!

Define "last". The way Access stores records and the way you conceptualize
"last" may not match up.

Comparing "across" is something you do in a spreadsheet. It's rarely
appropriate in a well-normalized relational database, like Access.

And why? You've described a "how" (get max of 'last' record's 4 fields).
If you'll describe a bit more about what having that (and what those
represent) will allow you to do, the newsgroup readers may be able to offer
more specific suggestions to meet your underlying business need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to find a way to display in a form text box the highest numerical
value entered in any one of four fields for the last record entered.

[quoted text clipped - 7 lines]

When the form is opened, the textbox would display the number 0008. The
textbox should display the highest of the four fields of the last record.

 

[Jeff Boyce]

Based on your description, you'll want to reconsider using a spreadsheet,
or, consider taking on the fairly steep learning curve to pick up more of
what Access requires and offers.

Doing what you are describing is relatively simple in Access ... IF you've
created a data structure that is well-normalized. Repeating fields (same
type data, multiple columns) is not well-normalized.

Is there a reason you have to do this in Access?

Another option might be to collect the data (in Access), export it to Excel,
and do your comparison there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The four Fields represent specific numbers used in customer billing. When
the
user opens the form the new record may or not require this specific
number,
but the user is a where of the highest of numbers. What I left out before
was
that the textbox should display highest of the four fields when they are
actually used. In other words, several records may pass before a value or
values are entered, so the textbox should keep track of this number.

Thank you for your help!

Define "last". The way Access stores records and the way you
conceptualize
"last" may not match up.

Comparing "across" is something you do in a spreadsheet. It's rarely
appropriate in a well-normalized relational database, like Access.

And why? You've described a "how" (get max of 'last' record's 4 fields).
If you'll describe a bit more about what having that (and what those
represent) will allow you to do, the newsgroup readers may be able to
offer
more specific suggestions to meet your underlying business need.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I need to find a way to display in a form text box the highest numerical
value entered in any one of four fields for the last record entered.

[quoted text clipped - 7 lines]

When the form is opened, the textbox would display the number 0008. The
textbox should display the highest of the four fields of the last
record.

 

[JP via AccessMonster.com]

It is ideal for me to use Access for many reasons.

Is what I've describe possible to do with the DMax function?

Thanks

Based on your description, you'll want to reconsider using a spreadsheet,
or, consider taking on the fairly steep learning curve to pick up more of
what Access requires and offers.

Doing what you are describing is relatively simple in Access ... IF you've
created a data structure that is well-normalized. Repeating fields (same
type data, multiple columns) is not well-normalized.

Is there a reason you have to do this in Access?

Another option might be to collect the data (in Access), export it to Excel,
and do your comparison there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The four Fields represent specific numbers used in customer billing. When
the

[quoted text clipped - 32 lines]

 

[Douglas J. Steele]

DMax will give the the maximum value for a single field over a number of
rows (where the number of rows can be influenced through a Where clause).

You haven't given any indication whether or not the 4 fields are in a single
row, or are the same field in 4 separate rows. If they're 4 fields in a
single row, then DMax won't do anything for you.

It may well be ideal for you to use Access, but it will be even better for
you if you use it properly!

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

It is ideal for me to use Access for many reasons.

Is what I've describe possible to do with the DMax function?

Thanks

Based on your description, you'll want to reconsider using a spreadsheet,
or, consider taking on the fairly steep learning curve to pick up more of
what Access requires and offers.

Doing what you are describing is relatively simple in Access ... IF you've
created a data structure that is well-normalized. Repeating fields (same
type data, multiple columns) is not well-normalized.

Is there a reason you have to do this in Access?

Another option might be to collect the data (in Access), export it to
Excel,
and do your comparison there?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The four Fields represent specific numbers used in customer billing.
When
the

[quoted text clipped - 32 lines]

textbox should display the highest of the four fields of the last
record.

 

[JP via AccessMonster.com]

I’m trying to display the maximum value of multiple fields, over a number of
records.

This is the best visual representation of the kind of situation I’m up
against.

Column1-------Column2-------Column3-------Column4
======================================

0001---------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
0002------------0003--------------------------------------------------
0004---------------------------------------------------------------------
----------------------------------------------------------------------------
0005------------0006-------------0007------------------------------

Each of the values above are examples of numbers entered into four different
textboxes by the users through a form. Each textbox is assigned to each
Column.
I would like to have an additional textbox within the form that displays the
next available number of the last record where the number was actually used.
Therefore, the function must keep track of the maximum value across all four
Columns. Also, some rows do not contain anything (because the specific record
did not require the use of the number), but the textbox will still keep track
of the next available number. In the example above the textbox would display
“0008â€. The user may or may not need to use this number for the particular
entry, but never the less “0008†is waiting to be used.

DMax will give the the maximum value for a single field over a number of
rows (where the number of rows can be influenced through a Where clause).

You haven't given any indication whether or not the 4 fields are in a single
row, or are the same field in 4 separate rows. If they're 4 fields in a
single row, then DMax won't do anything for you.

It may well be ideal for you to use Access, but it will be even better for
you if you use it properly!

It is ideal for me to use Access for many reasons.

[quoted text clipped - 27 lines]

 

[Douglas J. Steele]

I would strongly advise rethinking your data design. While that may work
well in Excel, it won't work well in a relational database.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I'm trying to display the maximum value of multiple fields, over a number
of
records.

This is the best visual representation of the kind of situation I'm up
against.

Column1-------Column2-------Column3-------Column4
======================================

0001---------------------------------------------------------------------
----------------------------------------------------------------------------
----------------------------------------------------------------------------
0002------------0003--------------------------------------------------
0004---------------------------------------------------------------------
----------------------------------------------------------------------------
0005------------0006-------------0007------------------------------

Each of the values above are examples of numbers entered into four
different
textboxes by the users through a form. Each textbox is assigned to each
Column.
I would like to have an additional textbox within the form that displays
the
next available number of the last record where the number was actually
used.
Therefore, the function must keep track of the maximum value across all
four
Columns. Also, some rows do not contain anything (because the specific
record
did not require the use of the number), but the textbox will still keep
track
of the next available number. In the example above the textbox would
display
"0008". The user may or may not need to use this number for the particular
entry, but never the less "0008" is waiting to be used.

DMax will give the the maximum value for a single field over a number of
rows (where the number of rows can be influenced through a Where clause).

You haven't given any indication whether or not the 4 fields are in a
single
row, or are the same field in 4 separate rows. If they're 4 fields in a
single row, then DMax won't do anything for you.

It may well be ideal for you to use Access, but it will be even better for
you if you use it properly!

It is ideal for me to use Access for many reasons.

[quoted text clipped - 27 lines]

textbox should display the highest of the four fields of the last
record.