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

D

DawnTreader

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.
 
B

Bob Barrows

DawnTreader said:
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
UNION SELECT
Click to expand...

The only improvement I can see is to include the ALL keyword to prevent the
query engine from going to the trouble of eliminating duplicate rows
returned from the union. Only do this if you know there is no chance of any
of the unioned select statements returning duplicate rows.

Other than that, unions and pivots provide very few opportunities for
performance enhancement. One key to remember for pivots:
minimize the number of rows going into the pivot operation - if you can
perform an initial grouping on the input data, it might help. For example,
if 100,000 records need to be pivoted using a SUM operation, performance of
the pivot could be improved if you initally created a query to perform the
group by and sum, reducing the number of records. Then feed the results of
the grouping query into the pivot query. This won't always have an effect,
so testing of course will be required.
 
D

DawnTreader

The only improvement I can see is to include the ALL keyword to prevent the
query engine from going to the trouble of eliminating duplicate rows
returned from the union. Only do this if you know there is no chance of any
of the unioned select statements returning duplicate rows.

Other than that, unions and pivots provide very few opportunities for
performance enhancement. One key to remember for pivots:
minimize the number of rows going into the pivot operation - if you can
perform an initial grouping on the input data, it might help. For example,
if 100,000 records need to be pivoted using a SUM operation, performance of
the pivot could be improved if you initally created a query to perform the
group by and sum, reducing the number of  records. Then feed the results of
the grouping query into the pivot query. This won't always have an effect,
so testing of course will be required.
Click to expand...

i think this is what you mean:

SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.onekmaint)="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.onekmaint)="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FiveKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"2 5000 Hr Rebuild" AS IntervalDescription,
5000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FiveKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"2 5000 Hr Rebuild" AS IntervalDescription,
5000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TenKRebuild)="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"3 10000 Hr Rebuild" AS IntervalDescription,
10000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TenKRebuild)="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"3 10000 Hr Rebuild" AS IntervalDescription,
10000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"4 15000 Hr Rebuild" AS IntervalDescription,
15000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"4 15000 Hr Rebuild" AS IntervalDescription,
15000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"5 20000 Hr Rebuild" AS IntervalDescription,
20000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"5 20000 Hr Rebuild" AS IntervalDescription,
20000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwFKThouRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"6 25000 Hr Rebuild" AS IntervalDescription,
25000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwFKThouRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"6 25000 Hr Rebuild" AS IntervalDescription,
25000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"7 30000 Hr Rebuild" AS IntervalDescription,
30000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"7 30000 Hr Rebuild" AS IntervalDescription,
30000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThFKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"8 35000 Hr Rebuild" AS IntervalDescription,
35000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThFKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"8 35000 Hr Rebuild" AS IntervalDescription,
35000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
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,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtyKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"9 40000 Hr Rebuild" AS IntervalDescription,
40000 AS IntervalLink,
"BatchList" AS ValueType,
tblMasterPartList.BatchList AS PartValue,
(tblMasterPartList.BatchList*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtyKRebuild )="y"))
UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"9 40000 Hr Rebuild" AS IntervalDescription,
40000 AS IntervalLink,
"VisualList" AS ValueType,
dbo_PART.UNIT_PRICE AS PartValue,
(dbo_PART.UNIT_PRICE*tblProductPartList.QTY) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtyKRebuild )="y"));

i have moved all the stuff i was doing a level up into the union query
so i am calculating before the crosstab. but now i have a different
problem. how do i get the cross tab to show all three dollar value
sets SetCost, VisualList and BatchList?
 
D

DawnTreader

The only improvement I can see is to include the ALL keyword to prevent the
query engine from going to the trouble of eliminating duplicate rows
returned from the union. Only do this if you know there is no chance of any
of the unioned select statements returning duplicate rows.

Other than that, unions and pivots provide very few opportunities for
performance enhancement. One key to remember for pivots:
minimize the number of rows going into the pivot operation - if you can
perform an initial grouping on the input data, it might help. For example,
if 100,000 records need to be pivoted using a SUM operation, performance of
the pivot could be improved if you initally created a query to perform the
group by and sum, reducing the number of  records. Then feed the results of
the grouping query into the pivot query. This won't always have an effect,
so testing of course will be required.
Click to expand...

yeah! i figured it out:

SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.onekmaint)="y"))
GROUP BY tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID, dbo_PART.DESCRIPTION,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "1 1000 Hr Service",
1000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"2 5000 Hr Rebuild" AS IntervalDescription,
5000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FiveKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "2 5000 Hr Rebuild",
5000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"3 10000 Hr Rebuild" AS IntervalDescription,
10000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TenKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "3 10000 Hr
Rebuild", 10000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"4 15000 Hr Rebuild" AS IntervalDescription,
15000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "4 15000 Hr
Rebuild", 15000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"5 20000 Hr Rebuild" AS IntervalDescription,
20000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "5 20000 Hr
Rebuild", 20000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"6 25000 Hr Rebuild" AS IntervalDescription,
25000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.TwFKThouRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "6 25000 Hr
Rebuild", 25000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"7 30000 Hr Rebuild" AS IntervalDescription,
30000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "7 30000 Hr
Rebuild", 30000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"8 35000 Hr Rebuild" AS IntervalDescription,
35000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
Sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS
TotalVisualList
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.ThFKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "8 35000 Hr
Rebuild", 35000

