Populating rows with set values

[Kozbot]

Hello

I have a Machine Running Conditions database that records running conditions for 2 machines. The table is set up like this

LotNumber       Parameter       Condition
130506m11    Screwspeed         100
130506m11    StockTemp           50
130506m11     KniveSpeed          75

In reality there are many more machine parameters (about 30). We have two machines that usually are set with 2 different set combinations of parameters. The combinations vary at times but are usually one or the other

Currently the user is going in and selecting the parameters from a combobox manually for each lot number.

To save him time and work, I want those fields auto populated with the parameters for a machine. So the user will hit a button for machine 1 or 2 and the parameter rows will fill with that machine's correct parameters.

Anyone point me in the right direction? Thank you

 

[MarkK]

Create a table called MachineParameters that stores an authoritative list of parameters that belong to a particular machine. Presumably you already have a table called Machine. This new table will have a many-to-one relationship with the Machine table.

 

[Kozbot]

Create a table called MachineParameters that stores an authoritative list of parameters that belong to a particular machine. Presumably you already have a table called Machine. This new table will have a many-to-one relationship with the Machine table.

I already have it set up this way

My issue is when the user goes to enter the values for those parameters for a particular lot number. He is entering the lot number and manually selecting the parameter from a combobox that is fed from the machine parameter table then entering the value for that parameter in a different table.

So when he is entering a lot number's running conditions. He must go in and manually select 20 or so parameters from that machine parameters table. I'm wondering if the I can auto populate 20 rows in that field automatically.

 

[MarkK]

I'd expect that the table the user fills data into should have a LotNumber, MachineParameterID, and Condition field. Is that right? I'd expect the MachineParameterID field to have a combobox on it that allows the user to select the parameter, which is drawn from the MachineParameters table for the given machine. Probably the LotNumber is associated with a specific machine with a fixed set of parameters.

If you want, you can insert multiple rows with the given LotNumber and the list of MachineParameterIDs that should occur for that machine. Is that what you want to do?

INSERT INTO tblMachineParameterCondition
( LotNumber, MachineParameterID )
SELECT [prmLotNumber], MachineParameterID
FROM tblMachineParameter
WHERE MachineID = [prmMachineID]

Does that make sense? Run that as a querydef that you can programmatically supply with parameters?

 

[Kozbot]

I'd expect that the table the user fills data into should have a LotNumber, MachineParameterID, and Condition field. Is that right? I'd expect the MachineParameterID field to have a combobox on it that allows the user to select the parameter, which is drawn from the MachineParameters table for the given machine. Probably the LotNumber is associated with a specific machine with a fixed set of parameters.

If you want, you can insert multiple rows with the given LotNumber and the list of MachineParameterIDs that should occur for that machine. Is that what you want to do?



Does that make sense? Run that as a querydef that you can programmatically supply with parameters?

This how my relevant tables are set up
http://i.imgur.com/j5Ot02L.png?1
Ignore the SetRunCond table

But see how the actual running conditions table is related to the parameters table. There are two different combinations of parameters i want to auto populate new records for that field in the Actual Running Conditions table

Essentially I want to create 20 or so new records with the same product code and lot number but those different parameters that I mentioned. that way the user only has to go in and enter the values (ActualCond)

 

[MarkK]

Yeah, so your tables:

It doesn't make sense to me to have a table with just one field. The power of a database system comes from being able to relate data to objects in One-To-Many relationships. Your table MachineParameters should be related to a machine, since presumably different machines have different parameters. With this current Parameters table, how do you select only those parameters you want to add for a particular machine??? I would expect a table called MachineParameters to clarify which machine a parameter belongs to.

tblLotNo2. The LotNumber is data, not structure. If you have a tables like tblLot1, tblLot2, tblLot3, with exactly the same structure, then you have a table design problem. LotNumber should be a field in the Lot table and rows that belong to Lot2 should have the value 2 in the LotNumber field. Then you isolate rows for that lot using a where clause like . . .

SELECT *
FROM Lot
WHERE LotNumber = 2

 

[Kozbot]

Yeah, so your tables:

It doesn't make sense to me to have a table with just one field. The power of a database system comes from being able to relate data to objects in One-To-Many relationships. Your table MachineParameters should be related to a machine, since presumably different machines have different parameters. With this current Parameters table, how do you select only those parameters you want to add for a particular machine??? I would expect a table called MachineParameters to clarify which machine a parameter belongs to.

tblLotNo2. The LotNumber is data, not structure. If you have a tables like tblLot1, tblLot2, tblLot3, with exactly the same structure, then you have a table design problem. LotNumber should be a field in the Lot table and rows that belong to Lot2 should have the value 2 in the LotNumber field. Then you isolate rows for that lot using a where clause like . . .

SELECT *
FROM Lot
WHERE LotNumber = 2

What you are saying about my parameters table is making obvious sense to me now. Thank you

There is only one lot number table, I put a 2 in the name for some reason awhile ago