SQL for multiple tables, inner joins and groups

R

rjagodka

Joined
Mar 7, 2011
Messages
1
Reaction score
0
Hello all,

I have an Access database that I am writing a query for. I have 4 tables I need to join:

tblCompany
CompanyID
etc.

tblEventDtl
EvntDtlID
CompanyID
EvntDtlName
etc.

tblEventAttend
EventAttendID
CompanyID
EvntDtlID
etc.

tblTechnical
TechnicalID
CompanyID
etc.


I want to list the companies on a page so that each company is listed only once, and each event attendee is listed (with details about the event), and each technical recipient is listed (with details about the technical).

My first try at the SQL was:

Select tblCompany.CompanyName,
tblCompany.CompanyExportStatus,
tblCompany.CompanyExportCountry,
tblCompany.CompanyImportStatus,
tblCompany.CompanyImportCountry,
tblCompany.CompanyProducts,
tblEventAttend.EventAttndFirstName,
tblEventAttend.EventAttndLastName,
tblEventDtl.EvntDtlName,
tblEventDtl.EvntDtlDate,
tblEventDtl.EvntDtlCity,
tblEventDtl.EvntDtlLocation,
tblEventDtl.EvntDtlHours
From (tblCompany Inner Join
tblEventAttend On tblCompany.CompanyID = tblEventAttend.CompanyID)
Inner Join
tblEventDtl On tblEventDtl.EvntDtlID = tblEventAttend.EvntDtlID
Group By tblCompany.CompanyName,
tblCompany.CompanyExportStatus,
tblCompany.CompanyExportCountry,
tblCompany.CompanyImportStatus,
tblCompany.CompanyImportCountry,
tblCompany.CompanyProducts,
tblEventAttend.EventAttndFirstName,
tblEventAttend.EventAttndLastName,
tblEventDtl.EvntDtlName,
tblEventDtl.EvntDtlDate,
tblEventDtl.EvntDtlCity,
tblEventDtl.EvntDtlLocation,
tblEventDtl.EvntDtlHours
Order By tblCompany.CompanyName ASC,
tblEventAttend.EventAttndLastName ASC,
tblEventAttend.EventAttndFirstName ASC,
tblEventDtl.EvntDtlName ASC,
tblEventDtl.EvntDtlDate ASC

This worked fine in that it gave me (with a bit of cleaning) a list of each company, with each respective employee's training sessions, as follows.

Clients1.jpg


My next try at the SQl was to get the technical clients listed by company as well (with session details under them):

Select tblCompany.CompanyName,
tblCompany.CompanyExportStatus,
tblCompany.CompanyExportCountry,
tblCompany.CompanyImportStatus,
tblCompany.CompanyImportCountry,
tblCompany.CompanyProducts,
tblEventAttend.EventAttndFirstName,
tblEventAttend.EventAttndLastName,
tblEventDtl.EvntDtlName,
tblEventDtl.EvntDtlDate,
tblEventDtl.EvntDtlCity,
tblEventDtl.EvntDtlLocation,
tblEventDtl.EvntDtlHours,
tblTechnical.TechnicalContactFirst,
tblTechnical.TechnicalContactLast,
tblTechnical.TechnicalDate,
tblTechnical.TechnicalHours,
tblTechnical.TechnicalRequest,
tblTechnical.TechnicalOutcome,
tblTechnical.TechnicalOutcomeDesc
From ((tblCompany
Inner Join tblEventAttend On tblCompany.CompanyID = tblEventAttend.CompanyID)
Inner Join tblEventDtl On tblEventDtl.EvntDtlID = tblEventAttend.EvntDtlID)
Inner join tblTechnical ON tblCompany.CompanyID = tblTechnical.CompanyID
Group By tblCompany.CompanyName,
tblCompany.CompanyExportStatus,
tblCompany.CompanyExportCountry,
tblCompany.CompanyImportStatus,
tblCompany.CompanyImportCountry,
tblCompany.CompanyProducts,
tblEventAttend.EventAttndFirstName,
tblEventAttend.EventAttndLastName,
tblEventDtl.EvntDtlName,
tblEventDtl.EvntDtlDate,
tblEventDtl.EvntDtlCity,
tblEventDtl.EvntDtlLocation,
tblEventDtl.EvntDtlHours,
tblTechnical.TechnicalContactFirst,
tblTechnical.TechnicalContactLast,
tblTechnical.TechnicalDate,
tblTechnical.TechnicalHours,
tblTechnical.TechnicalRequest,
tblTechnical.TechnicalOutcome,
tblTechnical.TechnicalOutcomeDesc
Order By tblCompany.CompanyName ASC,
tblEventAttend.EventAttndLastName ASC,
tblEventAttend.EventAttndFirstName ASC,
tblEventDtl.EvntDtlName ASC,
tblEventDtl.EvntDtlDate ASC

But this was the result:

Clients2.jpg


Not sure what to do. The Grouping and joins provide all of the data but do not seem to allow me to group them properly by Company, then by Participant (with detail records).

Any ideas on this one? Thank you very much for your help and advice...
 
Back
Top