Automatically adding a subnumber

[Sniper-BoOyA-]

Good morning,

I am working on a small database for performing concrede tests.
One of the tests is the 'Bending' test.

Basically, the people in the lab make a prism of concrete.

And according to the dimensions (length, height and width) of the prism they calculate the 'stress rate' they need to apply in order to break the prism into 2 halfs.

Both halfs will be used for other tests.

Anyways, when the people in the lab make the prism they will give it a number.

What i would like to is ; once the bending test is done, add a subnumber to the 2 halfs.

Example:

Prismnumber : 13-101
Prism half 1 : 13-101a
Prism half 2 : 13-101b

So basically i would like to add either an "a" or "b" to the allready existing Prismnumber. And this for ever tested prism.

The problem is; I cant not figure out how to do this. Ive tried adding an some sort of autonumbering function. Which worked, but not the way i wanted it.

My question:

Do you know if its possible to automacally add a subnumber (in this case text "a" or "b"). So both the prism and its halfs will be numbered?

Cheers!

 

[Mr. B]

Why not just use the "a" and "b" without actually adding the initial prism number. Later if you can concatenate the initial prism number in front of the "a" or "b" when it is needed for clarification.

Both halfs will be used for other tests.

Just a comment: If each of the halves of the prism are going to have additional tests will you not need to keep track of that test data? If so, want you need another table that will hold a record for prism half "A" and another record for prism half "B"? I am thinking that you many not have your database in a normalized state. I could be wrong. Just something I thought you might want to consider.

 

[plog]

I agree with Mr. B, especially the part about your table not being normalized. Could you post your structure (table and field names) along with some sample data?

Regardless of what the lab techs name the name the initial prism, you should probably give it its own internal autonumber ID. Then, each prism half would get its own autonumber ID in another table. Don't worry, you will still save the lab techs ID for each piece and if its A or B, its just for your database you will assign your own internal IDs.

 

[jdraw]

I agree with Mr B and plog - some normalization will help.
Without knowing much of your environment I see something along these lines

Prism---->PostTestPieces
1------------>Many
where
tblPrism
PrismID autonumber PK
other Prism info

tblPostTestPieces
PTPieceID autonumber PK
PrismID_FK Long Integer (foreign key to tblPrism)
otherPTPiece info

Good luck.

 

[Sniper-BoOyA-]

Thanks for the feedback guys. I will see if i can put something together.

 

[Sniper-BoOyA-]

Hopefully this will clear things up and will give you an idea how the tables and relations are currently set.

At this point, 2 tables are important:

Table : Labgegevens (Labinfo)
This table will be used to store general data related to the sample (in this case prism) Such as Projectnumber, dates and some checkboxes to let the user select the tests he would like to perform on this sample.

Table : Mortelproefstukken (Prism table)
In this table the data relating to do bending test of the prism will be stored.

The properties of both tables are:

Labgegevens (Labinfo)

Labnr (samplenr)..........text--> Pk
Registrationdate..........date
Samplingdate..............date
Pouringdate ...............date
Tested ............yes/no
Fluxuralstrength..........yes/no
Compressivestrength .. yes/no

Mortelproefstukken (prism table)

Labnr (samplenr).......... text --> Fk
Testingdate ............date
Lengthprism ..................num
dprism ........................num
Flatnessprism ...............num
Squarenessprism ...........num
Surfacecondition...........text
distancerolls.................num
breakingload.................num

The FluxuralStrength will be calculated by the formule:

(1.5*breakingload*distancerolls)/(dprisma^3)

During the process the prism will break in 2.

Now i would like to number the two halfs, and perform a compressive strength on both halfs.

 

[Mr. B]

You will need another table where you can create each of the two records needed to store the information about the tests that will be performed on each of the two halves of the first prism. In this new table you would include a foreign key to the original prism record so you would have the two halves linked to the original record from which they originated.

 

[jdraw]

I agree with Mr B- table like in my post #4.

I also see other entities ("possible") such as Test and Project.
Getting the tables set up correctly to meet your needs is an important step. I don't think tit is a good strategy to piecemeal the tables --

At this point, 2 tables are important

Spend the time now to make sure your tables support your "business".

Also, I would not use text pks. You can keep text fields for your use, but I'd give Access the autonumber PKs for his use.