Argh! Best dbase normalisation / construction 2

[MrMode]

I need to record 'interactions' between delegates and providers.

There will be multiple delegates, and potentially multiple providers (and provider staff).

I need to be able to report on all the delegates that attended a given interaction and the providers that were involved.

The issue I have come across is... If I have a table for the interaction, a table for the delegates and a table for the suppliers, I will get duplicate rows for each of the delegates for each of the providers that are returned by the query...

It seems I will get something like this as a result:
Interaction / Delegate / Provider
Event1 / Mr A / Provider A
Event1 / Mr A / Provider B
Event1 / Mr B / Provider A
Event1 / Mr B / Provider B

When what I am after is something more like this:
Interaction / Delegate / Provider
Event1 / Mr A / Provider A, Provider B
Event1 / Mr B / Provider A, Provider B

What is the best way to normalise the structure / get these kinds of results?

Any help, most appreciated!

 

[johnherman]

Your design is perfectly normalized. What you are doing by grouping providers within the Event and Person is denormalizing the data. So, my advice is to keep the normalized design and work on (or redo) the report design, or summary table design.

==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)

 

[MrMode]

Great, thanks.

I will work on presenting the results through queries and reports in that case.

 

[MajP]

look at FAQ701-4233. Then your query is

SELECT DISTINCT tblInteractions.Interaction, tblInteractions.Delegate, Concatenate("Select Provider from tblInteractions where Interaction = '" & [interaction] & "' And Delegate = '" & [Delegate] & "'") AS Providers
FROM tblInteractions;

 

[MrMode]

I have used a similar solution from Allen Browne where he has a Function called ConcatRelated. I am having one issue...

I can pull the Provider ID's into a concatenated field (Provider). SUCCESS

I need to get the names of these individuals from a linked table called tblProviderPeople.

There are 5 tables I have tried to represent the flow between them below.
tblProviderPeople --> tblInteractionProvider --> tblInteraction <-- tblInteractionSupplier <--tblSupplerPeople

I can create another field and get the first name in the string from the newly concatenated field (Providers):

SELECT tblInteraction.InteractionName, tblInteraction.InteractionDate, tblInteraction.Location, tblSupplierPeople.[Company Name], ConcatRelated("ProviderPeopleID","tblInteractionProvider","InteractionID = " & [InteractionLinkID]) AS Provider, [tblProviderPeople].[ContactTitle] & " " & [tblProviderPeople].[FirstName] & " " & [tblProviderPeople].[Surname] AS ProviderNames
FROM tblSupplierPeople INNER JOIN (tblProviderPeople INNER JOIN ((tblInteraction INNER JOIN tblInteractionProvider ON tblInteraction.InteractionLinkID = tblInteractionProvider.InteractionID) INNER JOIN tblInteractionSupplier ON tblInteraction.InteractionLinkID = tblInteractionSupplier.InteractionID) ON tblProviderPeople.ProviderPeopleID = tblInteractionProvider.ProviderPeopleId) ON tblSupplierPeople.[Company ID] = tblInteractionSupplier.CompanyID;

If I try and use Provider in a ConcatRelated statement, I am getting duplicate rows again:

SELECT tblInteraction.InteractionName, tblInteraction.InteractionDate, tblInteraction.Location, tblSupplierPeople.[Company Name], ConcatRelated("ProviderPeopleID","tblInteractionProvider","InteractionID = " & [InteractionLinkID]) AS Provider, ConcatRelated("[tblProviderPeople].[ContactTitle] & "" "" & [tblProviderPeople].[FirstName] & "" "" & [tblProviderPeople].[Surname]","tblProviderPeople","ProviderPeopleID IN " & "(" & [provider] & ")") AS ProviderNames
FROM tblSupplierPeople INNER JOIN (tblProviderPeople INNER JOIN ((tblInteraction INNER JOIN tblInteractionProvider ON tblInteraction.InteractionLinkID = tblInteractionProvider.InteractionID) INNER JOIN tblInteractionSupplier ON tblInteraction.InteractionLinkID = tblInteractionSupplier.InteractionID) ON tblProviderPeople.ProviderPeopleID = tblInteractionProvider.ProviderPeopleId) ON tblSupplierPeople.[Company ID] = tblInteractionSupplier.CompanyID;

How would I get all the names concatenated into a field without creating duplicate rows? I am guessing that somehow I need to nest the id step inside the names step, but not sure how to achieve it...

 

[MrMode]

I think I may have cracked it?! I will keep testing it...

SELECT tblInteraction.InteractionName, tblInteraction.InteractionDate, tblInteraction.Location, tblSupplierPeople.[Company Name], ConcatRelated("[tblProviderPeople].[ContactTitle] & "" "" & [tblProviderPeople].[FirstName] & "" "" & [tblProviderPeople].[Surname]","tblProviderPeople","ProviderPeopleID IN " & "(" & ConcatRelated("ProviderPeopleID","tblInteractionProvider","InteractionID = " & [InteractionLinkID]) & ")") AS ProviderNames
FROM tblSupplierPeople INNER JOIN (tblProviderPeople INNER JOIN ((tblInteraction INNER JOIN tblInteractionProvider ON tblInteraction.InteractionLinkID = tblInteractionProvider.InteractionID) INNER JOIN tblInteractionSupplier ON tblInteraction.InteractionLinkID = tblInteractionSupplier.InteractionID) ON tblProviderPeople.ProviderPeopleID = tblInteractionProvider.ProviderPeopleId) ON tblSupplierPeople.[Company ID] = tblInteractionSupplier.CompanyID
GROUP BY tblInteraction.InteractionName, tblInteraction.InteractionDate, tblInteraction.Location, tblSupplierPeople.[Company Name], ConcatRelated("[tblProviderPeople].[ContactTitle] & "" "" & [tblProviderPeople].[FirstName] & "" "" & [tblProviderPeople].[Surname]","tblProviderPeople","ProviderPeopleID IN " & "(" & ConcatRelated("ProviderPeopleID","tblInteractionProvider","InteractionID = " & [InteractionLinkID]) & ")");