Query joins using and/or operators

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?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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?

--
Microsoft MVP - ASP/ASP.NET - 2004-2007
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

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

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?

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

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?

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

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?

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

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

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


.