Query joins using > and/or < operators

[KarenB]

I don't know if this is possible in Access - I can do it in Crystal but
haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent back to us
(Survey_tbl). The other shows a range of response card numbers for a
particular region (Kits_tbl). There is one field in the Survey_tbl with the
response card number[KitNumber]. There are two fields in the Kits_tbl with
the response card numbers assigned to a region [KitStartNumber] and
[KitEndNumber]. I would like to be able to create a query joining the
Survey_tbl to the Kits_tbl. In Crystal, i am able to create joins that link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is there any way
to do the same sort of thing in Access?

 

[Jeff Boyce]

Karen

I'm not sure what you're asking about yet. You mention a ">= and <= join
type", but don't describe what they do. I don't know if Access can do that
because I don't know what "that" is...

More info, please...

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Dale Fye]

yes. What you are looking for is a non-equi join. Unfortunately, you cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and create
just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL, then
create the query in the design grid as though you were doing equality joins.
Then, once you have the query created, switch to design view and change the =
signs to >= or <= as appropriate.

 

[David F Cox]

I am very rusty, here to scrape some off. My first thought was WHERE ....
BETWEEN ... Am I completely off target?

David F. Cox

yes. What you are looking for is a non-equi join. Unfortunately, you
cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and create
just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL, then
create the query in the design grid as though you were doing equality
joins.
Then, once you have the query created, switch to design view and change
the =
signs to >= or <= as appropriate.

----
HTH
Dale

I don't know if this is possible in Access - I can do it in Crystal but
haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent back to
us
(Survey_tbl). The other shows a range of response card numbers for a
particular region (Kits_tbl). There is one field in the Survey_tbl with
the
response card number[KitNumber]. There are two fields in the Kits_tbl
with
the response card numbers assigned to a region [KitStartNumber] and
[KitEndNumber]. I would like to be able to create a query joining the
Survey_tbl to the Kits_tbl. In Crystal, i am able to create joins that
link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is there any
way
to do the same sort of thing in Access?

 

[Bob Barrows]

No. Some people prefer to avoid BETWEEN if there is a possibility that they
will need to port their code to a database that does not support that
keyword, which is simply syntactic "sugar" that makes writing the " ...<=
.... and ... >= ... " statement a bit easier.
Jet and SQL Server both support the use of that keyword.

I am very rusty, here to scrape some off. My first thought was WHERE
.... BETWEEN ... Am I completely off target?

David F. Cox

yes. What you are looking for is a non-equi join. Unfortunately,
you cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and
create just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL,
then create the query in the design grid as though you were doing
equality joins.
Then, once you have the query created, switch to design view and
change the =
signs to >= or <= as appropriate.

----
HTH
Dale

I don't know if this is possible in Access - I can do it in Crystal
but haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent
back to us
(Survey_tbl). The other shows a range of response card numbers for a
particular region (Kits_tbl). There is one field in the Survey_tbl
with the
response card number[KitNumber]. There are two fields in the
Kits_tbl with
the response card numbers assigned to a region [KitStartNumber] and
[KitEndNumber]. I would like to be able to create a query joining
the Survey_tbl to the Kits_tbl. In Crystal, i am able to create
joins that link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is
there any way
to do the same sort of thing in Access?

 

[John Spencer]

I know MS SQL supports the Between ... and ... construct, BUT it is a bit
different from the way Access does it. In MS SQL (up to 2005 the latest
version I have) you must have the values in order lowest to highest or you
will get zero records returned. I assume that is because the SQL interpreter
changes X Between 20 and 1 to the equivalent of
x>=20 and x<=1

Access handles the statement a bit differently and effectively changes X
Between 2 and 1 to the equivalent of
X>=1 and X<=20

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

No. Some people prefer to avoid BETWEEN if there is a possibility that they
will need to port their code to a database that does not support that
keyword, which is simply syntactic "sugar" that makes writing the " ...<=
... and ... >= ... " statement a bit easier.
Jet and SQL Server both support the use of that keyword.

I am very rusty, here to scrape some off. My first thought was WHERE
.... BETWEEN ... Am I completely off target?

David F. Cox

yes. What you are looking for is a non-equi join. Unfortunately,
you cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and
create just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL,
then create the query in the design grid as though you were doing
equality joins.
Then, once you have the query created, switch to design view and
change the =
signs to >= or <= as appropriate.

----
HTH
Dale



:

