Re: Massive Summary of data

Hello

well i tried the DISTINCT thing, didnt work, still getting the same
problem. unfortunately i dont have time to keep waiting for an idea to
come to me so i will just have to give up having one massive query.

i think i need to go about it a different way. i think i will have to
have a report with sub reports to get what the boss wants. it would be
nice to have a form that would show this and have the option of
printing it, instead i will have to have a form for each seperate
section of data and a print report button to show a summary of the
data.

thanks anyways.


On Dec 12, 6:17 pm, Salad wrote:
> DawnTreader wrote:
> > 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. :)
>
> Any idea how you could create one row but have 1 IssueID, 2
> ServiceReportIDs, 3 WarrantyClaimIDs, 4 PartOrderIds, 5RMAIDs, and 6
> ProductID?
>
> I would think if these were all 1 to 1 you'd get 1 row only. If you
> have any 1-Many then you need to have the above sub queries return only
> 1 row per ServiceRepID. See if adding Distinct to the SQL statement, in
> the subqueries, provides any relief.
>
> Babyhttp://www.youtube.com/watch?v=dCSSvsC8D1U- Hide quoted text -
>
> - Show quoted text -