Massive Summary of data

[DawnTreader]

hello

i have a huge dataset that i am trying to summarize into one simple report /
pivot table / query.

i have tried everything i know to get it to work, but the biggest problem is
the amount of records that are spat out. i know that the query is working,
the problem is the way it is working.

first here is the layout of the information. i have a total of 7 queries
that look directly at the tables and generate 7 pivot tables that give me
summaries of those areas of data. that works great.

the thing is i want to be able to take a section of that data and group it
by a common table.

Table: tblServiceRep
Field Key
ServiceRepID PK

Query: kqryProducts
ProductID PK
ServiceRepID FK

Query: kqryIssues
IssueID PK
ServiceRepID FK

Query: kqryServiceReports
ServiceReportID PK
ServiceRepID FK

Query: kqryWarrantyClaims
WarrantyClaimID PK
ServiceRepID FK

Query: kqryPartOrder
PartOrderID PK
ServiceRepID FK

Query: kqryPartReturns
RMAID PK
ServiceRepID FK

Query: kqryLoginLog
LoginLogID PK
EmployeeID FK

The service rep table is the center of this data. I want to be able to call
all the other stuff by service rep and the problem is that when I run the
query each record in the secondary tables repeats for each time a record
appears in another table. Is there a way to stop that from happening. I have
already taken care of the joins. I don’t know what it is called but the arrow
points away from the service rep table in each join. currently i have only
one field from each query, just to see if i can get it to work, but when the
basic summary works i will be adding in other fields from each query.

Here is the SQL for those who are interested in seeing it:

SELECT tblServiceReps.ServiceRepID, tblServiceReps.Name, kqryIssues.IssueID,
kqryServiceReports.ServiceReportID, kqryWarrantyClaimTotals.WarrantyClaimID,
kqryPartsOrders.PartOrderId, kqryRMAs.RMAID, kqryProducts.ProductID,
kqryUsageLog.LoginLogId
FROM ((((((tblServiceReps LEFT JOIN kqryIssues ON
tblServiceReps.ServiceRepID = kqryIssues.ServiceRepID) LEFT JOIN
kqryPartsOrders ON tblServiceReps.ServiceRepID =
kqryPartsOrders.ServiceRepID) LEFT JOIN kqryProducts ON
tblServiceReps.ServiceRepID = kqryProducts.ServiceRepID) LEFT JOIN kqryRMAs
ON tblServiceReps.ServiceRepID = kqryRMAs.ServiceRepID) LEFT JOIN
kqryServiceReports ON tblServiceReps.ServiceRepID =
kqryServiceReports.ServiceRepID) LEFT JOIN kqryUsageLog ON
tblServiceReps.ServiceRepID = kqryUsageLog.ServiceRepID) LEFT JOIN
kqryWarrantyClaimTotals ON tblServiceReps.ServiceRepID =
kqryWarrantyClaimTotals.ServiceRepID
GROUP BY tblServiceReps.ServiceRepID, tblServiceReps.Name,
kqryIssues.IssueID, kqryServiceReports.ServiceReportID,
kqryWarrantyClaimTotals.WarrantyClaimID, kqryPartsOrders.PartOrderId,
kqryRMAs.RMAID, kqryProducts.ProductID, kqryUsageLog.LoginLogId;

Any thoughts or ideas, or even SQL would be appreciated.

 

[DawnTreader]

well...

i guess i have stumped everyone, or that it cant be done.

so i am moving to have a form that shows each section of data and then a
report with subreports that show the summary of data.