Argh! Best dbase normalisation / construction
Argh! Best dbase normalisation / construction
(OP)
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!
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!
RE: Argh! Best dbase normalisation / construction
==================================
adaptive uber info galaxies (bigger, better, faster than agile big data clouds)
RE: Argh! Best dbase normalisation / construction
I will work on presenting the results through queries and reports in that case.
RE: Argh! Best dbase normalisation / construction
CODE
RE: Argh! Best dbase normalisation / construction
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):
CODE -->
If I try and use Provider in a ConcatRelated statement, I am getting duplicate rows again:
CODE -->
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...
RE: Argh! Best dbase normalisation / construction
CODE -->