Subform Relationship question

[fb63]

I have a master form with location field that is pulling a subform based on a table that used yes/no fields for each location. I was trying to link by converting all yes's into the locations 3 letter code and concatenating them into one field. But Can I use the *location* from master to the VClocation field which holds all the concatenated locaitons values. As the table with the yes/no fields is linked from another database, I am not at this time allowed to modify it.

 

[dhookom]

It is not at all clear what values might be in your main and subforms. The link master/child requires exact value matches.

If you need more help, how about providing some table structures, sample data, and how you want to relate the main and subforms.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

If I understand correctly

The main table has a location name

The sub table in not normalized and it has columns representing locations and these are boolean fields.

tblSelectedLocations
ID  locationOne locationTwo locationThree
1   Yes         No          No
2   Yes	        Yes         No
3   No          Yes         Yes
4   Yes         No          No
5   No          Yes         No
6   No          Yes        Yes

You then need to use a union query to normalize your data

SELECT 
  tblSelectedLocations.ID, 
  "Location One" AS LocationName
FROM 
  tblSelectedLocations
WHERE locationOne=True
Union
SELECT 
  tblSelectedLocations.ID, 
  "Location Two" AS LocationName
FROM 
  tblSelectedLocations
WHERE locationTwo=True
Union
SELECT 
  tblSelectedLocations.ID, 
  "Location Three" AS LocationName
FROM 
  tblSelectedLocations
WHERE locationThree=True
Order by 1

ID   LocationName
1     Location One
2     Location One
2     Location Two
3     Location Three
3     Location Two
4     Location One
5     Location Two
6     Location Three
6     Location Two

now you can link by name

 

[fb63]

Thanks, MajP, that is the exact issue that I have. I will try that to resolve my issue

 

[MajP]

You may want to consider fixing your tables to be normalized. that will solve a lot of problems. However if fixing the db is impractical, the Union query is a good trick to take non-normal tables and normalize the data.