Complicated for a newbie, Likely easy for a pro. Please help

[DavidSwift9]

Hi, I have an problem that I really need help with. I been trying to get around it all day. Its been simplified below so I don't lose anyone.

Facts
1. I have a list in an excel sheet or text file, its a list of names of people who have a particular hobby that is taken from a website. Theres around 600 names for each hobby and around 250 hobbies.

2. I have a table in my database for people with all their statistics, height, weight etc. (table 1)

3. I have another table in my database for hobbies (table 2). Each hobby is listed here. I have a field in this table that has a look up for persons name from the people table (table 1). So each hobby record has a field which has a combo box showing all the peoples names (from table 1) and allows me to tick who likes a specific hobby (The list is 600 strong and from table 1 name field). Still with me?

Problem
It would take me hours and hours to fill in each combo box ticking everyone who likes each hobby. But luck would have it I have all the information in a text file/excel file. How can I paste all the names into the combo box of names field in the hobbies table. I've tried but since I've used the look up wizard, it saying its a numerical value type in design view and I must tick the names and not copy and paste them in when datasheet view. Or maybe there is another way for me to copy and paste all this information into the hobbies.PersonsName (but it must also link to the PersonsName Field in table 1 at the end of all this)



Please help, Ive been at this problem for hours and hours and cant figure it out.

[John_G]

Hi -

What you have is a many-to-many relationship. One person can have many hobbies, and one hobby can be practiced by many people. In many-to-many relationships, the need three tables. In your case, one for people, one for hobbies, and a third one to relat the two - let's call it Person_Hobby. Each record in person_hobby will contain a Foreign Key (FK) field identifying a record in the Persons table, another FK field identifying a record in the Hobby table, and other (optional) fields that apply to that particular Person-Hobby pair. The combination of the two FK fields would form the PK of Person_Hobby.

How easy it will be to populate all these depends on the structure of your Excel and text files. Can you give a better idea of what they look like (i.e. what the data structure is)?

HTH

John

[amrut]

Access experts advise against use of look up fields.
Following should help you -
-Suppose PeopleID (Autonumber) is the primary key of your tblPeople and HobbyID (Autonumber) is the primary key of your tblHobbies
-Modify the excel sheet you have ( after keeping a backup ) to include two more columns for PeopleID and HobbyID. You can use the VLookup in excel to fill these fields.Do not forget to Copy & Paste the formula result as values.Delete all other columns except these two.
-In your database, create another table with fields PeopleIDFK(number, Long Integer) and HobbyIDFK(number, Long Integer). Use the External Data>Excel to append the modified excel sheet into this. These FK fields should be related to the respective tables.

[Dal Jeanis]

First, you should have a table for your 600 people, a table for your 250 hobbies, and a junction table that says which people have which hobbies. Here's the minimum layout:

tblPeople
   PersonID   
   PersonName

tblHobbies
   HobbyID
   HobbyName

tblPersonHobby
   PersonID
   HobbyID

Second, don't do this manually, import your excel sheet into a temporary table in your database and then use it to populate the three tables:

tblTempImport
   PersonName
   Hobbyname

Query1:
INSERT INTO tblPeople (PersonName)
SELECT DISTINCT PersonName from tblTempImport;

Query2:
INSERT INTO tblHobbies (HobbyName)
SELECT DISTINCT HobbyName from tblTempImport;

Query3:
INSERT INTO tblPersonHobby (PersonID, HobbyID)
SELECT DISTINCT TP.PersonID, TH.HobbyID
FROM tblPersons AS TP INNER JOIN
     (tblTempImport AS TT
      INNER JOIN 
      tblHobbies AS TH
      ON TH.HobbyName = TT.HobbyName)
ON TP.PersonName = TT.PersonName;

[June7]

Just a point of claification: use of lookups is not advisable in table design but are perfectly acceptable, even necessary, in data entry forms.