I don't know if this is possible in Access - I can do it in Crystal
but haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent
back to us
(Survey_tbl). The other shows a range of response card numbers for a
particular region (Kits_tbl). There is one field in the Survey_tbl
with the
response card number[KitNumber]. There are two fields in the
Kits_tbl with
the response card numbers assigned to a region [KitStartNumber] and
[KitEndNumber]. I would like to be able to create a query joining
the Survey_tbl to the Kits_tbl. In Crystal, i am able to create
joins that link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is
there any way
to do the same sort of thing in Access?

 

[Bob Barrows]

You're right, I forgot about that ... basically because it would never
occur to me to write the from...to values in reverse order

I know MS SQL supports the Between ... and ... construct, BUT it is a
bit different from the way Access does it. In MS SQL (up to 2005 the
latest version I have) you must have the values in order lowest to
highest or you will get zero records returned. I assume that is
because the SQL interpreter changes X Between 20 and 1 to the
equivalent of x>=20 and x<=1

Access handles the statement a bit differently and effectively
changes X Between 2 and 1 to the equivalent of

=1 and X<=20

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

No. Some people prefer to avoid BETWEEN if there is a possibility
that they will need to port their code to a database that does not
support that keyword, which is simply syntactic "sugar" that makes
writing the " ...<= ... and ... >= ... " statement a bit easier.
Jet and SQL Server both support the use of that keyword.

I am very rusty, here to scrape some off. My first thought was WHERE
.... BETWEEN ... Am I completely off target?

David F. Cox


yes. What you are looking for is a non-equi join. Unfortunately,
you cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and
create just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL,
then create the query in the design grid as though you were doing
equality joins.
Then, once you have the query created, switch to design view and
change the =
signs to >= or <= as appropriate.

----
HTH
Dale



:

I don't know if this is possible in Access - I can do it in
Crystal but haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent
back to us
(Survey_tbl). The other shows a range of response card numbers
for a particular region (Kits_tbl). There is one field in the
Survey_tbl with the
response card number[KitNumber]. There are two fields in the
Kits_tbl with
the response card numbers assigned to a region [KitStartNumber]
and [KitEndNumber]. I would like to be able to create a query
joining the Survey_tbl to the Kits_tbl. In Crystal, i am able to
create joins that link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is
there any way
to do the same sort of thing in Access?

 

[KarenB]

Thank you guys - I was able to get this to work with the values in reverse
order. I had tried writing it myself prior to posting my question, and didn't
know about the reverse order thing. Works perfectly now.

You're right, I forgot about that ... basically because it would never
occur to me to write the from...to values in reverse order

I know MS SQL supports the Between ... and ... construct, BUT it is a
bit different from the way Access does it. In MS SQL (up to 2005 the
latest version I have) you must have the values in order lowest to
highest or you will get zero records returned. I assume that is
because the SQL interpreter changes X Between 20 and 1 to the
equivalent of x>=20 and x<=1

Access handles the statement a bit differently and effectively
changes X Between 2 and 1 to the equivalent of

=1 and X<=20

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

No. Some people prefer to avoid BETWEEN if there is a possibility
that they will need to port their code to a database that does not
support that keyword, which is simply syntactic "sugar" that makes
writing the " ...<= ... and ... >= ... " statement a bit easier.
Jet and SQL Server both support the use of that keyword.

David F Cox wrote:
I am very rusty, here to scrape some off. My first thought was WHERE
.... BETWEEN ... Am I completely off target?

David F. Cox


yes. What you are looking for is a non-equi join. Unfortunately,
you cannot
create or view this type of query in the query design grid.

If you know SQL well enough to type the SQL, go to the SQL view and
create just create the query. It would look something like:

SELECT S.*, K.*
FROM Survey_tbl as S
INNER JOIN Kits_tbl as K
ON S.KitNumber >= K.KitStartNumber
AND S.KitNumber <= K.KitEndNumber

If you are not comfortable with writing the query directly in SQL,
then create the query in the design grid as though you were doing
equality joins.
Then, once you have the query created, switch to design view and
change the =
signs to >= or <= as appropriate.

----
HTH
Dale



:

I don't know if this is possible in Access - I can do it in
Crystal but haven't been able to figure out how in Access.

I have two tables - one shows the number of a response card sent
back to us
(Survey_tbl). The other shows a range of response card numbers
for a particular region (Kits_tbl). There is one field in the
Survey_tbl with the
response card number[KitNumber]. There are two fields in the
Kits_tbl with
the response card numbers assigned to a region [KitStartNumber]
and [KitEndNumber]. I would like to be able to create a query
joining the Survey_tbl to the Kits_tbl. In Crystal, i am able to
create joins that link
the [KitNumber] in the Survey_tbl to both the [KitStartNumber] and
[KitEndNumber] in the Kits_tbl, with a >= and <= join type. Is
there any way
to do the same sort of thing in Access?

--
HTH,
Bob Barrows


.