Results 1 to 12 of 12
  1. #1
    tbcguytb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4

    Rate Lookup based on values of 3 fields

    Hi there, i am coming from a Filemaker background, not extensive but Filemaker nonetheless. I have decided to move a project I am working on to Access. I have a database that tracks a logging operation (forestry). Main focus on the application is tracking the contactors, loads hauled, skidded, cut, or processed. Designed basically as in invoice application with a forestry swing on it.



    I select the contractor and begin entering in this case, the load information in the line items table. The line items table is linked by a multi-predicate join to select the rate of pay. 3 fields determine the rate of pay. I Enter the info including the source, destination. The Class filed is a hidden calculated field that is created on creation of the new record set by and Contractor Pay Type field (master record). See picsClick image for larger version. 

Name:	Capture3.PNG 
Views:	34 
Size:	15.8 KB 
ID:	41496











    In my rates table i have fields (RateID, Block, Destination, Class, Unit_of_Pay and Rate). Rates are different for each class, being LOAD, CUT, Skid, Process, and Slash


    Click image for larger version. 

Name:	Capture2.PNG 
Views:	33 
Size:	16.6 KB 
ID:	41495










    So three conditions have to be met before the correct rate will be selected. Now to Access, I would like to have my rate auto-populate in my loads table on each record when entering but cannot seem to see how. I want to setup a calculated field that concatenates the three fields in question, and use that in a foreign key to the rates table and do the same in the rates table with a concatenated field with the three fields as a primary key. Is this possible. And I apologize for the long post.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Although possible, your idea for constructing a key field with concatenated values is frowned on by many developers. A compound primary key is also an option, but I avoid as much as possible and have used only once.

    Why don't you just save the RateID as key?

    You might find cascading comboboxes useful in a procedure to select desired rate record.
    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
    tbcguytb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Because if the user has to look at a list dropdown say, that list could become long And the user would have to know all the rates. At any given location, the contractor can perform all the jobs...cut,skid,delimbing, load and haul. Also they move blocks often..can become messy. At some point there will be many many rates... Far too many for anyone to remember. So I thought by entering the 3 key fields that make up the rate would help data entry

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Yes, use those three fields to select rate but RateID is what is saved as key to associate related records.

    Again, look into use of cascading comboboxes. A very common topic.
    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.

  5. #5
    Join Date
    Apr 2017
    Posts
    1,673
    When you had this app originally in Filemaker, then I see how you want to make such weird calculations (unless Filemaker has discovered subforms in last years - last time I looked into it, the only 2nd level possibility was to have an list item created).

    When you want to use Access instead, you better start from scratch. Access and filemaker are based on very different logic!

    It is difficult to give exact advice, as I feel some info is left out in your posting, and some info is not clear at all.
    1. Is contractor same as destination, or are they different animals at all? Are there table(s) for it/them?
    2. It looks like Source and Block are same in different tables. What it is? An area of forest where all this logging is done? Or is it something like article (seeing it must be what connects rows in 1st table in your post, and querytable/table displayed as 2nd there)? Is there a table where all sources/blocks are registered?
    3. What is Load? Is it something like a delivery at certain date? Is there a table where load is registered?
    4. Is the 1st table in your post a table of load details?
    5. What is Plate? Some transport unit (it looks like same plate may occur in different loads)? Is there a table where those plates are registered, or is it a part of some other characteristic?
    6. What is Bol? Again, is there a table where bol's are registered?
    7. How are Unit and UOP connected? How is UOP different for same block in 2nd querytable/table? And also sometimes different for same block and destination?
    8. Class looks like an operation made for load detail row. I.e. you have table for loads, then a table for load rows where different articles sent with load are described, and then you have a table for load row operations, where an unit price for every load row unit is determined. Is that what you have?
    9. Do you have a table where prices for different operations are registered?

  6. #6
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    So three conditions have to be met before the correct rate will be selected.
    These would appear to be destination>destination, Unit>UOP and Source>Block. But your rate table has duplicates on that basis 062-Barwick-Tonne for example - so are you also filtering by Class as well?

    As others have said, no evidence of a numeric primary key other than your mention of a composite one which will be a) extremely inefficient and b) at risk of relationship failure if one of the field values is changed

  7. #7
    tbcguytb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Contractor - the company that performs the work, be it Cutting, Skidding, Delimbing, Processing, Slashing and Hauling. (This is a bush operation). Just like a building construction site, this operation can have many contractors working.
    Block - Area in the forest, Gov.t has subdivided the forest into smaller areas. Each area is a Block for us. So from above, block 52B and 062-G could be 100 miles apart, therefore producing different hauling rates based on the distance to the Mill it is delivering it to
    Load - when you see a log truck carrying logs...that is a load of wood
    Destination - the Mill that the Load is being delivered to.
    Plate - Licence Plate on the Truck (applies to hauling only) - we need to identify which Contractor transported the Load to the mill.. contractors can have 10 trucks..therefore 10 Plates
    BOL - this is the Ticket Number on the Bill of Lading that the Mill produces when the Load is delivered.
    Unit / UOP are the same - Loads are measured in either Metric Tonnes M³ or by weight Tonne (there is is difference)
    Class - set up to determine the type of work this rate applies to.

    So my rate table can and will have many rates. ie, Block 052B. there are two different hauling rates because block 052B is about an hour from the Resolute Mill and 3 Hours from the Barwick mill. So hopefully I have explained the operation clearly enough. Also all my tables in filemaker have ID's and yes in the rate table too! I found it easier to get my rate by using a calculated field and use that in a join, albeit against the grain in Access. I found using a cascading list was just a bunch of dropdowns...to many on a big sheet. A dropdown for source(Block) one for Destination and I have the Class field hidden because I have set up the Contractor Recap (Invoice) so that when I create a newone , I select the class on the master record, then populate each record with that class...again trying to speed things up.


  8. #8
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'm going to drop out because I cannot see the point of a composite index when all you need is a simple query along the lines

    Code:
    Select Rate
    FROM tblRates
    WHERE destination=[txtdestination] AND UOP=[txtUnit] AND Block=[txtSource] AND Class='Haul'


    good luck with your project. Easter is here and I have other things I need to focus on

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    To directly answer your question: a primary key cannot be a Calculated type field in table. A compound key is possible but advice is to not do that if at all possible.

    How could 3 cascading comboboxes be too many? It is true they don't work nicely on form set as continuous or datasheet.
    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
    tbcguytb is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Hi everyone, "ok i get it!" I can't use concatenated fields in a join or composite keys...i did right from the beginning. I was merely explaining that is how i did it in filemaker. I see how both platforms are completely different. I used the above sql suggested from Ajax before he bailed on this. I'm not offended Ajax. Now i am just trying to get it to autofill the parameters so the parameter box doesn't run on every field in the query. Basically fill the parameters with the values in the three fields in question.

    Thanks again.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    It is possible to join on a calculated field, just cannot assign a calculated field in table as primary key.

    If you want a search/filter functionality with 3 comboboxes to select descriptive parameters - review http://allenbrowne.com/ser-62.html

    Use cascading code if you want each successive combobox list reduced by selection in previous.
    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
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    my point was, user selects source, destination, unit and code from a simple combo of each of those options then runs the query to populate rate - could use the sql or a dlookup. No need for cascading combo's. If the combination does not already exist in the rates table, nothing will be returned

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 06-02-2017, 03:49 PM
  2. Variable rate based upon a month
    By Lluewhyn in forum Queries
    Replies: 6
    Last Post: 07-07-2016, 02:26 PM
  3. Replies: 4
    Last Post: 07-28-2015, 10:14 AM
  4. Replies: 1
    Last Post: 05-12-2015, 02:34 PM
  5. Replies: 5
    Last Post: 06-14-2012, 08:30 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
  •  
Other Forums: Microsoft Office Forums