Sub Query with Between condition

[aireq]

I have a Query called [Building Stories] which contains two fields [Building
ID] and [Stories]. I also have a table called [Building Type Codes] which
lists a range of building types and a minimum and maximum number of stories
which would identify a building as being in that type. Thus [Building Type
Codes] has fields [Building Type Code], [Min Floors], and [Max Floors]

I'm trying to create a query that will combine [Building Stories] and
[Building Type Codes] which will list all the buildings and their derived
type. So I created a query that selects [Building ID] and [Stories] from
[Building Stories], and then has a sub query that selects the [Building Type
Code] from [Building Type Codes] where [Stories] is between [Min Floors] and
[Max Floors]. My SQL command is below.

Whenever I try to run this query I get the error: "Unknown Access database
error"

Can someone tell me what I'm doing wrong? I'm new the Access and all this
SQL query stuff.



SELECT [Building Stories].[Building ID], [Building Stories].Stories, (SELECT
[Building Type Code] FROM [Building Type Codes] WHERE [Stories] Between
[Building Type Codes]![Min Floors] And [Building Type Codes]![Max Floors]) AS
[Building Type]
FROM [Building Stories];

 

[John Spencer]

Try the following query which uses a non-equi join (fields in the on
clause are not equal)

SELECT [Building Stories].[Building ID]
, [Building Stories].Stories
, [Building Type Codes].[Building Type Code]
FROM [Building Stories] INNER JOIN [Building Type Codes]
ON [Building Stories].Stories <= [Building type Codes].[Max Floors]
AND [Building Stories].Stories >= [Building Type Codes].[Min Floors]


The problem with your query was that in theory the sub query could
return more than one record. That is a no-no in the SELECT CLAUSE

You might be able to use

SELECT [Building Stories].[Building ID]
, [Building Stories].Stories
, (SELECT First([Building Type Code])
FROM [Building Type Codes]
WHERE [Building Stories].[Stories]
Between [Building Type Codes].[Min Floors]
And [Building Type Codes]![Max Floors])
AS [The Building Type]
FROM [Building Stories];

Note I change the alias for the result to a different name the field
involved. That should not be necessary, but it won't hurt.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[aireq]

OK here's the test contents of [Building Stories] and [Building Type Codes]

[Building Stories]
Building ID Stories
01-01-01 4
01-01-02 7
02-01-01 5

[Building Type Codes]
ID Building Type Code Min Floors Max Floors
1 L 1 5
2 M 6 10
3 T 11 99


Here's the query that I used:

SELECT
[Building Stories].[Building ID],
[Building Stories].Stories,
[Building Type Codes].[Building Type Code],
[Building Type Codes].[Min Floors],
[Building Type Codes].[Max Floors]
FROM
[Building Stories]
INNER JOIN
[Building Type Codes]
ON
[Building Stories].Stories <= [Building Type Codes].[Max Floors]
AND
[Building Stories].Stories >=[Building Type Codes].[Min Floors]

.. .and here is the result

Building ID Stories Building Type Code Min Floors Max Floors
01-01-01 4 L 1 5
01-01-01 4 T 11 99
01-01-02 7 T 11 99
02-01-01 5 L 1 5
02-01-01 5 T 11 99

Any idea what is going on here? It thinks 01-01-02 is a (T)ower which it's
not. While it thinks 02-01-01, and 01-01-01 are (L)owrises, it also thinks
they are (T)owers. If I take out the join it creates a full inner join as I
would expect matching every building type to every building.


Eric

 

[Lord Kelvan]

are you sure because i just ran a test on your data and your query and
my results were

Building ID Stories Building Type Code Min Floors Max Floors
01-01-01 4 l
1 5
02-01-01 5 l
1 5
01-01-02 7 m 6
10

Regards
Kelvan

 

[aireq]

Ok but in my dataase [Building Stories] is the result of another query. What
I'm wondering now is if it's an issue of data types. [Building Stories] could
be returning the number of stories as a string rather then an integer which
is messing up the <= and >= comparisons.

I tried replacing my query [Building Stories] with a table, but use TEXT
values for [Stories]. This gave me the same result as before. Then I tried
changing [Stories], [Min Floors], and [Max Floors] to number fields and it
worked correctly. So obviously it's a data type issue.

So how do you control the datatypes of query results? Or specifically how do
I force a number to be interpreted as a number rather then a string in a
query.


Eric

 

[Lord Kelvan]

o i c then thats easy

SELECT
[Building Stories].[Building ID],
[Building Stories].Stories,
[Building Type Codes].[Building Type Code],
[Building Type Codes].[Min Floors],
[Building Type Codes].[Max Floors]
FROM
[Building Stories]
INNER JOIN
[Building Type Codes]
ON
cint([Building Stories].Stories) <= cint([Building Type Codes].[Max
Floors])
AND
cint([Building Stories].Stories) >= cint([Building Type Codes].[Min
Floors])

if it is text then it is most likly doing it so you can use the
converter cint() to convert the data type to a number

hope this helps

Regards
Kelvan