none
Select field from Query based on Criteria in query RRS feed

  • 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!

    Friday, March 4, 2016 12:48 AM

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


    Saturday, March 5, 2016 12:14 PM

All replies

  • Post the actual table structures -- table name, field1 name-datatype, field2 name-datatype, ...     for all your tables.

    Post sample data (no need for actual data) of each.

    Post the query SQL your is using to interface the tables.


    Build a little, test a little

    Friday, March 4, 2016 2:00 AM
  • https://www.dropbox.com/s/l64t9qxq3sb2xhr/www.dropbox.com.url?dl=0

    Here is a link to my DB.

    Any Input would be helpful.

    Friday, March 4, 2016 4:45 PM
  • That link requires a login and I do not want to open an account.

    Build a little, test a little

    Friday, March 4, 2016 5:30 PM
  • 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

    Friday, March 4, 2016 6:47 PM
  • 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

    Friday, March 4, 2016 6:59 PM
  • 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?

    Saturday, March 5, 2016 3:10 AM
  • 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


    Saturday, March 5, 2016 12:14 PM