Help with query need

[news.uow.edu.au]

Any suggestions for the following would be gratefully accepted.

I have a table which includes name and interest fields e.g.,

contact interest
smith carpentry
jones gardening
smith plumbing
smith geology
jones geology

I want users to be able to produce a report which lists the names of those
who meet certain criteria (i.e., the user selects up to 3 interests using
combo boxes and then produces a report which lists the names of those who
have those interests). For example, choosing geology and carpentry should
return only the name 'smith'.

[Tim Marshall]

news.uow.edu.au wrote:

Any suggestions for the following would be gratefully accepted.

I have a table which includes name and interest fields e.g.,

contact interest
smith carpentry
jones gardening
smith plumbing
smith geology
jones geology

I want users to be able to produce a report which lists the names of those
who meet certain criteria (i.e., the user selects up to 3 interests using
combo boxes and then produces a report which lists the names of those who
have those interests). For example, choosing geology and carpentry should
return only the name 'smith'.

You would be much better off to have the names listed once in a single
table the interests in a second table listed once and a third table
which has a pointer number from the first and second.

For example:

TBL_CONTACTS

CON_PK CON_NAME_2 CON_NAME_1 CON_ADDRESS etc, etc
7568 Smith John
123 Jones Brigitte
34569 Keen I.M.
etc

TBL_INTERESTS

INT_PK INT_DESCRIP INT_DETAILS etc, etc
671234 Carpentry Hitting things with hammers...
1235 Gardening Swearing at and hitting weeds with hammers...
1112 Plumbing Hitting pipes with hammers...
98356 Geology Hitting rocks with hammers....

TBL_INTEREST_LO OKUP (configured as for your example)

LU_CON_FK LU_INT_FK
7568 671234
123 1235
7568 1112
7568 98356
123 98356

For the PK numbers, you never let people see or bother with the numbers
- use an autonumber datatype (that's why I stuffed in random looking
numbers).

This is a harder way to do things for a newbie, especially with respect
to data entry - you'll need to make up 3 forms: one for listing and
adding/editing contacts (form associated with TBL_CONTACTS), another for
listing interests (form associated with TBL_INTERESTS) and a third,
probably using combo boxes, to list contacts and interests.

Hard, yes, but you'll thank yourself in the future for doing things the
database way.

The query to get the information you'd want would be (air code):

select
CON_NAME_1 & ", " & CON_NAME_2 as Contact,
INT_DESCRIP as Interest
from
(TBL_INTERESTS INNER JOIN TBL_INTEREST_LO OKUP ON TBL_INTEREST.IN T_PK =
TBL_INTEREST_LO OKUP.LU_INT_FK) INNER JOIN TBL_CONTACTS ON
TBL_INTEREST_LO OKUP.LU_CON_FK = TBL_CONTACTS.CO N_PK
where
INT_DESCRIP like Forms!MyForm.cb oInterest1 & "*" or
INT_DESCRIP like Forms!MyForm.cb oInterest2 & "*" or
INT_DESCRIP like Forms!MyForm.cb oInterest3 & "*"

Open a query, close the show table dialog and click on the SQL view of
the table and paste the above into the query.

The key part here (which can be used also on your single table approach
in your original post) is the where clause, above. By using the Like
operator, you allow a combo box which has nothing in it to not mess up
the works.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[BatHawk]

"Tim Marshall" <TI****@PurpleP andaChasers.Moe rtherium> wrote in message
news:d5******** **@coranto.ucs. mun.ca...

news.uow.edu.au wrote:

Any suggestions for the following would be gratefully accepted.

I have a table which includes name and interest fields e.g.,

contact interest
smith carpentry
jones gardening
smith plumbing
smith geology
jones geology

I want users to be able to produce a report which lists the names of
those who meet certain criteria (i.e., the user selects up to 3 interests
using combo boxes and then produces a report which lists the names of
those who have those interests). For example, choosing geology and
carpentry should return only the name 'smith'.

You would be much better off to have the names listed once in a single
table the interests in a second table listed once and a third table which
has a pointer number from the first and second.

For example:

TBL_CONTACTS

CON_PK CON_NAME_2 CON_NAME_1 CON_ADDRESS etc, etc
7568 Smith John
123 Jones Brigitte
34569 Keen I.M.
etc

TBL_INTERESTS

INT_PK INT_DESCRIP INT_DETAILS etc, etc
671234 Carpentry Hitting things with hammers...
1235 Gardening Swearing at and hitting weeds with hammers...
1112 Plumbing Hitting pipes with hammers...
98356 Geology Hitting rocks with hammers....

TBL_INTEREST_LO OKUP (configured as for your example)

LU_CON_FK LU_INT_FK
7568 671234
123 1235
7568 1112
7568 98356
123 98356

For the PK numbers, you never let people see or bother with the numbers -
use an autonumber datatype (that's why I stuffed in random looking
numbers).

This is a harder way to do things for a newbie, especially with respect to
data entry - you'll need to make up 3 forms: one for listing and
adding/editing contacts (form associated with TBL_CONTACTS), another for
listing interests (form associated with TBL_INTERESTS) and a third,
probably using combo boxes, to list contacts and interests.

Hard, yes, but you'll thank yourself in the future for doing things the
database way.

The query to get the information you'd want would be (air code):

select
CON_NAME_1 & ", " & CON_NAME_2 as Contact,
INT_DESCRIP as Interest
from
(TBL_INTERESTS INNER JOIN TBL_INTEREST_LO OKUP ON TBL_INTEREST.IN T_PK =
TBL_INTEREST_LO OKUP.LU_INT_FK) INNER JOIN TBL_CONTACTS ON
TBL_INTEREST_LO OKUP.LU_CON_FK = TBL_CONTACTS.CO N_PK
where
INT_DESCRIP like Forms!MyForm.cb oInterest1 & "*" or
INT_DESCRIP like Forms!MyForm.cb oInterest2 & "*" or
INT_DESCRIP like Forms!MyForm.cb oInterest3 & "*"

Open a query, close the show table dialog and click on the SQL view of the
table and paste the above into the query.

The key part here (which can be used also on your single table approach in
your original post) is the where clause, above. By using the Like
operator, you allow a combo box which has nothing in it to not mess up the
works.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Thanks Tim,

much appreciate the response, it gets me half of what I'd like, but i'm
still stuck on the rest e.g., if i search for people who have 3 interests,
I get them, but I also get the names of those who have only 1 or 2 of the
three. I'd like to be able to return ONLY the names of those who have ALL
the interests (i.e., to exclude those who have only 1 or 2 of the interests.

PS: I thought i might be able to use the COUNT option to limit the result to
those who have all 3 interests only, but it all seems a bit beyond my skills
:-(