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?
3 2147
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...
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 - 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.....
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]!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 - 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.....
Sign in to post your reply or Sign up for a free account.
Similar topics
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 ...
|
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,...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
|
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...
|
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...
| |