0

My main Access table contains 95 rows. One column is a name field with a unique name in each field. Two other tables also have a name column, but he name field from each of these tables contain one or more names separated by comma and a space. These tables are different lengths too, one has 99 rows the other has 33.

I need to link the data from these tables to a comprehensive form. To do this I think I want to make a cross tab query using the value in the Main table name field. It will need to search the name field of the other tables to see if one of the listed names match.

Please help.

Mark Stewart
  • 2,046
  • 4
  • 22
  • 32
Brian
  • 1
  • 1) Are the names exactly the same in every case? Matching names can be very difficult otherwise. 2) Can you change the 2nd and 3rd tables to only have one name in the field, with multiple records if necessary? 3) What are you looking for as the columns of the crosstab? I understand you need 1 row per name from the 1st table, but don't understand the columns you expect to see. 4) Are there more names in the 3rd table (with 99 rows) than are in the 1st table (with 95 rows) - and what do you want to do with the extras? – Don George May 10 '16 at 21:36

1 Answers1

1

Are You looking for this:

SELECT * FROM mainTable, Tble99Rows, Tbl33Rows 
WHERE InStr(mainTable.Name, Tble99Rows.Name) AND InStr(mainTable.Name,  Tble33Rows.Name)

? Note it could be inaccurate, for example: it will link records with name Max and Maxine.

For proper Table Joining, Follow Data-Base normalization rules, in our case the First rule: all the attributes in a relation must have atomic domains. The values in an atomic domain are indivisible units.

EDIT:
Please read more: Is storing a delimited list in a database column really that bad?

Community
  • 1
  • 1
marlan
  • 1,485
  • 1
  • 12
  • 18