Query joins using and/or operators

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.


"Bob Barrows" wrote:

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


John Spencer wrote:
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

Bob Barrows wrote:
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


"Dale Fye" wrote in message
...
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



"KarenB" wrote:

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


.

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.


"Bob Barrows" wrote:

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


John Spencer wrote:
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

Bob Barrows wrote:
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


"Dale Fye" wrote in message
...
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



"KarenB" wrote:

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


.