×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

*
*
*
*

Thanks. We have received your request and will respond promptly.

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Argh! Best dbase normalisation / construction
2

thread700-1741369

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!

RE: Argh! Best dbase normalisation / construction

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)


RE: Argh! Best dbase normalisation / construction

(OP)
Great, thanks.

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

RE: Argh! Best dbase normalisation / construction

look at FAQ701-4233: How to concatenate multiple child records into a single value. Then your query is

CODE

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

RE: Argh! Best dbase normalisation / construction

(OP)
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):

CODE --> 

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:

CODE --> 

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...


RE: Argh! Best dbase normalisation / construction

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

CODE --> 

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]) & ")");

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login
Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close