Answered by:
Select field from Query based on Criteria in query
Question

Ok, This is for a repair DB. I Have a couple tables linked by model number. Each model number has 3 repair classes (a, b, c). Each Repair class for each unit has its own price
Right now on my main units table i have a model field, repair class field, and an estimate field. The model field has a direct relationship to a model_number table.
The model_number table contains all models and versions in a field along with their corresponding A_class, B_class, and C_class price.
When I open my Quote form, (model number is already input to table) and select repair class, I want to input the corresponding A_class, B_class, or C_class value of the modelnumber table into the estimate textbox that is bound to the estimate field on my main units table
I have a query that shows Unit ID(main units table), Model Number(main units table), Repair Class(main units table), A_class(model# Table), B_class(model# table), C_class(model# table)
So if Unit 213 is a model# "5510" with a repair class "A", the estimate field = that value from the model table.
I can't imagine that this is too complicated, but I've been stuck on this for a couple days. I sincerely appreciate any help on this. Thanks!
Answers

So you are saying that I should have one table with all models,
Then have another table for each model, with 3 records, one for each class. 2 fields, class and price. So the class is the primary key linked back to its model in the modelnumber table.
Am I understanding this correctly?
No. You should have four tables, one with one row per class, one with one row per model, one with one row per repair, and one (the estimates table) with three rows per model. So if the models table has 50 rows, the estimates table would have 150 rows. So the tables would, in broad outline, be like this:
Models
….ModelID (PK)
….Model
….etc
Repairs
….RepairID (PK)
….ModelID (FK)
….RepairClass (FK)
….Estimate
….etc
RepairClasses
….RepairClass (PK)
Estimates
….ModelID (FK)
….RepairClass (FK)
….Estimate
The primary key of the last table is a composite one of the two foreign keys, ModelID and RepairClass.
Note that both Estimates and Repairs have an Estimate column. You cannot simply reference the Estimate column in Estimates as (a) its values will change over time, and (b) you might wish to manually adjust the estimate for a repair. The value is therefore assigned to the Estimate column in Repairs when a row is inserted into that table. This is done by means of code in the AfterUpdate event procedures of both the ModelID and RepairClass controls in a data entry form:
Dim strCriteria As String
strCriteria = "ModelID = " & Me.ModelID & " And RepairClass = """ & Me.RepairClass & """"
If Not IsNull(Me.ModelID) And Not IsNull(Me.RepairClass)
Me.Estimate = DLookup("Estimate", "Estimates", strCriteria)
Else
Me.Estimate = 0
End If
Ken Sheridan, Stafford, England
 Edited by Ken Sheridan Saturday, March 5, 2016 12:14 PM Typo corrected.
 Proposed as answer by Edward8520Microsoft contingent staff Wednesday, March 9, 2016 9:50 AM
 Marked as answer by JoeCamel9166 Wednesday, March 9, 2016 1:05 PM
All replies




Sorry I don't know how to attach.
Essentially I am looking for this
If me.Repair_Class = "A" Then
Me.Estimate = tblModelID.ClassA
End If
If me.Repair_Class = "B" Then
Me.Estimate = tblModelID.ClassB
End If
If me.Repair_Class = "C" Then
Me.Estimate = tblModelID.ClassC
End IF
I already have the record selected, I just need to pull out the value of that field.
Am I going about this the wrong way?
I am doing this project for work, and am learning all this in the process.
Thanks

Each class estimate should be represented by a separate row in a related table, not a separate column in the tblModelID table. Each row in each subset of three rows would include the same ModelID foreign ID but a different Class. The ModelID and Class columns would thus be the composite primary key of the table. The estimate for a model/class combination can then be easily obtained.
Ken Sheridan, Stafford, England

So you are saying that I should have one table with all models,
Then have another table for each model, with 3 records, one for each class. 2 fields, class and price. So the class is the primary key linked back to its model in the modelnumber table.
Am I understanding this correctly?

So you are saying that I should have one table with all models,
Then have another table for each model, with 3 records, one for each class. 2 fields, class and price. So the class is the primary key linked back to its model in the modelnumber table.
Am I understanding this correctly?
No. You should have four tables, one with one row per class, one with one row per model, one with one row per repair, and one (the estimates table) with three rows per model. So if the models table has 50 rows, the estimates table would have 150 rows. So the tables would, in broad outline, be like this:
Models
….ModelID (PK)
….Model
….etc
Repairs
….RepairID (PK)
….ModelID (FK)
….RepairClass (FK)
….Estimate
….etc
RepairClasses
….RepairClass (PK)
Estimates
….ModelID (FK)
….RepairClass (FK)
….Estimate
The primary key of the last table is a composite one of the two foreign keys, ModelID and RepairClass.
Note that both Estimates and Repairs have an Estimate column. You cannot simply reference the Estimate column in Estimates as (a) its values will change over time, and (b) you might wish to manually adjust the estimate for a repair. The value is therefore assigned to the Estimate column in Repairs when a row is inserted into that table. This is done by means of code in the AfterUpdate event procedures of both the ModelID and RepairClass controls in a data entry form:
Dim strCriteria As String
strCriteria = "ModelID = " & Me.ModelID & " And RepairClass = """ & Me.RepairClass & """"
If Not IsNull(Me.ModelID) And Not IsNull(Me.RepairClass)
Me.Estimate = DLookup("Estimate", "Estimates", strCriteria)
Else
Me.Estimate = 0
End If
Ken Sheridan, Stafford, England
 Edited by Ken Sheridan Saturday, March 5, 2016 12:14 PM Typo corrected.
 Proposed as answer by Edward8520Microsoft contingent staff Wednesday, March 9, 2016 9:50 AM
 Marked as answer by JoeCamel9166 Wednesday, March 9, 2016 1:05 PM