Autonumber loop? (subnumber)

Sniper-BoOyA-

Registered User.
Local time
Yesterday, 16:53
Joined
Jun 15, 2010
Messages
204
Goof Afternoon,

I am working on a database for our laboratory and ive come across the following;

The database i have been working on is for our concrete lab.
They make prism's and they perform a couple of tests on those prisms. (Flexural strenght and Compressive Strength).

Basically they get the a sample from the client. They name the sample with a sampleID. They use the sample to make the prisms (6 total).

3 prisms to be tested after 28 days, and 3 prisms to be tested after 91 days.

Ive split both in to 2 tables.

Both tables contain the following fields;

id - Autonumbering
labnr - Text (translation: SampleID)
DateofTest - Date/Time
LengthPrism - Num
DPrism - Num
....

id_____labnr ...

1____13-001
2____13-001
3____13-001
4____13-002
5____13-002
6____13-002

Now heres my question;

Is it possible to add another field, and add some sort of autonumbering loop which allows you to number the prisms inidividually as long as the labnr is the same?

I.e. ; Each Labnumber will have 3 prisms and i am looking to do the following;


id_____labnr____AUTONUMBERLOOP

1____13-001________1
2____13-001________2
3____13-001________3
4____13-002________1
5____13-002________2
6____13-002________3

My question; Is this possible, if yes how would you do this?
Ive tried a lot of things like adding a autonumbering field to the table and somehow control the autonumbering mechanic, but no luck so far.

If you guys have any ideas/thoughts, i am looking forward to hear those.

Cheers,

Michael Schrijver.
 
You can start the first record as 1 and use a function to add 1 but filter it for each lab number.

Look up DMAX :)
 
Both tables contain the following fields;

You should probably normalize your tables. No reason to have all the fields in both. The Sample table should have SampleID as primary key, and dates and other fields unique to each sample. The Prism table linked to it, with SampleID plus prism # as pk, with only data unique to each prism. Then display and edit with a form/subform. That wouldn't solve the numbering issue, but would make the data more efficient and manageable. Scubadiver's suggestion sounds good. In the function, set up a query of records with the current SampleID, and then use rs.RecordCount + 1 to get the next prism #.
 
Hi Michael,

IIUC, the labnr field suffix (the numerical value after "-") already determines whether the test is the one after 28 days or the one after 91 days. If that is the case you do not need a second table to separate the type of testing. You already know it. It is gived by the "-00x" portion of the lab number. (You can also get rid of the '0's if there are only two types of runs).
Whether you should have a separate prism table, you will need to determine. If the prism simply identifies a test speciment of standard concrete mix tested by the the lab then likely don't bother. However, if the ingredients or mixing methods vary then, yes, you need one.
The new sequence number of the prism test (your "autonumber loop") is easily determined by the DMAX function as scubadiver suggests, ie assumeing that the field is named PrismTestNo you can get a new record number with
Code:
PrismTestNo = Nz(DMax("PrismTestNo", "YourTableName", "labnr = '" & Me!labnr & "'")) + 1
You should probably test first if the value for PrismTestNo exceeds the count of 3 and cancel the record if it is so. Good Luck with your project.

Best,
Jiri
 
Thanks for the pointers guys! I will have a look at it..

I will let you guys know how things work out!

Thanks again.
 
I wasn't familiar with DMAX, but I love it! Now I have to go back and rewrite every app I developed in the last 15 years :(

Always more to learn!
 

Users who are viewing this thread

Back
Top Bottom