Heavy data munching - is there a more efficient way of doing this?

Hello All

i have a report i am trying to generate, where at specific intervals i
need to total sale values of parts for the rebuilds of the life of an
"engine". basically i have had to create a couple of levels of queries
and a table to create the "matrix" of dollar values.

the first query looks like so:

SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.onekmaint ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"2 5000 Hr Rebuild" AS IntervalDescription,
5000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.FiveKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"3 10000 Hr Rebuild" AS IntervalDescription,
10000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.TenKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"4 15000 Hr Rebuild" AS IntervalDescription,
15000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.FtKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"5 20000 Hr Rebuild" AS IntervalDescription,
20000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.TwKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"6 25000 Hr Rebuild" AS IntervalDescription,
25000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.TwFKThouRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"7 30000 Hr Rebuild" AS IntervalDescription,
30000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.ThKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"8 35000 Hr Rebuild" AS IntervalDescription,
35000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.ThFKRebuild ) = "y" ) )
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"9 40000 Hr Rebuild" AS IntervalDescription,
40000 AS IntervalLink
FROM tblProductPartList
WHERE ( ( ( tblProductPartList.ProductID ) = Forms!frmManageAssets!
subProductID ) AND ( ( tblProductPartList.FtyKRebuild ) = "y" ) );

that gives me all the parts in thier proper interval. the second query
looks like this:

TRANSFORM Sum([SetCost]*[QTY]) AS TotalSetCost
SELECT qryRebuildReportStacked.ProductID,
qryRebuildReportStacked.IntervalDescription,
qryRebuildReportStacked.IntervalLink
FROM (qryRebuildReportStacked LEFT JOIN dbo_PART ON
qryRebuildReportStacked.IMWPartNumberID = dbo_PART.ID) LEFT JOIN
tblMasterPartList ON qryRebuildReportStacked.IMWPartNumberID tblMasterPartList.ID
GROUP BY qryRebuildReportStacked.ProductID,
qryRebuildReportStacked.IntervalDescription,
qryRebuildReportStacked.IntervalLink
ORDER BY qryRebuildReportStacked.IntervalDescription
PIVOT qryRebuildReportStacked.IntervalLink In
("1000","5000","10000","15000","20000","25000","30000","35000","40000");

that gives me a matrix that gives a summed dollar value at the right
interval. the next step is to make the matrix mean something on the
report and to do that i hooked it up with a table that shows the
lifetime number of intervals with the appropriate dollar values. that
looks like this:

SELECT DISTINCTROW qryRebuildCostingCrossTab.ProductID,
tblRebuildIntervals.HoursID, tblRebuildIntervals.IntervalHours,
qryRebuildCostingCrossTab.[1000], qryRebuildCostingCrossTab_1.[5000],
qryRebuildCostingCrossTab_2.[10000], qryRebuildCostingCrossTab_3.
[15000], qryRebuildCostingCrossTab_4.[20000],
qryRebuildCostingCrossTab_5.[25000], qryRebuildCostingCrossTab_6.
[30000], qryRebuildCostingCrossTab_7.[35000],
qryRebuildCostingCrossTab_8.[40000]
FROM qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_8 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_7 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_6 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_5 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_4 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_3 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_2 RIGHT
JOIN (qryRebuildCostingCrossTab AS qryRebuildCostingCrossTab_1 RIGHT
JOIN (qryRebuildCostingCrossTab RIGHT JOIN tblRebuildIntervals ON
qryRebuildCostingCrossTab.IntervalLink = tblRebuildIntervals.rOneKhr)
ON qryRebuildCostingCrossTab_1.IntervalLink tblRebuildIntervals.rFiveKhr) ON
qryRebuildCostingCrossTab_2.IntervalLink tblRebuildIntervals.rTenKhr) ON
qryRebuildCostingCrossTab_3.IntervalLink tblRebuildIntervals.rFifteenKhr) ON
qryRebuildCostingCrossTab_4.IntervalLink tblRebuildIntervals.rTwentyKhr) ON
qryRebuildCostingCrossTab_5.IntervalLink tblRebuildIntervals.rTwentyFiveKhr) ON
qryRebuildCostingCrossTab_6.IntervalLink tblRebuildIntervals.rThirtyKhr) ON
qryRebuildCostingCrossTab_7.IntervalLink tblRebuildIntervals.rThiryFiveKhr) ON
qryRebuildCostingCrossTab_8.IntervalLink tblRebuildIntervals.rFortyKhr
WHERE (((tblRebuildIntervals.IntervalHours)<>0))
GROUP BY qryRebuildCostingCrossTab.ProductID,
tblRebuildIntervals.HoursID, tblRebuildIntervals.IntervalHours,
qryRebuildCostingCrossTab.[1000], qryRebuildCostingCrossTab_1.[5000],
qryRebuildCostingCrossTab_2.[10000], qryRebuildCostingCrossTab_3.
[15000], qryRebuildCostingCrossTab_4.[20000],
qryRebuildCostingCrossTab_5.[25000], qryRebuildCostingCrossTab_6.
[30000], qryRebuildCostingCrossTab_7.[35000],
qryRebuildCostingCrossTab_8.[40000];

i am trying to make sure this report is as speedy as possible. it isnt
super slow or anything but i am always interested in insights from the
community and sometimes i think i miss a simpler more obvious way of
doing things.

any and all help appreciated.