Compare two tables

S

Saz

Hi,

I have two tables Student and Hobby

Student Table
Stu_ID Stuent_name Stu_Address Stu_Hobby1 Stu_Hobby2
Stu_Hobby3
1 John Doe 123 Noname Street Traveling
Soccer Football
2 Mary Smith 456 Yesname Street Soccer
Dancing Singing
3 George Doe 789 Noyes Street Traveling
Dancing Singing
4 No Name 321 Today Street Stamp
Coin Dancing

Hobby Table
Hobby_ID Hobby_name
1 Soccer

How can I get the result table with the hobby1 or Hobby2 or Hobby3 =
Soccer

The expected Table is
Hobby Soccer
Stu_ID Stuent_name Stu_Address Stu_Hobby1 Stu_Hobby2
Stu_Hobby3
1 John Doe 123 Noname Street Traveling
Soccer Football
2 Mary Smith 456 Yesname Street Soccer
Dancing Singing

Please HELP!
 
G

Guest

Your design is not good, you should have the hobbies in a separate table not
all in the student table.
How are your student table and hobby table linked?
-Dorian
 
J

John W. Vinson

Hi,

I have two tables Student and Hobby

Student Table
Stu_ID Stuent_name Stu_Address Stu_Hobby1 Stu_Hobby2
Stu_Hobby3
1 John Doe 123 Noname Street Traveling
Soccer Football
2 Mary Smith 456 Yesname Street Soccer
Dancing Singing
3 George Doe 789 Noyes Street Traveling
Dancing Singing
4 No Name 321 Today Street Stamp
Coin Dancing

Hobby Table
Hobby_ID Hobby_name
1 Soccer

How can I get the result table with the hobby1 or Hobby2 or Hobby3 =
Soccer

The expected Table is
Hobby Soccer
Stu_ID Stuent_name Stu_Address Stu_Hobby1 Stu_Hobby2
Stu_Hobby3
1 John Doe 123 Noname Street Traveling
Soccer Football
2 Mary Smith 456 Yesname Street Soccer
Dancing Singing

Please HELP!
Click to expand...

Correct your table design. I have four hobbies - contradancing, gardening,
singing and answering newsgroup questions. Your table design cannot handle me!

"Fields are expensive, records are cheap". When you have a Many (students) to
Many (hobbies) relationship you need *three tables*:

Students
Stu_ID
LastName
FirstName
<other biographical info>

Hobbies
Hobby_Name <text, primary key; don't need a numeric ID I'd say>

StudentHobbies
Stu_ID <link to Students>
Hobby_Name <link to Hobbies>

You could then use a form based on Students with a subform based on
StudentHobbies to add as many hobbies to a student's list as he or she has
time for (or perhaps one or two more).

John W. Vinson [MVP]
 
Top