How to format autonumbers

  • Thread starter Thread starter Deanna
  • Start date Start date
D

Deanna

I would like to know if there is a way to autonumber a
field based on another field.

Example:

Goals Table
Goal (A, B, C, etc.)
Description

Results Table
Goal (from Goal Table)
Result #
Result Description

There can be multiple results for each goal. Therefore,
the result # needs to be A.1, A.2, B.1, C.1, C.2, etc.

Is there a way to have the Result # autonumber depending
on which Goal is in the Goal field? Say if I enter A in
the Goal field on the Result Table, it would create A.1
for the result #. If there is already an A.1 and A.2, it
would create A.3.

Thanks for any help.
Deanna
 
Auto numbers are good for only one thing. They create an almost always
unique number. They don't always stay in order or they may skip numbers or
fill in old unused numbers etc.

You can however combine two fields in a query. You can create a new
field in the query combining two other fields. However this would not
accomplish what you want, even if the autonumber was consistent.

To do what you want will require using VBA (writing code) with luck
someone who has already written the code will read this and give you an
example. If not you may wish to ask the question again a little different.
The solution does not use auto numbers

Have you used any VBA?

Good Luck
 
On my website (see sig below) are a couple of small sample databases that
might help: "FormSubform.mdb" and "AutonumberProblem.mdb". Neither do
exactly what you want, but they may give you some ideas.
 
Joseph Meehan said:
Auto numbers are good for only one thing. They create an almost always
unique number. They don't always stay in order
Click to expand...

If using the Random property which is required for replicated MDBs.
In which case they are absolutely not in order.
fill in old unused numbers etc.
Click to expand...

Eh? I haven't seen this behavior.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Tony Toews said:
If using the Random property which is required for replicated MDBs.
In which case they are absolutely not in order.
Click to expand...

If you do a compact of a database it may reuse the numbers that have
been deleted.

It should not be counted on to do so however.
 
Joseph Meehan said:
If you do a compact of a database it may reuse the numbers that have
been deleted.

It should not be counted on to do so however.
Click to expand...

Ah, yes, ok. I was thinking in terms of 1,2,4,5,6 and Jet would
reissue 3.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
 
Back
Top