Linking Subform with multiple child fields to one master field.

[macwiz]

Hello,

I know that my table is poorly designed but I'm trying to link a form to a subform with two fields that could contain the same information. Example:

Record 1
Form Code: 12345
SubForm Code 1: 12345

Record 2
Form Code: 67890
SubForm Code 2: 67890

I'd like to link the subform to the form on both Code 1 and Code 2 such that if code 12345 is selected in the master form, regardless of what column it appears on the subform (code 1 or code 2) it is displayed.

Any ideas? Hope I've communicated the idea clearly enough (and sorry for the poor table design).

Thanks.

 

[HiTechCoach]

Hello,

I know that my table is poorly designed but I'm trying to link a form to a subform with two fields that could contain the same information. Example:

Record 1
Form Code: 12345
SubForm Code 1: 12345

Record 2
Form Code: 67890
SubForm Code 2: 67890

I'd like to link the subform to the form on both Code 1 and Code 2 such that if code 12345 is selected in the master form, regardless of what column it appears on the subform (code 1 or code 2) it is displayed.

Any ideas? Hope I've communicated the idea clearly enough (and sorry for the poor table design).

Thanks.

I would fix the data normalization issue if at all possible.

There is a workaround that I use when working on someone else's .database that is not properly normalized.

1) I use a a UNION query to convert the table into multiple records. This will allow you to do what you need by linking on a single field. This will not be updatable.

or

2) if you will need to edit the data, I append the data into a properly normalized temp/work table. To save the reoced, I myst use VBA code to update the original table.



IMHO, it is a lot less work to fix the table design (only have to do this once) than to have to use a workaround every time you need to work with the data.

 

[macwiz]

Thanks for the suggestions. In this case I am avoiding normalzing the data because it presents much better as 1 record to the user (although I hate what it does to the data).

I was able to differentiate the two codes (essientially one code is used for one category, the other code for another). I then combined with logic to determine when to link the fields based on which code by using the assumed category logic.

Not perfect, but it does the trick.