Query Problem

[Niall]

I have two tables, one contains information on properties and one of the
fields within this table is "Band" this contains a numeric value. The second
table contains a "minimumvalue", "maximiumvalue" and "equvilantband". I am
trying to build a query that will take the value of band from the properties
table and determine what the "equivilant band" is by seeing which category of
"minimum/maximumvalue" it falls in eg say one of the rows from the second
table is as follows

minimumvalue maximumvalue equivilantband
0 100 A

and from the properties table the "band" value is 50 then I want the query
to return the value "A"

Thanks for your help

 

[JL]

Hi Niall,

Here is one way.

SELECT properties.[Band], DLookUp("[equvilantband]","Table1",[Band] & " >=
[Table1]![minimumvalue] AND " & [Band] & " <= [Table1]![maximiumvalue]")
FROM properties;

The only problem is that is will only return first one that matches. The
record that falls in the MIN and MAX criteria.

Hope this helps.

 

[Niall]

Thanks for this, the propertie table has over 4500 entries and i need to
calculate the equivilant bands for each row any ideas?

Hi Niall,

Here is one way.

SELECT properties.[Band], DLookUp("[equvilantband]","Table1",[Band] & " >=
[Table1]![minimumvalue] AND " & [Band] & " <= [Table1]![maximiumvalue]")
FROM properties;

The only problem is that is will only return first one that matches. The
record that falls in the MIN and MAX criteria.

Hope this helps.

I have two tables, one contains information on properties and one of the
fields within this table is "Band" this contains a numeric value. The second
table contains a "minimumvalue", "maximiumvalue" and "equvilantband". I am
trying to build a query that will take the value of band from the properties
table and determine what the "equivilant band" is by seeing which category of
"minimum/maximumvalue" it falls in eg say one of the rows from the second
table is as follows

minimumvalue maximumvalue equivilantband
0 100 A

and from the properties table the "band" value is 50 then I want the query
to return the value "A"

Thanks for your help

 

[JL]

Hi Niall,

Got lots ideas.
When I say it will only find first record, I mean that it will find the
first equivilant bands record that fit the propertie record. If there is
more than 1 equivilant bands that fit the propertie record, then you will
not be able to do it with that "DLookup query".
If the above is not enough, then post back.

Thanks for this, the propertie table has over 4500 entries and i need to
calculate the equivilant bands for each row any ideas?

Hi Niall,

Here is one way.

SELECT properties.[Band], DLookUp("[equvilantband]","Table1",[Band] & " >=
[Table1]![minimumvalue] AND " & [Band] & " <= [Table1]![maximiumvalue]")
FROM properties;

The only problem is that is will only return first one that matches. The
record that falls in the MIN and MAX criteria.

Hope this helps.

I have two tables, one contains information on properties and one of the
fields within this table is "Band" this contains a numeric value. The second
table contains a "minimumvalue", "maximiumvalue" and "equvilantband". I am
trying to build a query that will take the value of band from the properties
table and determine what the "equivilant band" is by seeing which category of
"minimum/maximumvalue" it falls in eg say one of the rows from the second
table is as follows

minimumvalue maximumvalue equivilantband
0 100 A

and from the properties table the "band" value is 50 then I want the query
to return the value "A"

Thanks for your help