DLookup & calculation troubles

[benjee]

Hi - please find attached jpg of subform

Currently trying to create a subtotal calculating data from the fields shown in the image.

The values from the list boxes are obtained from two tables respectively. Each table contains three fields,

Description (displayed in the list box)
Primary Key ID
Cost

Is it possible to use a function to abstract the 'cost' from each table and use it in a equation with the 'dimensions' to form an estimation?

Many thanks BEN

 

[Rich]

Why two tables? The easiest way is to add the Cost field to the listbox and refer to it directly, =[MyList].[Column](1) where column 1 is the second column of your listbox

 

[benjee]

Rich,

ok, but can you create a list box that contains two columns i.e. cost and description?

...and shall i get rid of the tables?

B

 

[Rich]

Here's a simple example, it uses unbound controls in this case.
If you want to create quotes/estimate etc, then you'll have to store the length fields and the cost per metre at the time of the quote
HTH

 

[benjee]

Rich thats great help,

thankyou very much,

the estimate variables (thermalite bricks) can be stored easily enough can't they if i create a table for the value to be stored?

BEN

 

[Rich]

You would normally store the PK as the foreign key in say your quotes table, the Item details can be retrieved via a query for reporting purposes, dealing with the cost per metre is not so easy to deal with as it's subject to regular change, storing it with the quote is much easier

 

[benjee]

Hey Rich,

One last question ive created a quotes table, doing as you suggested using the PK as Foreign keys etc but am abit miffed on joining the tables in the relationship window.

As you can gather from the attachment, i have three joint tables, two tables containing the estimation variables and finally the quote table.

Is it correct in joining the tblBondStructure... & and the tblPointing to the estimate table (to the respective fields) as a one-to-many relationship? Or can these tables be 'standalone'.

And finally how does the tblQuote join up in the relationships?