If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can i do a query to just search the whole record?Please Help!
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 |
#2
|
|||
|
|||
How can i do a query to just search the whole record?Please Help!
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 "benmc" wrote in message ... 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 |
#3
|
|||
|
|||
How can i do a query to just search the whole record?Please Help!
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. "Jeff Boyce" wrote in message ... 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 "benmc" wrote in message ... 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 |
#4
|
|||
|
|||
How can i do a query to just search the whole record?Please He
Just wanted to say thanks very much jeff.
"Jeff Boyce" wrote: 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 "benmc" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cross tab query construction with Subqueries | Steven Cheng | Running & Setting Up Queries | 7 | February 13th, 2006 06:52 PM |
Get External Data - not editable using Query Wizard | MargaretBeckbury | General Discussion | 7 | January 17th, 2006 09:13 AM |
Press Release - New Search Engine Debuts Features | [email protected] | Powerpoint | 0 | December 17th, 2005 11:35 AM |
Press Release - New Search Engine Debuts Features | [email protected] | General Discussion | 0 | December 17th, 2005 11:28 AM |
Need One Query to Search Multiple Date Fields, If Possible | Marcia | Running & Setting Up Queries | 4 | October 24th, 2004 05:28 AM |