Results 1 to 13 of 13
  1. #1
    dbhokie is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    12

    Using lookups to complete equations, should I be using queries instead?

    So I am making a new database with a few forms and tables.



    So far a JOB, Customers, Materials, and Salesperson Table

    The Job table keeps track of each individual job as a record, so it has fields that it looks up from other tables as well as specific entry fields:
    JOB Table
    Job Name
    Salesperson (Lookup from Salesperson name field on Salesperson table)
    Material 1 (Lookup from Material SKU field on Material Table)
    Material 2
    Length 1 (in inches)
    Length 2
    Cost 1 - Calculated
    Cost 2 - Calculated

    Customers Table
    Customer Name
    Customer Number
    Phone Number etc etc

    Salesperson Table
    Salesperson Number
    Salesperson name

    Materials Table
    Description
    SKU
    Roll Price
    Width
    Length
    Length Unit
    Width Unit (EG: ft, yards, meters, inches)
    Roll Square Footage
    Cost per Square Foot


    So I have all of this information in my tables and basically am creating a form off of the Jobs table. In the Jobs table the Material1, Material 2 are lookups to the SKU field of the materials table, thus picking from available substrates. However when this is picked, I want a field to simply lookup the corresponding cost per square ft for that SKU (Material1, or Material2) 1to1 relationship and multiply it times a field the user will enter of Length used 1, 2 etc. Which will then get the user what the substrate used in a particular job will cost. I am trying to do it with DLOOKUP and failing, I thought if
    Material 1 and 2 etc are lookups of the SKU field on the material table.

    I could basically take (Material 1 chosen) ([Length 1] * [Width] * 0.0069444 )*[Cost per Square Foot] = Cost1 (Line item Material Cost)

    I can't figure out how to get Access to take the drop down choice in material 1 and choose the corresponding cost and width from it on the material table

  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,819
    Include the cost/length/width fields in combobox RowSource. Reference those columns in calculation. No DLookup and no query.

    When you build a report, use a query that joins tables to retrieve related data. The Materials table would have to be included twice, joining to each of the material fields in Job table.

    Limited to only 2 materials?
    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
    dbhokie is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    12
    Quote Originally Posted by June7 View Post
    Include the cost/length/width fields in combobox RowSource. Reference those columns in calculation. No DLookup and no query.

    When you build a report, use a query that joins tables to retrieve related data. The Materials table would have to be included twice, joining to each of the material fields in Job table.

    Limited to only 2 materials?

    There will actually be 8 material lines, I just shortened it for the sake of example. However, I already had used those fields in the drop down combo box. How do I reference the columns in an equation?

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Jou need a jobs table - tblJobs: JobID, JobName, SalesPersonNumber, [Quantity] (The field Quantity is needed, when the job is to do quantity of something),
    and jobs materials table (tblJobMaterials: JobMaterialsID, JobID, MaterialID, [Length] (The field Length is needed, when this is not read from materials table. When for job a quantity is given, then materials here are for one unit of job.).

    And it would be better to have a field MaterialID (autonumeric) in materials table also.

    With this design you can have any number of different materials for job.

    Needed quantities of materials, costs, etc. are calculated in query, which is then used as source for report. No need to save such info in tables.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Agree with Arvil - need to normalize the Jobs data with a dependent related table for JobMaterials.

    Reference combobox columns by index. Index begins with 0 so column 2 is index 1.

    =comboboxname.Column(1) * comboboxname.Column(2) * 0.0069444
    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.

  6. #6
    dbhokie is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    12
    Quote Originally Posted by ArviLaanemets View Post
    Jou need a jobs table - tblJobs: JobID, JobName, SalesPersonNumber, [Quantity] (The field Quantity is needed, when the job is to do quantity of something),
    and jobs materials table (tblJobMaterials: JobMaterialsID, JobID, MaterialID, [Length] (The field Length is needed, when this is not read from materials table. When for job a quantity is given, then materials here are for one unit of job.).

    And it would be better to have a field MaterialID (autonumeric) in materials table also.

    With this design you can have any number of different materials for job.

    Needed quantities of materials, costs, etc. are calculated in query, which is then used as source for report. No need to save such info in tables.
    First off you are all so kind to share your knowledge.

    I already have a jobs table, and a material table. (The Length Used essentially is quantity) This is material that is in rolls of inches, feet, yard, meters etc, so when they enter in length used in inches, in order to get the cost of the "part" the length used has to be multiplied times the width, then converted to square feet, and multiplied times the cost per square feet (or 3 other different ways but you get it), which returns back the cost for that particular 12 inches of material or 132 inches etc. There is an ID field in the materials table that is autonumbered already, the key id field.

    Basically on a form a user will enter in what they are using and then go to the next job or save it, then that job will be saved with the relevant costs all summed/labor etc for that particular job.

    I think I see what you are saying though, you have those tables, no need to save cost in the tables, that will come at request through a report built from queries, multiplied out by length used on each item (quantity).

    I was trying to reference columnar data in a table (which it does not allow you to do) and save that data, instead of having the idea of a query for it when needed.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Jobs and Materials have a many-to-many relationship. M-to-M usually requires a third 'junction' table to associate data. It is the junction table JobMaterials we suggest you need.

    Review https://support.airtable.com/hc/en-u...-relationships
    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.

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by dbhokie View Post
    I already have a jobs table, ...
    Which contains fields which must not be in tables at all (e.g.sums), and fields which really are working better in another table (materials).

    Quote Originally Posted by dbhokie View Post
    ... and a material table.
    No problems with it, except that when you have Description as primary key for this table, then this is not good idea too. Add an autonumeric primary key, and all queries where materials table is involwed will work much faster.

    Quote Originally Posted by dbhokie View Post
    I was trying to reference columnar data in a table (which it does not allow you to do) and save that data, instead of having the idea of a query for it when needed.
    You are trying to force Access to operate like Excel. It is like you have a dog and you try to make him to lay eggs!

  9. #9
    dbhokie is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    12
    Ok, I am not trying to be a bother, so I took a couple days and tried to get a better understanding before coming back here so woefully unprepared (hopefully)

    Click image for larger version. 

Name:	vinyljobrelationships.jpg 
Views:	23 
Size:	77.6 KB 
ID:	38306
    These are my tables and relationships now. I see what you mean by many to many relationships

    So I made it this way and then was coming up with an entry form at which point, I based my form off of my jobs table:

    Click image for larger version. 

Name:	JobForm.jpg 
Views:	24 
Size:	105.4 KB 
ID:	38308

    Then I made a query to get everything I needed with the Materials:
    Click image for larger version. 

Name:	qryJob_Materials.jpg 
Views:	24 
Size:	122.2 KB 
ID:	38309

    After which, I made a subform from the query:
    Click image for larger version. 

Name:	qrySubform.jpg 
Views:	24 
Size:	77.8 KB 
ID:	38310

    Which gave me a final product very close to what I want, I was hoping for a person to be able to enter in new line items of materials used in the subform on the jobs form:

    Click image for larger version. 

Name:	entryform.jpg 
Views:	23 
Size:	68.6 KB 
ID:	38311
    Yet it won't allow any new entery here, or on my query?

    What am I missing?

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Cannot edit an aggregate (GROUP BY) query. Aggregate queries are for report output, not data entry.

    A form should do data entry/edit for 1 table. Review
    https://access-programmers.com/main-...bform-concepts
    https://support.office.com/en-us/art...a-1e74d6f5f06b

    This is basic Access functionality. Have you completed an introductory tutorial book?
    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.

  11. #11
    Join Date
    Apr 2017
    Posts
    1,673
    What is calculated in field SubTotal?

    When this is cost of one material used for job, then use an unbound text box to calculate and display it. Calculating is done using domain aggregate functions [DSum(), DCount()]. And of-course remove GROUP BY from subform query - you can never edit query which has Group By in it - or even e.g. in saved query used as one of sources for your query. This restriction applies absolutely - it does not depend are you editing any field from such source or not.

  12. #12
    dbhokie is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    12
    Quote Originally Posted by ArviLaanemets View Post
    What is calculated in field SubTotal?

    When this is cost of one material used for job, then use an unbound text box to calculate and display it. Calculating is done using domain aggregate functions [DSum(), DCount()]. And of-course remove GROUP BY from subform query - you can never edit query which has Group By in it - or even e.g. in saved query used as one of sources for your query. This restriction applies absolutely - it does not depend are you editing any field from such source or not.

    I apologize to both of you. I am generally doing network engineering and working on servers, I am doing my best, and I have tried to read up and do multiple tutorials. The group by was an oversight, I didn't even mean to have it turned on. Obviously after turning it off everything worked perfectly. I am learning.

    I was using the calculation in the query in order to get the cost per square ft, funny that you mention it. That is a problem I actually ran into today, (not a problem persay, but I am trying to think of the future) I realized in it's current structure if I change a price in my materials table, it will mess up the historical record of the materials.

    I believe the correct option is to add a "roll cost" field to the junction table between materials and jobs, and store the historical cost of the roll and use an unbound box on the subform to calculate it based off of this field in the junction table.

    Now the database functions as I want it to, and I need to make this change so that the materials pricing table can be updated without affect on historical jobs.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,819
    Yes, saving the cost is probably simplest method to assure history is not altered. Requires code (macro or VBA) to save.

    Alternative method is to create a new product records when the costs change and to set the older records as "Inactive" and exclude them from comboboxes by filtering. But this approach definitely has its own issues.
    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.

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

Similar Threads

  1. > Solving N Equations In N Unknowns
    By Milade8080 in forum Forms
    Replies: 4
    Last Post: 03-09-2014, 06:08 AM
  2. Lookups, queries, and combo boxes, oh my
    By MGustin in forum Access
    Replies: 2
    Last Post: 12-04-2013, 12:46 AM
  3. Replies: 1
    Last Post: 07-23-2012, 11:29 AM
  4. Replies: 7
    Last Post: 09-06-2011, 08:00 PM
  5. Editing equations with VBA
    By Alexandre Cote in forum Programming
    Replies: 0
    Last Post: 07-15-2010, 11:13 AM

Tags for this Thread

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