Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Find value based on multiple criteria

  1. #1
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9

    Find value based on multiple criteria

    Good day all

    I have two tables (Category and Vehicle Details). In Category table there are the following fields (Weight Class 1, Weight Class 2, Motor Vehicle, Motorcycle, Caravan, Trailer, Semi-Trailer, Tractor and Special Class) and in Vehicle Details table there is the following fields (Category, Weight and Licence Amount). The Category field in Vehicle Details is a lookup field with the different types of vehicles as highlighted above. In table Category the Weight Class 1 has the minimum weight and Weight Class 2 has the maximum value for each vehicle type and that determine what the value and currency will be for each vehicle class.
    Now my dilema is that I want the Licence Amount field in Vehicle Details table to auto lookup the correct currency in the Category table based on the Category and Weight fields in Vehicle Details table. Is it as simple as a query that could be run or is it more intricate like a vb code that is needed? This really frustrates the living daylight out of me.

    I hope someone could be of assistance.



    Thanks in advance.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Category table is not a normalized structure. If you normalize structure and save CategoryID into VehicleDetails, a simple query joining tables would be possible.

    Not clear to me exactly what data is saved in Category. Edit post to show sample tables.

    Advise not to use spaces nor punctuation/special characters in naming convention.

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    June beat me again, but I was thinking alone the same lines.


    Consider:

    tblVehicles
    -----------------
    VehicleID_PK (Autonumber)
    VehicleType (Text) (values of: Motor Vehicle, Motorcycle, Caravan, Trailer, Semi-Trailer, Tractor and Special Class)



    tblCategories
    -------------
    CategoryID_PK (Autonumber)
    VehicleID_FK (Number-Long) (Foreign key to tblVehicles )
    WeightClass_Min (Number-Double or Integer if weight is a whole number) (Minimum)
    WeightClass_Max (Number-Double or Integer if weight is a whole number) (Maximum)
    LicenseAmount (Currency) (amt per Category)



    tblVehicleDetails
    ----------------
    VehicleDetailsID_PK (Autonumber)
    CategoryID_FK (Number-Long) Foreign key to tblCategories
    VehicleWeight (Number-Double or Integer if weight is a whole number)
    LicenseAmount (Currency) (saved because of amount changes over time.)





    If you need/want a history of License Amounts over time, then you would need another table.
    Advise you to get rid of the Look-up FIELDS. See The Evils of Lookup Fields in Tables
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    Darryl,
    Please step back and describe in simple, plain terms what you are trying to achieve. Forget tables for the moment.
    You have various vehicle types that require licenses. Some criteria identifies the licensing cost/fee for vehicles depending on the their weight class or some combination of factors.
    Perhaps you could include a few examples showing vehicle, class/category and license fee in different currencies?
    Seems you are in physical design mode without some analysis, model and test scenarios.

  5. #5
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    Quote Originally Posted by orange View Post
    Darryl,
    Please step back and describe in simple, plain terms what you are trying to achieve. Forget tables for the moment.
    You have various vehicle types that require licenses. Some criteria identifies the licensing cost/fee for vehicles depending on the their weight class or some combination of factors.
    Perhaps you could include a few examples showing vehicle, class/category and license fee in different currencies?
    Seems you are in physical design mode without some analysis, model and test scenarios.
    Okay, so when I enter new vehicle weight into Vehicle_Details table, I need the licence fee amount to be looked up based on the vehicle type and the weight class from the Category table and auto inserted into the Vehicle_Details table.

    @ssanfu
    I've taken your structure in consideration and tried it but each vehicle type does not have the same licence amount in the same weight category, thats why I need each vehicle type to be a field with its own licence amount. Each vehicle type's licence fee is determined by the weight, just like @orange mentioned above. Oh and @orange there is only 1 currency.

    @June7 Please see the demo file below.


    Demo.zip

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    In normalized structure, each type/weight pair would be a record. How does that not meet your requirement?

    Your OP says you need to look up 'correct currency'. What do you mean by 'currency' if there is only 1 currency involved?

    I cannot open your db with Access 2010, it is using some feature that requires a later version of Access.

    So what exactly is saved into Category field - text values like "Motor Vehicle", "Motorcycle", etc that match field names in Category table?

    If yes, then:

    DLookup("[" & [Category] & "]", "Category", [Weight] & " BETWEEN [Weight Class 1] AND [Weight Class 2]")

    That expression can be in query or textbox on form or report.

    Advise table name should be Categories to reduce confusion with field name Category. It is common practice to use plural for table names.
    Last edited by June7; 10-18-2019 at 03:46 PM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,288
    Darryl,
    You need to identify things in business terms. You are too focused on tables. A description will allow readers to assist you with Normalization --which is critical to database design. I would also guess that license fees change with time, so some allowance for such rates would seem necessary.
    In your data it appears that some vehicle types have the same license fee regardless of weight. This indicates some business fact(s) has not been stated clearly in your post.
    Since we don't know the details of your "business", we can only guess at the possible table designs.

    Your table Category seems to be a mixture of several things.
    tblVehicles would be better named as tblVehicleTypes based on the values in your table.
    Vehicle_Details also seems to be a mixture of things (hidden entities possible). It is unclear why Vehicle_Details would have a composite primary key???

    I suggest you write a description of "a day in my business" or similar so readers and you get a clear picture of what you are trying to support with a database. You are into physical database without a clear requirement in my view.
    Good luck with your project.

  8. #8
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    orange
    My business idea is to do the following:
    1. Create a database for Clients with vehicles
    2. Based on the expiry date of each vehicle the following must happen:
    2.1 An email must be autogenerated with the application form already completed as an attachment.
    2.2 The first email will be sent on the first eligible date when licence can be done. That is 2 months from the expiry date.
    2.3 The second email with the same attachment at the 1st day of the month the licence expiry and on the day the licence expires, and on last email 15 days after the licence expires. All with the same attachment.
    2.4 The application form will have to include the amount payable for that specific vehicle.
    3. The client will have the option to let me do the actual licence renewal on their behalve.

    All of the above I have solved except 2.4.
    W.r.t the licence fees. That is the current rate which the government actually charges at the moment. I do not know when in the future it will change and with how much but the licencing department should have some type of clue as to when and propebly how much.

    I hope this will give you some insight.

    Thanks again for your eagerness to help.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Did you see post #6?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    Hi June7
    I only saw it after I replied to orange. I am working through your suggestion.
    I am working with Access 2016. I did add Switchboard to the ribbon, thats maybe the reason why you cannot open the file. Other than that, I have no clue.

    Thankd again for the suggestion/solution. I will post if there is any progress.

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    I should be able to open database that uses Switchboard. Still can't load db even after adding Switchboard Manager to ribbon.

    I don't recommend Switchboard. There is reason MS deprecated the tool. It's terrible.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,436
    The attached file named "DemoSS_2010.zip" is the 2016 dB converted to 2010 format.


    The attached dB named "DemoSS2010 MOD.zip" (2010 format) is the dB modified with a couple of forms to demonstrate (one method) what I think the OP is trying to do.
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    Hi ssanfu

    Your vision is on par with what I need. So what I am gonna try is to remove the LicenceAmount fields from the tables and create that in a query. This way a new table is created with the LicenceAmount, the way June opted in #6. That should work right?

  14. #14
    Darryl001 is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2019
    Posts
    9
    Hi Guys

    I'm getting an #Error when I use a query to create the Licence_Amount field with the following formula:
    Licence_Amount: DLookUp("[" & [VehicleType] & "]","tblVehicles",[Weight] & " BETWEEN[WeightClass_Min] AND [WeightClass_Max]"),

    I used DemoSS in the following query:
    from tblVehicles: VehicleType
    from Vehicle_Details: Licence_Number, Register, Weight
    from tblCategories: WeightClass_Min, WeightClass_Max

    Can you tell me where I'm going wrong with this formula or am I missing a field or something.
    So close, almost there, I can feel it. Just a little push and we are there.

  15. #15
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,224
    Steve's MOD version of your db has normalized tblCategories we advocated. Wait, both db have this normalized table.

    I would name tblVehicles to tblVehicleTypes, VehicleID_PK to VehicleTypeID_PK, and field VehicleID_FK in tblCategories to VehicleTypeID_FK.

    Then have a combobox on frmVehicleDetails to select vehicle type/weight class record from tblCategories. This would save CategoryID_PK as foreign key into VehicleDetails.

    Then would not need DLookup expression at all.

    CategoryID_FK field in VehicleDetails is a number type, not text and does not store values that coincide with Category table field names.
    With your original Category table, as I described, this is necessary for the DLookup to work.
    Why is there VehicleID field in Category?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-18-2016, 08:31 PM
  2. find values based on text criteria
    By boboivan in forum Access
    Replies: 2
    Last Post: 12-09-2015, 05:37 AM
  3. countiff based on multiple criteria
    By ritimajain in forum Access
    Replies: 8
    Last Post: 07-25-2013, 11:37 PM
  4. Matching based on criteria from multiple tables
    By Jonpro03 in forum Database Design
    Replies: 13
    Last Post: 08-04-2011, 10:29 AM
  5. Find existing record with multiple criteria
    By TheWedgie in forum Forms
    Replies: 1
    Last Post: 03-25-2009, 10:24 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums