UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Complex Query With Parent, Child, Grandchild & Crosstab From Child, Access 2010    
 
   
larai
post Nov 28 2017, 09:23 PM
Post#1



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hello,

Not sure if all the cost types can be done in a single query. Eventually this will be the recordsource for a report. I have a query in the attached database called qActLogCostTotals.


I can tell you off the bat that my labor & Parts totals aren't calculating correctly. The main form is fActivityLog (with all the subforms).

Parent table is ActivityLog
Child tables: Costs, Rework, Activity Detail
Activity Detail child tables: ActDetParts & ActDetResources

I would like a query that calculates for each ROW in ActivityLog:
ActivityID, Project Name, NamedIssue, Sum of Cost in Cost table by Cost Type (crosstab), Sum of Cost in Rework table, Sum of Cost of all ActDetParts where ActDetID = ActivityDetail.ActDetID rolls up to ActivityID, Sum of Cost of all ActDetResources where ActDetID = ActivityDetail.ActDetID rolls up to ActivityID.

So the reason for costs at different levels is because some costs are tracked by the activity performed, whereas others don't need to be grouped under the activity.

I'll also need another variant where the costs are tracked not by the ActivityID from the ActivityLog, but from the different NamedIssue.values
I know that it doesn't work mathmatically, but that's fine. As an example, some ActivityLog records have multiple named issues. So the total cost breakdown will be duplicated where there is more than 1 named issue for an activitylog record.


Thank you!
Attached File(s)
Attached File  DCQAOps___Copy.zip ( 417.71K )Number of downloads: 4
 
Go to the top of the page
 
Doug Steele
post Nov 29 2017, 01:21 PM
Post#2


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I think the issue may be that you're doing aggregation on fields that don't exist in the tables being aggregated.

Try creating subqueries that return the sums correctly, then join the other tables to those subqueries. (Note that this doesn't mean you need to save the subqueries separately: you should be able to create a single query that includes the subqueries in it, but you'll have to work directly with the SQL)

--------------------
Go to the top of the page
 
larai
post Nov 29 2017, 02:44 PM
Post#3



Posts: 1,064
Joined: 8-February 02
From: California, USA


So I created 2 subqueries to roll up costs for the grandchildren (using child/grandchild), then tried to add both those queries to the parent table and now I'm getting duplicate values. Completely unsure how to get the 2 subqueries to sum correctly to the parent table.

Here is the query to rollup labor:
SELECT ActivityDetail.ActivityID, ActivityDetail.DCLoc, Sum(ActDetResources.Cost) AS Labor
FROM ActivityDetail LEFT JOIN ActDetResources ON ActivityDetail.ActDetID = ActDetResources.ActDetID
GROUP BY ActivityDetail.ActivityID, ActivityDetail.DCLoc;

This is the query to rollup part cost (scrap)
SELECT ActivityDetail.ActivityID, ActivityDetail.DCLoc, Sum(ActDetParts.Cost) AS Scrap
FROM ActivityDetail LEFT JOIN ActDetParts ON ActivityDetail.ActDetID = ActDetParts.ActDetID
GROUP BY ActivityDetail.ActivityID, ActivityDetail.DCLoc;


Now I combine the 2 queries and join to the parent:
SELECT ActivityLog.ActivityID, qActivityID_Labor.DCLoc, Sum(qActivityID_Labor.Labor) AS SumOfLabor, Sum(qActivityID_Scrap.Scrap) AS SumOfScrap
FROM (ActivityLog INNER JOIN qActivityID_Labor ON ActivityLog.ActivityID = qActivityID_Labor.ActivityID) INNER JOIN qActivityID_Scrap ON ActivityLog.ActivityID = qActivityID_Scrap.ActivityID
GROUP BY ActivityLog.ActivityID, qActivityID_Labor.DCLoc;
Go to the top of the page
 
Doug Steele
post Nov 29 2017, 04:17 PM
Post#4


UtterAccess VIP
Posts: 21,498
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Obviously I don't know what you're trying to model, but I don't understand why you included ActivityDetail.DCLoc in the two intermediary queries, since that field isn't used in the Join (and wasn't in your original query).

I removed that field from the intermediary queries and combined it all into one query:

CODE
SELECT ActivityLog.ActivityID,
  Sum(qActivityID_Labor.Labor) AS SumOfLabor, Sum(qActivityID_Scrap.Scrap) AS SumOfScrap
FROM (ActivityLog INNER JOIN
(SELECT ActivityDetail.ActivityID, Sum(ActDetResources.Cost) AS Labor
FROM ActivityDetail LEFT JOIN ActDetResources ON ActivityDetail.ActDetID = ActDetResources.ActDetID
GROUP BY ActivityDetail.ActivityID) AS qActivityID_Labor
ON ActivityLog.ActivityID = qActivityID_Labor.ActivityID) INNER JOIN
(SELECT ActivityDetail.ActivityID, Sum(ActDetParts.Cost) AS Scrap
FROM ActivityDetail LEFT JOIN ActDetParts ON ActivityDetail.ActDetID = ActDetParts.ActDetID
GROUP BY ActivityDetail.ActivityID) AS qActivityID_Scrap
ON ActivityLog.ActivityID = qActivityID_Scrap.ActivityID
GROUP BY ActivityLog.ActivityID;

That resulted in:

CODE
ActivityID  SumOfLabor  SumOfScrap
         3       681.1     $135.00
         5                 $102.66
         6

Does that look any better?

--------------------
Go to the top of the page
 
larai
post Nov 29 2017, 04:55 PM
Post#5



Posts: 1,064
Joined: 8-February 02
From: California, USA


Hello Doug,

Thanks for the query, it works great and I do need it; however, I realized I also need the costs grouped by the facility which incurred the cost, hence the DCLoc in my group by. That, incidentally is what made the combined query explode (multiple values). I really still am not sure why that is causing the values to multiply. I tried adding it back into the query you fixed, but no matter how I set my joins it keeps multiplying by the # of DCs, regardless of whether or not there's any costs at that DC.
Go to the top of the page
 
larai
post Nov 29 2017, 05:08 PM
Post#6



Posts: 1,064
Joined: 8-February 02
From: California, USA


Whew, well that was a battle, but I think I've got it now; aside from figuring out how to get rid of rows where there's no labor or scrap for that location (but that's not a huge issue)

SELECT ActivityLog.ActivityID, ActivityDetail.DCLoc, Sum(qActivityID_Labor_1.Labor) AS SumOfLabor, Sum(qActivityID_Scrap_1.Scrap) AS SumOfScrap
FROM ActivityLog RIGHT JOIN ((ActivityDetail INNER JOIN (SELECT ActivityDetail.ActivityID, ActivityDetail.DCLoc, Sum(ActDetParts.Cost) AS Scrap
FROM ActivityDetail LEFT JOIN ActDetParts ON ActivityDetail.ActDetID = ActDetParts.ActDetID
GROUP BY ActivityDetail.ActivityID, ActivityDetail.DCLoc) AS qActivityID_Scrap_1 ON (ActivityDetail.DCLoc = qActivityID_Scrap_1.DCLoc) AND (ActivityDetail.ActivityID = qActivityID_Scrap_1.ActivityID)) INNER JOIN (SELECT ActivityDetail.ActivityID, ActivityDetail.DCLoc, Sum(ActDetResources.Cost) AS Labor
FROM ActivityDetail LEFT JOIN ActDetResources ON ActivityDetail.ActDetID = ActDetResources.ActDetID
GROUP BY ActivityDetail.ActivityID, ActivityDetail.DCLoc) AS qActivityID_Labor_1 ON (ActivityDetail.DCLoc = qActivityID_Labor_1.DCLoc) AND (ActivityDetail.ActivityID = qActivityID_Labor_1.ActivityID)) ON ActivityLog.ActivityID = ActivityDetail.ActivityID
GROUP BY ActivityLog.ActivityID, ActivityDetail.DCLoc;


Thanks Doug for steering me in the right direction.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 12:14 AM