473,320 Members | 2,041 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Need cross reference from table for query

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 2147
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
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
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

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

Similar topics

2
by: News | last post by:
Folks, I need help with this task. I have a set of data that needs to be plotted on timeline chart. Example: Unit ProcStart ProcEnd Machine U1 5/5/03 6:01 5/5/03 6:04 M1 U2 ...
3
by: Prem | last post by:
Hi, I am having many problems with inner join. my first problem is : 1) I want to know the precedance while evaluating query with multiple joins. eg. select Employees.FirstName,...
4
by: David Peach | last post by:
Hello, hope somebody here can help me... I have a query that lists defects recorded in a user defined date range. That query is then used as the source for a Cross Tab query that cross-tabs count...
3
by: amanda | last post by:
Hope someone can help me with this - I've been staring at it stupidly for hours now, convinced there must be an easy way to achieve the results I want: I have a very large table recording every...
3
by: maffonso | last post by:
Hi guys, I have built a cross reference query (columns is year). I would like to change the caption and adjust others things. The best way would be to wrap the query in a form, but soon 2007 will...
9
by: Tony Girgenti | last post by:
Hello I developed and tested a web application using VS.NET 2003, VB, .NET Framework 1.1.4322, ASP.NET 1.1.4322 and IIS5.1. It uses a web form. I tried doing this without any help, but i'm...
1
by: Sebastian Spandauer | last post by:
Hey, i am searching for a php script that features sending a lets say 20 lesson online course. it should: subscription on website opt-in after subsriciption send one lesson per day to every...
0
by: Chuck36963 | last post by:
Hi all, I've been working on a listing problem and I can't figure out how to work it out. I have looked far and wide on the web to find answers, but I'd like other peoples input on my project in...
69
by: kabradley | last post by:
Alrighty Guys and Gals, I have another question that I hope you all can help me with. I have a report that uses a cross-tab query as its record source. This cross-tab query is getting all of its...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.