How do I display a linked table as a Multi-valued field?

[jeremystocker]

I have a table with Client data where each client has a unique client ID and a linked table that contain the client ID with each partner that they were referred to. In this table the each client ID has a record for each referral, making multiple records for some of the client IDs.
What cant seem to do is display this data in a query returning only one result per client ID while showing each partner they have been referred to on a continuous form.
Thanks in advance to any who consider this question.

[twinnyfo]

Create a Main form (Single Form) with the Client Information. Have a Subform (Continuous) with the Partner information, with the Master-Child Relationship based upon the Client ID.

Hope this hepps!

[jeremystocker]

Thank you Twinnyfo, I will try your Idea.

[jeremystocker]

Twinnyfo,
I tried this method an was only able to get on client per page on the forms as access will not allow sub-forms to be embedded into the details portion of a continuous form. I am looking into problematically converting the linked table into a mutilated field for display purposes to overcome the issue I am just not sure how to go about doing it.

Thank you for your suggestion.

[twinnyfo]

1. Why would you want more than one client per page? This is a Form, not a Report. From a workflow standpoint, one would only address one client at a time.

2. I would highly recommend against using a multi-value field for this. There are so many inherent challenges involved with MVFs that I can't imagine a scenario where it is "better" to use a MVF instead of a properly normalized table. Even with a MVF, I'm not sure you would be able to see the multiple partners at one time.

Perhaps you could explain in greater detail why there need to be multiple Clients with multiple partners on one page of a Form? That might be helpful for us to come up with different ideas/solutions.

Thanks!

[jeremystocker]

I am trying to build a tool were my end users can view and search the case IDs and the associated data selected. Most of this data is contained in the main table or a linked table with a one to one relationship. The issue I am having is with a linked table with a one to many relationship.
I also do not like using multivalued fields (MVFs) and this table used to be a MVF in the main Table until I replaced it with this table.
Thank you for your help it is appreciated.

[NeoPa]

Hi Jeremy.

I'm not allowed to link to commercial solutions on here but I would suggest the concept of a treeview control might be what you're looking for.

I would suggest searching for "access treeview" as the Access team in Redmond are still not ready to re-introduce it into the product natively yet.

Actually, I can link to an Access MVP-run conference site that ran over the last two days in which such a facility was demo-ed by Kevin Bell who used to work on the Access team itself. When the videos come out look for the last session ;-)