Linking Tables - Adding Rows

[Burger]

How do I Link Tables, so that new rows are added?

Hi,

I am trying to figure out how to link 2 tables to add rows based on corresponding data...below is an example...

First table contains general info...
row 1: column 1 = John Doe column 2 = Sports
row 2: coulmn 2 = Bob Smith column 2 = Music

Second table contains detailed info
column 1 = Sports
row 1= Football row 2 = Baseball row 3 = Soccer

Expected Result:

I would like multiple rows to show for each person linking all of detailed info...
row 1: column 1 = John Doe Column 2 = Football
row 2: column 1 = John Doe Column 2 = Soccer
row 3: column 1 = John Doe Column 2 = Baseball

Thanks,

 

[neileg]

I'm not clear on what the issue is here. However, this kind of relationship is often handled in a form/subform setup. Access handles the relationship via the parent/child process that undelies the form/subform structure.

 

[LGOF62]

join the union

I find your description unclear too but i assume that you have a column for each sport in the first table and a whole list of names with an entry in the relevant column, dus the J. doe record would have an entry in three of the x columns.

This is indeed a horrible format and impossible to work with. To convert this to a normalised table format, I would use a union query...
Each section of the union query would query the name column and one other column. Add a column to store "sport type" and populate this with the Title of the column that you are working with; Effectively you are saying...
- give me a list of all the people doing football
union all
- give me a list of all the people doing Basketball

etc.

greetings,

 

[Burger]

Thank you for responding so quickly. Let me be a little more clear on my columns/rows and my goal. I can change the format in excel as well.

I have 750 contacts. Each contact has a "correspondance" type. The second table is a list of 100 "correspondance" types. Each one of these "correspondance" types has between 1 and 8 "mail-out" types. I need to join these two tables, so that if a contact has a certain correspondance type, it will add a row for that contact for each "mail-out" type.

So, I need to join the 'type' from the first table, to the 'type' from the second table, to build additional rows to show the contact with each "mail-out" type as new rows.

 

[neileg]

Many to many relationships like the one you have between contacts and correspondance types need to be handled via a junction table. Your mailout table should be the junction table, here. Do a search on 'many to many' and 'junction table' in these forums.

 

[Burger]

Thanks for the info. I created a junction table, and I linked both of my original tables to this table in the relationships section, with the appropriate values and naming convention. The Junction table is completely empty though. Do you know how the new table will become populated with the values?

 

[neileg]

It depends on how you intend to caputure the data. I would expect to use a form boun to the junction table that uses two combo boxes to look up the values in the two outer tables, plus other controls for whatever other information you want to hold in the table.

 

[Burger]

I am having a difficult time creating new rows. I think that my data format may be an issue...I have all of the data that I need for each contact now, but I need to transpose the data to additional rows. Below is how my data appears. I need 3 new rows for Jane Doe, each one showing the attributes associated with her.

-Ryan

Name : Type : Attribute 1 : Attribute 2 : Attribute 3

Jane Doe : E-mail : Financial Statements : Distribution Letters : Contributions
John Doe : Fax : Quarterly Reports : Tax documents
Steve Smith : E-mail : Financial Statements : Distribution Letters : Contributions