Query joins using and/or operators

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?

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.


"KarenB" wrote in message
...
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?

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.


"KarenB" wrote in message
...
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?

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.


"KarenB" wrote in message
...
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?

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?

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?

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 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 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 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?