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

1. Novice
Windows 10 Access 2016
Join Date
Apr 2019
Posts
5

## 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. 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?

3. Novice
Windows 10 Access 2016
Join Date
Apr 2019
Posts
5
Originally Posted by June7
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. VIP
Windows 8 Access 2016
Join Date
Apr 2017
Posts
1,133
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. 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

6. Novice
Windows 10 Access 2016
Join Date
Apr 2019
Posts
5
Originally Posted by ArviLaanemets
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. 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

8. VIP
Windows 8 Access 2016
Join Date
Apr 2017
Posts
1,133
Originally Posted by dbhokie
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).

Originally Posted by dbhokie
... 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.

Originally Posted by dbhokie
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. Novice
Windows 10 Access 2016
Join Date
Apr 2019
Posts
5
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)

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:

Then I made a query to get everything I needed with the Materials:

After which, I made a subform from the query:

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:

Yet it won't allow any new entery here, or on my query?

What am I missing?

10. 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?

11. VIP
Windows 8 Access 2016
Join Date
Apr 2017
Posts
1,133
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. Novice
Windows 10 Access 2016
Join Date
Apr 2019
Posts
5
Originally Posted by ArviLaanemets
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. 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.