Selecting Data from 3 Tables

[jackyaz]

Hi there,

I'm struggling to get a query to select the right number of records based on field selections from 3 tables.

Table 1 (tblEvents) has the below list of fields:

Event ID
Client ID
Location ID
Consultant ID
Work Requested By
Other work
Date Work Completed
Rate ID
Rate Amount
Any other details
Telephone Call
Telephone Incoming/Outgoing
Telephone Time (mins)
Email
Email Incoming/Outgoing
Email Time (mins)
Meeting
Meeting - preparation time hrs
Meeting - preparation time mins
Meeting - meeting time hrs
Meeting - meeting time mins
Meeting - meeting notes write up hrs
Meeting - meeting notes write up mins
Meeting - include travel?
Meeting - mileage
Meeting - car park charges/tolls
Meeting - charge amount
Meeting - public transport
Meeting - one way
Meeting - return
Meeting - public transport amount
Meeting - include hotel/subsistence?
Meeting - hotel amount
Project or Specific Tasks
Project Time hrs
Project Time mins
Proj - include travel?
Proj - mileage
Proj - car park charges/tolls
Proj - charge amount
Proj - public transport
Proj - one way
Proj - return
Proj - public transport amoount
Proj - include hotel/subsistence
Proj - hotel amount
Training
Training - preparation time hrs
Training - preparation time mins
Training - on site hrs
Training - on site mins
Training - follow-up hrs
Training - follow-up mins
Training - include travel?
Training - mileage
Training - car park charges/tolls
Training - charge amount
Training - public transport
Training - one way
Training - return
Training - public transport amount
Training - include hotel/subsistence?
Training - hotel amount
Credit Report
Type
Other
Other time hrs
Other time mins
Subject
Advice given/work undertaken
Meeting - custom
Meeting - custom amount
Meeting - custom notes
Training - custom
Training - custom amount
Training - custom notes
Proj - custom
Proj - custom amount
Proj - custom notes
Meeting - custom time
Training - custom time
Proj - custom time

Table 2 (tblNonClient) has the below list of fields:

Event ID
Consultant ID
Date Work Completed
Reading Trade Press
Reading hours
Reading mins
Research
Research hours
Research mins
Recruitment
Recruitment hours
Recruitment mins
Training
Training hours
Training mins
Meetings
Meetings hours
Meetings mins
Conferences
Conferences hours
Conferences mins
Other
Other hrs
Other mins
Streamline Development
Streamline hours
Streamline mins
Marketing
Newsletter hours
Newsletter mins
Prospecting
Prospecting hours
Prospecting mins
Subject
Newsletters
Writing articles
Updates to websites
Writing hours
Writing mins
Updates hours
Updates mins
Mailings
Mailings hours
Mailings mins
Marketother
Marketother hours
Marketother mins
Other details
Meeting location meeting room
Meeting location other
Location other
Training location meeting room
Training location other
Location other train
Meeting attendees
Training attendees

Table 3 (tblConsultants) has just 2 fields:

Consultant ID
Consultant Name




Now what I am trying to do is select all the fields from each of the two tables, and group them underneath the corresponding Consultant ID. Whenever I try making a query for this, I either only end up with the records where a given Consultant appears in both, or duplicates where the Consultant appears on both. Can someone help me with this?

Regards,

Jack

 

[Mr Smin]

Could you output the results to a report to show each event and billing items for each consultant?
As a query result, as far as I know, you would expect to see each consultant's name as many times as there were events they were involved in.
So if consultant 1, named Bob, was involved in events 1, 12 and 20 you would get 3 rows returned for Bob.
Bob, Event 1, etc
Bob, Event 12, etc
Bob, Event 20, etc

But in a report you can have Consultant as a section header and then as many Event detail entries as required. Same query but more readable end product.

 

[jackyaz]

Hi Mr Smin,

I was trying to build the query so it could be used in a report. The same duplicate issue arises even when I set the Consultant name as a section header, so I thought I must be doing the query wrong!

Regards,

Jack

 

[Mr Smin]

I'm pretty sure your query is right and you need to tweak the report.
Try right-clicking a header in the report and choose 'sorting and grouping' then take a look at the options there.
Define 'Consultant' as a grouping item then you probably need group header=yes, group footer=no, group on=each value, group interval=1.
in that header, put the consultant's name field,
then put the 'Event' fields in the detail section below the header.

Hope that makes sense (and hope it works!)

 

[jackyaz]

Ok I'll give that a go.

Quickly though, what relationships do I need in the query? I assumed 2 outer joins between the tblEvents and tblNonclient and the tblConsultants would be right, but I get an error about ambiguous outer joins???

 

[Mr Smin]

I'm a bit hazy about the relationships. Is it one consultant to many events, and _separately_ one consultant to many tblNonClient?

Or is it one consultant to many events and one event to many tblNonClient?

If the latter, you might want to go for a sub-report.

Ambiguous join errors can be resolved by right-clicking the join lines in the upper part of the query editor and investigating the settings there.

I'm hoping someone will come along soon who can explain this better than I can!

 

[jackyaz]

Hi Mr Smin,

One consultant can have many records in tblEvents and many records in tblNonclient.

Jack

 

[Mr Smin]

I think it should look like
Header:Consultant
______Detail:Event
___________SubReport:tblNonClient

Since it seems each tblNonClient row is associated with both a Consultant and an Event and I'm guessing there can be any number of tblNonClient rows associated with each Event.

Disclaimer: There may be better options that I haven't thought of!

 

[jackyaz]

Hi there,

The Event ID column in each of the tblEvents and tblNonClients are ID numbers for the rows in each individual column. Theyre not linked in any way, perhaps my fault for calling them the same thing! With a subreport, would it bunch all the tblNonClient entries at the end of the section?

Regards,

Jack