473,320 Members | 1,946 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Help with query need

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'.

Nov 13 '05 #1
2 1433
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_LOOKUP (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_LOOKUP ON TBL_INTEREST.INT_PK =
TBL_INTEREST_LOOKUP.LU_INT_FK) INNER JOIN TBL_CONTACTS ON
TBL_INTEREST_LOOKUP.LU_CON_FK = TBL_CONTACTS.CON_PK
where
INT_DESCRIP like Forms!MyForm.cboInterest1 & "*" or
INT_DESCRIP like Forms!MyForm.cboInterest2 & "*" or
INT_DESCRIP like Forms!MyForm.cboInterest3 & "*"

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
Nov 13 '05 #2

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> 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_LOOKUP (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_LOOKUP ON TBL_INTEREST.INT_PK =
TBL_INTEREST_LOOKUP.LU_INT_FK) INNER JOIN TBL_CONTACTS ON
TBL_INTEREST_LOOKUP.LU_CON_FK = TBL_CONTACTS.CON_PK
where
INT_DESCRIP like Forms!MyForm.cboInterest1 & "*" or
INT_DESCRIP like Forms!MyForm.cboInterest2 & "*" or
INT_DESCRIP like Forms!MyForm.cboInterest3 & "*"

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
:-(
Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

9
by: netpurpose | last post by:
I need to extract data from this table to find the lowest prices of each product as of today. The product will be listed/grouped by the name only, discarding the product code - I use...
7
by: Steve | last post by:
I have a SQL query I'm invoking via VB6 & ADO 2.8, that requires three "Left Outer Joins" in order to return every transaction for a specific set of criteria. Using three "Left Outer Joins"...
9
by: Dom Boyce | last post by:
Hi First up, I am using MS Access 2002. I have a database which records analyst rating changes for a list of companies on a daily basis. Unfortunately, the database has been set up (by my...
8
by: Andrew McNab | last post by:
Hi folks, I have a problem with an MS Access SQL query which is being used in an Access Report, and am wondering if anyone can help. Basically, my query (shown below) gets some records from a...
5
by: Rated R1 | last post by:
I wrote this before in the NGs, so I am going to paste the responses that I got and see if someone can please help me. Email me and we can set something up as Id even be willing to pay for your...
5
by: Steve Patrick | last post by:
Hi All You guys are my last hope, despite spending money on books and hours reading them I still can not achieve the results I need. I have designed a database in Access 2000 based on 1 table,...
4
by: Laura | last post by:
Here's the situation: I'm trying to use an update query to copy data from one row to another. Here is the situation: I have 5 companies that are linked to each other. I need to show all 5...
10
by: L. R. Du Broff | last post by:
I own a small business. Need to track a few hundred pieces of rental equipment that can be in any of a few dozen locations. I'm an old-time C language programmer (UNIX environment). If the only...
8
by: pamelafluente | last post by:
I am beginning aspNet, I know well win apps. Need a simple and schematic code example to start work. This is what I need to accomplish: ---------------------- Given button and a TextBox on a...
11
by: troy_lee | last post by:
I have two fields on a form. These two fields' values are based on an expression and represent a date range. I need to create a SQL statement that will use the returned values of these two fields...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.