By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,282 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 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
Share this Question
Share on Google+
3 Replies


P: 34
This should work to get the value of column 1 where square feet is 1600:
Expand|Select|Wrap|Line Numbers
  1. sngvalue = dlookup("[1]","[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
  1. SELECT SELECT [que:Main].Shape, [que:Main].[Total Sq Footage]
  2. FROM [que:Main], [tbl:ShapeMultiplier], [tbl:ShapeMultiplier] AS [tbl:ShapeMultiplier_1]
  3. GROUP BY [que:Main].Shape, [que:Main].[Total Sq Footage];
  4.  
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
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. ID, Square Feet,    Shape, Multiplier Value
  2.  1,         400, Basement, 1.251
  3.  2,         400,        1, 1.137
etc


Dlookup in this example would be
Expand|Select|Wrap|Line Numbers
  1. 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
  1. SELECT SELECT [que:Main].Shape, [que:Main].[Total Sq Footage]
  2. FROM [que:Main], [tbl:ShapeMultiplier], [tbl:ShapeMultiplier] AS [tbl:ShapeMultiplier_1]
  3. GROUP BY [que:Main].Shape, [que:Main].[Total Sq Footage];
  4.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.