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