435,560 Members | 2,989 Online + Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,560 IT Pros & Developers. It's quick & easy.

Need cross reference from table for query

 P: 20 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:ShapeMultiplier, 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:ShapeMultiplier How? Or what is the easiest way for a query to lookup the multiplier required given the square footage and the complexity indicator? Feb 13 '08 #1
3 Replies

 P: 34 This should work to get the value of column 1 where square feet is 1600: Expand|Select|Wrap|Line Numbers sngvalue = dlookup("","[tbl:ShapeMultiplier]", "[Square Feet] = 1600") it is placing the value in a variable call sngvalue... Feb 13 '08 #2

 P: 20 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:ShapeMultiplier Expand|Select|Wrap|Line Numbers 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..... Feb 14 '08 #3

 P: 34 my example was for VBA code sorry, here is it's usage in a query (paste in the Query builder in the 'field' row) Expand|Select|Wrap|Line Numbers Multiplier: DLookUp("","[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: Expand|Select|Wrap|Line Numbers ID, Square Feet,    Shape, Multiplier Value  1,         400, Basement, 1.251  2,         400,        1, 1.137 etc Dlookup in this example would be Expand|Select|Wrap|Line Numbers 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]!OtherControlName 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:ShapeMultiplier Expand|Select|Wrap|Line Numbers 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..... Feb 14 '08 #4 