development of report number

[johnpolg]

hello,

this is my first time posting. forgive me if the English is bad or i am not explaining myself well.

So i am trying to set up access to develop and store report/document numbers for multiply user to ensure no duplicates are used. (see screen shot from form below).

when a user fills out the form. it build an a document identifier, there the series number and number which are important.

once you select the series number. the the number below should count how many times for example XXXX0783-ZZZZ-10-XXXX-DR-C-AC200 has been enter and add the count to the 200.

for example: if XXXX0783-ZZZZ-10-XXXX-DR-C-AC200 appears 10 times in the table the next document number should be XXXX0783-ZZZZ-10-XXXX-DR-C-AC211 this is the document number.

is this possible? i have done something like this in excel but the information table become to large.

or is there a way if user enters the number it could state the number is already taken?

[orange]

johnpolg,

Your post was awaiting approval which has now been done. If English is not your mother tongue, you can create a post in your language and then use google translate to get English and post that in the forum.

Welcome.

[CarlettoFed]

Of course it can be done but you should attach the Access file to make it easier to help you.

[johnpolg]

i have attached now

[orange]

Where does the 200 and/or 211 in your post #1 come from? Your query doesn't show such a number in your query.

Project Number:	Originator:	Volume	Location	Type Code	Role code	Classification	Document Identifier
;l							;l------
test	test	10	xxxxx	DR	A	AC	test-test-10-xxxxx-DR-A-AC
MCT0783	ZZZ	10	XXXX	RD	S	BR	MCT0783-ZZZ-10-XXXX-RD-S-BR
MCT0783	RPS	00	XXXX	DR	S	BR	MCT0783-RPS-00-XXXX-DR-S-BR
MCT0783	RPS	00	XXXX	DR	S	BR	MCT0783-RPS-00-XXXX-DR-S-BR

[johnpolg]

hi, thanks for the quick reply. i didn't add the number to the query.

as i need the combined number in the 00 record table.

i was trying to you select the series number (100,200,300) this gets added to the number of time the document identifier is in the 00 record table, this would make the document number.

the form should then add all this info to the 00 record table.

[davegri]

i was trying to you select the series number (100,200,300) this gets added to the number of time the document identifier is in the 00 record table, this would make the document number.

If user is entering a new record, how does user know what series number to select?

[johnpolg]

this is assigned to each team, I.e road team bridge team environmental team. the space between the series is the expect number of drawing/report are to produce.

[davegri]

this is assigned to each team, I.e road team bridge team environmental team. the space between the series is the expect number of drawing/report are to produce.

Does this mean that code can automatically enter series when Discipline is entered? If so, series could just be a field in the Discipline table.

[Micron]

To me, this is a document control db, where the successive document identifier that is created in a series gets the next number in that series. IMO, you do this on the final stage, assuming there are stages. In other words, if you allow partial saves, you don't save the document identifier data until all required info is provided. If stages are not applicable, you create the identifier when the record is committed (i.e during the form BeforeUpdate event) so that when the document record is created, that's when you reveal the document identifier. IMO this is the required methodology in a multi user document/record db.

In any event, the next identifier number is the DMax (or Max) of the number field WHERE the other parts match the rest of the document identifier parts. So the document identifier should not be a single field, but a compilation of however many fields would make up the document identifier string.

[johnpolg]

no, there could be multiple series within any discipline depending on the porject.

[johnpolg]

Micron
how, do i do the above. i am at a beginner to intermediate level in MS access. I can see how this would work, Dmax get added to the series number and concatenated with document identifier. how, do i add the concatenated values to the record from the user from. it doesnt seem to be adding to the table.

[Micron]

Using the format from an ISO document and record management db I used to work with as an example, each field would contain an element (portion) of the document identifier, such as

DEPT | ELEMENT | DOC_NO (or using lookup table values for 1st 2 fields)
MACH | 4 | 001

indicating that is document number 001 under element 4 for a particular department. If every department could have a document at that level, another might look like

ELEC | 4 | 001

assuming the pattern allows duplicate numbers (e.g. 001) under different elements and/or departments. In that case DMax returns the greatest doc number for any given combination of DEPT | ELEMENT and adds one. Research DMax with criteria to see how you'd find only the last number that pertains to the combination of department and element.

If the doc number is to be incremented irrespective of any of the other parts, it can simply be the DMax of the document numbers, +1. However, that produces what looks like gaps in a department's document numbering and tends to raise questions about what looks like missing documents.

HTH

EDIT - if not clear, each potion of the document identifier is in a separate field. You string together the identifier portions in forms and reports as required, along with separators if needed (dashes, periods and the like).

[johnpolg]

Hi Micron, thanks again.

how do i a get the information concatenated in the form to the record table?

than i can use Dmax.

[Micron]

I see 7 or 8 parts there, so 7 (or 8) fields. Each field (textbox) must be on the form, hidden if you don't want to see the individual parts. Your document number textbox control source would then be = fld1 & "-" & fld2 & "-" & fld3 & "-" and so on, where fld means the name of the field for that part. You could also string them together with a function if the expression becomes too long for you.

Something tells me you only have one field for the doc number, which will never work as you can't generate numbers using DMax on text fields. That method also severely limits your ability to query and filter records for documents that belong to a dept/group/whatever. Not hard to fix though - you'd add the necessary fields and run a query to parse the document id into its parts. If those parts are like MTC0783 and ZZZ then it would not be properly normalized. Whatever MTC0783 is probably should be in its own table and you'd use the PK for whatever that piece is, same for the other parts. Like this (I left off about 11 fields and removed some values):

DocIDpk	AreaID	Element	DocNum	Title	Status	Media	LstReviseDte	LstReviewDte	NxtReviewDte
1	1	1	1		2	2		22-Dec-05	22-Dec-07

AreaID (1) is the PK value from tblAreas. If that doesn't make sense to you, you probably should research db normalization.