Auto picking numbers

[matjcb]

I’m building database that tracks a multitude of drawings with there changes.
Each change needs a tracking number and must start at 001 for each drawing.
Is there a way I could have Access auto pick the next change number
available for that drawing?

For example
Drawing 101-001
Change 1 would have number 001
Change 2 would have number 002
Change 3 would have number 003

Drawing 122-012
Change 1 would have number 001
Change 2 would have number 002
Change 3 would have number 003

 

[Dale Fye]

Yes, put it would be helpful to know a couple of things:
1. Name of your table
2. Names of the appropriate fields
3. Is this a single user or multiple user application? If multiple, would
more than one person be working with a particular drawing (101, or 122) at
the same time?

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[matjcb]

Thanks

Table Name is “Drawingsâ€

Fields are
-Drawing #
-Rev
-Change type
-Change number
-Change Description

This will be a multi user application and yes we can have more then one
person working with a particular drawing.

 

[Dale Fye]

Now that I see your table structure, I have a couple of additional questions.

1. It looks like you actually have 2 fields that track changes (Rev - I'm
assuming this is for "major revisions" or something like that, and [Change
Number]). Is that assessment correct?

2. What I meant in the previous question #3 was, will you have more than
one person in the application, entering change information for a particular
drawing/Rev at the same time? This makes a difference as there are several
ways to get the change number, and when you use them can depend on whether
you would actually have multiple people entering change information in the
database for drawing #122 at the same time.

3. If I were you, I would probably have several relational tables.

tbl_Drawings: to store specific information that relates to the drawing, but
which does not need to be repeated for the changes. As an example, if these
are architectural drawings, you might store the Client ID, SiteID, and other
fields that are specific to the project.

tbl_Drawing_Changes: to store information specific to the revisions or
changes that relate to a drawing.

I would then use a form and subform for viewing/entering the data, with the
form containing the basic information on the drawing, and the subform
containing the revision/change information.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Lance]

You can use the dmax function for this. Something like..

DMax("[Change number]", "Drawings", "[Drawing #] =101")

 

[xrysa]

hello