How can i do a query to just search the whole record?Please Help!

B

benmc

Hi,
I have searched the assistance site for the answer to this but i'm sure i am
missing it somewhere. Basically, i'll just explain my table and maybe someone
can help me...

One record includes, firstname, last name, email address, interest 1,
interest 2... up to interest 6.... so thats a different coloumn for each
interest.

This is for a student newspaper, so the interest are things like, sports,
games, features, etc. I want to make it so that i can bring up any record
where the person has an interest in, say, games. But for some people "games"
may be in interest 1 but for others its in interest 4, some people have all
the interest coloumns filled and some have say only one.
So basically if i search for people who want to write about games i want it
to bring up people who have games in any coloumn in their record.
I hope this explains my problem, i know there is probably a really basic
answer, but i am really going in circles here!
With thanks,
Ben
 
J

Jeff Boyce

Ben

From your description, you have a ... spreadsheet! Using repeating columns
("interest1", "interest2", ..."interestn") is a necessary consequence of
using a spreadsheet, but it is both unnecessary and counter-productive in a
relational database.

Here's why you don't want to do that ...
When someone decides that you should have SEVEN interests instead of 6,
you'll have to change your current table structure, any queries that refer
to the 6 columns, any forms that display the 6 columns, any reports that
display the 6 columns, and any code that refers to the 6 columns.

That's way too much work!

Instead, you can normalize your data structure and make your job a lot
easier. For example, you might want to try:

tblStudent
StudentID
FName
LName
DOB
... (other student-only information)

tblInterest (one row per interest)
InterestID
Interest (e.g. sports)

trelStudentInterest (one row per valid student-interest pair -- could be
none or 6 or 7 or ... per student)
StudentInterestID
StudentID
InterestID

Good luck!

Jeff Boyce
<Office/Access MVP>
 
E

Ed Warren

To add to Jeff's great advice.
When you want to know "how many people are interested in sports" you have a
very simple query. Whereas trying to query against the current structure is
a minor nightmare.

Ed Warren.
 
B

benmc

Just wanted to say thanks very much jeff.

Jeff Boyce said:
Ben

From your description, you have a ... spreadsheet! Using repeating columns
("interest1", "interest2", ..."interestn") is a necessary consequence of
using a spreadsheet, but it is both unnecessary and counter-productive in a
relational database.

Here's why you don't want to do that ...
When someone decides that you should have SEVEN interests instead of 6,
you'll have to change your current table structure, any queries that refer
to the 6 columns, any forms that display the 6 columns, any reports that
display the 6 columns, and any code that refers to the 6 columns.

That's way too much work!

Instead, you can normalize your data structure and make your job a lot
easier. For example, you might want to try:

tblStudent
StudentID
FName
LName
DOB
... (other student-only information)

tblInterest (one row per interest)
InterestID
Interest (e.g. sports)

trelStudentInterest (one row per valid student-interest pair -- could be
none or 6 or 7 or ... per student)
StudentInterestID
StudentID
InterestID

Good luck!

Jeff Boyce
<Office/Access MVP>
Click to expand...
 
Top