Need cross reference from table for query

[bruce24444]

I'm trying to create a database for calculating the approximate replacement cost of a house.

In the form I have, you input the square footage of each floor and the complexity of the exterior wall shaping, which is recorded in tbl:Main.
I then have a query which adds all of the floors square footage to obtain a “Total square footage”.

I have another table , tbl:ShapeMultip lier, which has predefined variable which take into consideration the square footage and the complexity of the exterior walls. With the information this provides, you multiply the square footage by this multiplier to produce a value.

View of tbl:ShapeMultip lier

How? Or what is the easiest way for a query to lookup the multiplier required given the square footage and the complexity indicator?

[Tetelestai]

This should work to get the value of column 1 where square feet is 1600:

sngvalue = dlookup("[1]","[tbl:ShapeMultiplier]", "[Square Feet] = 1600")

it is placing the value in a variable call sngvalue...

[bruce24444]

I'm not sure how your suggestion is to be applied.

I have a second query which gathers the information from que:Main and only returns the applicable shape multiplier and the total square footage, which sometimes doesn't specifically apply to tbl:ShapeMultip lier

SELECT SELECT [que:Main].Shape, [que:Main].[Total Sq Footage]
FROM [que:Main], [tbl:ShapeMultiplier], [tbl:ShapeMultiplier] AS [tbl:ShapeMultiplier_1]
GROUP BY [que:Main].Shape, [que:Main].[Total Sq Footage];

The third column is the variable that I want to retrieve from the table.

Query Results from my present test example

Hope this makes sense.....

[Tetelestai]

my example was for VBA code sorry, here is it's usage in a query (paste in the Query builder in the 'field' row)

Multiplier: DLookUp("[1]","[tbl:ShapeMultiplier]","[Square Feet] = 1600")

As I look deeper you may want to restructure your shapemultipler table. Read Normalization

I would suggest these columns

ID (Auto#), Square Feet (text), Shape {or what you consider your columns in the previous table} (as text), Multiplier value (Number:Single)

data in the table example:

ID, Square Feet,    Shape, Multiplier Value
 1,         400, Basement, 1.251
 2,         400,        1, 1.137

etc


Dlookup in this example would be

Multiplier: DLookUp("[Multiplier Value]","[tbl:ShapeMultiplier2]","[Square Feet] =  1600 AND Shape = 1")

You can have dlookup look at Form controls as well: "[Square Feet] > " & Forms![FormName]!ControlName & " AND Shape = " & Forms![FormName]!OtherControlNa me

note the greater than symbol. Its looking for a value greater then the control value. You could use it in the other example as well

Hope this helps,

I'm not sure how your suggestion is to be applied.

I have a second query which gathers the information from que:Main and only returns the applicable shape multiplier and the total square footage, which sometimes doesn't specifically apply to tbl:ShapeMultip lier

SELECT SELECT [que:Main].Shape, [que:Main].[Total Sq Footage]
FROM [que:Main], [tbl:ShapeMultiplier], [tbl:ShapeMultiplier] AS [tbl:ShapeMultiplier_1]
GROUP BY [que:Main].Shape, [que:Main].[Total Sq Footage];

The third column is the variable that I want to retrieve from the table.

Query Results from my present test example

Hope this makes sense.....