Add multiple records to same table from subform

[1gixxer]

Ok where to begin,

I’ve only started using Access 2010 since the beginning of January and have google’d almost every problem to date. This forum has been a gold mine of help but after days of searching I cannot find a solution for this problem .

So I’m hoping you guys can help.

Some background

My db is used to show the status of material through a manufacturing process.
We start with one slab of material which gets cut up in to many parts.

The db works so far however I’ve come to the conclusion that the date was not normalized correctly. So I’ve created the following tables to fix this.

I have two tables the first of which holds data on the slab we start with and the second shows the status of the parts it has been cut up into.


Table 1 has the following fields

Cast Number
P/O
UST Status
UST Comment

Table 2 has the following fields

Cast Number
Blade ID (which will be 1 to 32)
Status
Comment

I would like table one to be displayed on a form with all fields. Have table two as a subform on the main form. But here is where I get lost!

I need the subform to show 32 text boxes to represent each part. Have each box assigned a default blade ID (1 to 32). Then depending which of the 32 text boxes get used to update those multiple records within Table 2.

I did have 32 status fields for every part but realized that many were left null. Am I even going down the right road here?

I'm using, Access 2010 with Win 7


I hope that makes sense... I'd appreciate any and all help please

 

[1gixxer]

Any one?

Ive attached a mock db if it hepls.

 

[1gixxer]

Ok I’ve got it to ad 32 records for the blade ID'd field and display them in a subform in datasheet view.

But there may only be 10 blades. That’s 22 records that will never be used.

I need the subform in single form view with unbound text boxes to create a row in table 2 with its contents and the its label in the id field.

I’m beginning to think that this is not possible.

 

[shutzy]

what is you end goal. do you want to pull up a record and see what parts have been cut out of the slab?

if so. can this not be done on one table?

 

[thebatfink]

Hi. I'm not an expert by any means, but thought I would offer my thoughts on your situation. So I would suggest you see it purely as an opinion to kickstart some thinking, not a definitive answer!

In your Table2. Do you have 32 individual fields named BladeID1, BladeID2 etc etc? You have already stated that sometimes 1 slab does not directly translate into 32 blades and you have occurences of values as low as 10 already. Have you also considered if it is possible in the future you may get 33 blades from one slab?

If this is correct, it sounds to me like BladeID may be a repeating group. The first normal form says that your data should be atomic - one field contains only one value - and that there are no repeating groups in your tables.

Maybe it would be a solution to have your tables structured this way..

Table1.Table1ID - Primary key auto number
Table1.CastID - Force unique
Table1.P/O
Table1.USTStatus
Table1.USTComment

Table2.Table2ID - Primary key auto number
Table2.CastID - Foreign Key (Relationship Table1.CastID 1 to Many Table2.CastID)
Table2.BladeID
Table2.Status
Table2.Comment

So when you look at your data, rather than having 1 record in table2 for each record in table1, you now have a record for each individual blade which is related to a single record in table1. This way, if you get 10 blades, you have 10 records and only 10 records that all point back to the CastID. If you get 32, you would have 32.

You could then display the blade details relating to a Cast like this..

SELECT Table1.CastID, Table2.BladeID, Table2.Status, Table2.Comment
FROM Table1 INNER JOIN Table2 ON Table1.CastID=Table2.CastID
WHERE CastID=yourcastIDnumber