UNION SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION AS PartDesc,
tblProductPartList.QTY,
"9 40000 Hr Rebuild" AS IntervalDescription,
40000 AS IntervalLink,
tblMasterPartList.SetCost AS PartSetCost,
tblMasterPartList.BatchList AS PartBatchList,
dbo_PART.UNIT_PRICE AS PartVisualList,
sum((tblMasterPartList.SetCost*tblProductPartList.QTY)) AS
TotalSetCost,
sum((tblMasterPartList.BatchList*tblProductPartList.QTY)) AS
TotalBatchList,
sum((dbo_PART.UNIT_PRICE*tblProductPartList.QTY)) AS TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.FtyKRebuild)="y"))
GROUP BY
tblProductPartList.ProductID,tblProductPartList.IMWPartNumberID,
dbo_PART.DESCRIPTION, tblProductPartList.QTY,
tblProductPartList.QTY,tblMasterPartList.SetCost,
tblMasterPartList.BatchList, dbo_PART.UNIT_PRICE, "9 40000 Hr
Rebuild", 40000;


then

PARAMETERS [Forms]![frmManageAssets]![subProductID] Short;
TRANSFORM Sum(IIf([FieldName]="Set Cost",
[TotalSetCost],IIf([FieldName]="Batch List",[TotalBatchList],
[TotalVisualList]))) AS TotalValue
SELECT qryRebuildReportStacked.ProductID,
qryRebuildReportStacked.IMWPartNumberID,
qryRebuildReportStacked.PartDesc, qryRebuildReportStacked.QTY,
qryRebuildReportStacked.IntervalDescription,
qryRebuildReportStacked.IntervalLink,
qryRebuildReportStacked.PartSetCost,
qryRebuildReportStacked.PartBatchList,
qryRebuildReportStacked.PartVisualList
FROM qryRebuildReportStacked, stblRebuildFieldStack
GROUP BY qryRebuildReportStacked.ProductID,
qryRebuildReportStacked.IMWPartNumberID,
qryRebuildReportStacked.PartDesc, qryRebuildReportStacked.QTY,
qryRebuildReportStacked.IntervalDescription,
qryRebuildReportStacked.IntervalLink,
qryRebuildReportStacked.PartSetCost,
qryRebuildReportStacked.PartBatchList,
qryRebuildReportStacked.PartVisualList,
qryRebuildReportStacked.IMWPartNumberID
ORDER BY qryRebuildReportStacked.IntervalDescription,
qryRebuildReportStacked.IMWPartNumberID
PIVOT [IntervalDescription] & " " & [FieldName];

works wonderfully!
 
B

Bob Barrows

DawnTreader said:
i think this is what you mean:

SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.QTY,
"1 1000 Hr Service" AS IntervalDescription,
1000 AS IntervalLink,
"SetCost" AS ValueType,
tblMasterPartList.SetCost AS PartValue,
(tblMasterPartList.SetCost*tblProductPartList.QTY) AS
TotalPartValue
FROM
(tblProductPartList LEFT JOIN tblMasterPartList ON
tblProductPartList.IMWPartNumberID = tblMasterPartList.ID)
LEFT JOIN dbo_PART ON tblProductPartList.IMWPartNumberID =
dbo_PART.ID
WHERE
(((tblProductPartList.ProductID)=Forms!frmManageAssets!
subProductID) AND ((tblProductPartList.onekmaint)="y"))
UNION SELECT
Click to expand...

You're still leaving out the "ALL" keyword (my first suggestion in my reply)

UNION ALL SELECT
i have moved all the stuff i was doing a level up into the union query
so i am calculating before the crosstab. but now i have a different
problem. how do i get the cross tab to show all three dollar value
sets SetCost, VisualList and BatchList?
Click to expand...

This actually is not what I was suggesting. I was suggesting that you feed
the result of the original UNION query into a grouping query to reduce the
number of records that are going to be processed by the TRANSFORM...PIVOT
query.

I was speaking from a generic standpoint rather than attempting to analyze
what you are doing here so I am going to have to let someone else jump in
here to answer your specific question.
 
T

Tony Toews

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.
Click to expand...

My concern is what happens when the engineers decide one particular
engine/unit can now go 1200 hours between service intervals and all
the other numbers increase accordingly from 10,000 to 12,000.

Seems to me those kinds of numbers should be in a lookup table of some
sort.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
 
D

DawnTreader

My concern is what happens when the engineers decide one particular
engine/unit can now go 1200 hours between service intervals and all
the other numbers increase accordingly from 10,000 to 12,000.  

Seems to me those kinds of numbers should be in a lookup table of some
sort.  

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Click to expand...

They are already in a table. Thanks for the input. :)
 
D

DawnTreader

Awesome.  I love those kinds of answers.  <smile>

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages -http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
  updated seehttp://www.autofeupdater.com/
Click to expand...

I have been a student of you access MVPs for sometime without your
knowledge. :)

I find the internet an awesome resource for learning how to program
access. I have your site bookmarked and visit at least once a month to
see if there is anything on my 'current' predicament. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to merge these 2 queries 1
Help with Group By 6
Is there a way to do this with queries? 0
Is there a more elegant way of doing this ?? 4
Display the result of a query in a text box on a form 2

Top