Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Argh! Best dbase normalisation / construction 2

Status
Not open for further replies.
MrMode

MrMode

Technical User
Aug 28, 2003
195
GB
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!
 
Sort by date Sort by votes
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)


 
Upvote 0 Downvote
Great, thanks.

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

 
Upvote 0 Downvote
look at FAQ701-4233. 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;

 
Upvote 0 Downvote
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...


 
Upvote 0 Downvote
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]) & ")");
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

puforee
  • Locked
  • Question
Best way to setup tables for multible related data sets
Replies
1
Views
542
dhookom
dhookom
Moss100
  • Locked
  • Question
Property and Tenant tables - best approach for relationship 4
Replies
6
Views
311
dhookom
dhookom
Moss100
  • Locked
  • Helpful tip
Best table structure for a log table
Replies
1
Views
270
MajP
MajP
Moss100
  • Locked
  • Question
Best table approach - recording room data 2
Replies
3
Views
207
Moss100
Moss100
MarshaP
  • Locked
  • Question
Importing a dbase file in 64-bit Access 2010
Replies
1
Views
217
lameid
lameid

Part and Inventory Search

Sponsor

Back
Top