Lookup Query Help

  • Thread starter Thread starter mattc66 via AccessMonster.com
  • Start date Start date
M

mattc66 via AccessMonster.com

Hi All,

I need to build a query that will display the data bases on the range of a 2
cells.

We build heaters and CuFt Size of your room determines that Heater KW you
need.

So I have a table with the CuFt Min and CuFt Max and the corrisponding Heater
KW.

The user will enter the Room CuFt in a form and I want to display the Heater
KW(s) that would work for the room size. So if the user entered 125 the
result would be 3.0 KW and 4.5 KW.

How would I write my query so that it can look up this info.

Room Size HEATER
CuFt Min CuFt Max KW
45 100 2.1
70 150 3.0
100 210 4.5
175 310 6.0
250 425 8.0
390 600 10.5
510 740 12.0
630 950 14.4

Thanks
Matt
 
mattc66 said:
I need to build a query that will display the data bases on the range of a 2
cells.

We build heaters and CuFt Size of your room determines that Heater KW you
need.

So I have a table with the CuFt Min and CuFt Max and the corrisponding Heater
KW.

The user will enter the Room CuFt in a form and I want to display the Heater
KW(s) that would work for the room size. So if the user entered 125 the
result would be 3.0 KW and 4.5 KW.

How would I write my query so that it can look up this info.

Room Size HEATER
CuFt Min CuFt Max KW
45 100 2.1
70 150 3.0
100 210 4.5
175 310 6.0
250 425 8.0
390 600 10.5
510 740 12.0
630 950 14.4


If you are just using this as a quich calculation, may be
that this simple query is all you need.

SELECT KW
FROM table
WHERE [Enter Room Square Feet]
Between [CuFt Min] And [CuFt Max]

If you want to do this on a form where users are entering
other data, then try using a text box with this kind of
expression:
=DLookup("KW, "table", txtRSqSf & " Between [CuFt Min] And
[CuFt Max]")

where txtRSqSf is the name of the text box where users enter
the room size.
 
when I tried the first suggestion I got a prompt for [Enter Room Square Feet]
and a prompt for [CuFt Min] and a prompt for [CuFt Max]. It should have only
prompted for the Room Square Feet.

SELECT KW
FROM tblHeaterCuFt
WHERE [Enter Room Square Feet] Between [CuFt Min] And [CuFt Max];

Marshall said:
I need to build a query that will display the data bases on the range of a 2
cells.
[quoted text clipped - 21 lines]
510 740 12.0
630 950 14.4

If you are just using this as a quich calculation, may be
that this simple query is all you need.

SELECT KW
FROM table
WHERE [Enter Room Square Feet]
Between [CuFt Min] And [CuFt Max]

If you want to do this on a form where users are entering
other data, then try using a text box with this kind of
expression:
=DLookup("KW, "table", txtRSqSf & " Between [CuFt Min] And
[CuFt Max]")

where txtRSqSf is the name of the text box where users enter
the room size.
 
Your question indicated that "CuFt Min CuFt Max KW" were
the field names in the table. Apparently, that's not the
case, so you'll have to change them to whatever the real
names are.
--
Marsh
MVP [MS Access]

when I tried the first suggestion I got a prompt for [Enter Room Square Feet]
and a prompt for [CuFt Min] and a prompt for [CuFt Max]. It should have only
prompted for the Room Square Feet.

SELECT KW
FROM tblHeaterCuFt
WHERE [Enter Room Square Feet] Between [CuFt Min] And [CuFt Max];

Marshall said:
I need to build a query that will display the data bases on the range of a 2
cells.
[quoted text clipped - 21 lines]
510 740 12.0
630 950 14.4

If you are just using this as a quich calculation, may be
that this simple query is all you need.

SELECT KW
FROM table
WHERE [Enter Room Square Feet]
Between [CuFt Min] And [CuFt Max]

If you want to do this on a form where users are entering
other data, then try using a text box with this kind of
expression:
=DLookup("KW, "table", txtRSqSf & " Between [CuFt Min] And
[CuFt Max]")

where txtRSqSf is the name of the text box where users enter
the room size.
 
Thank you - yes I discovered that after I replied. Work great, thanks.

Marshall said:
Your question indicated that "CuFt Min CuFt Max KW" were
the field names in the table. Apparently, that's not the
case, so you'll have to change them to whatever the real
names are.
when I tried the first suggestion I got a prompt for [Enter Room Square Feet]
and a prompt for [CuFt Min] and a prompt for [CuFt Max]. It should have only
[quoted text clipped - 26 lines]
 
Back
Top