Crosstab query totals

Need some help,
I have a crosstab query that returns the following data see sql below, what I
need is to total all gearbox types that are R and 37 and R and 27 and R and
47 etc. an example R and RF 27 total = 492

1 2 4 SubTotals
R 27 20 26 303 12 361
R 37 6 66 307 8 387
R 47 8 87 424 2 521
R47R 37 2 2
R57R 37 3 2 5
R67R 37 1 1 2
R77R 37 19 19
RF 27 4 17 109 1 131
RF 37 1 11 76 88
RF 47 1 25 39 65
TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r* 27*")) OR (((InspectionLog.
GearboxType) Like "r* 37*")) OR (((InspectionLog.GearboxType) Like "r* 47*"))
GROUP BY InspectionLog.GearboxType
PIVOT InspectionLog.AssyLineNumberInspection;

Thanks,
Jerry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

Try this --
TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals,
IIF(InspectionLog.GearboxType Like "R? ?7", "R or RF",
InspectionLog.GearboxType) AS Gearbox
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r* 27*")) OR
(((InspectionLog.GearboxType) Like "r* 37*")) OR
(((InspectionLog.GearboxType) Like "r* 47*"))
GROUP BY InspectionLog.GearboxType
PIVOT InspectionLog.AssyLineNumberInspection;


--
Build a little, test a little.


"jbair via AccessMonster.com" wrote:

Need some help,
I have a crosstab query that returns the following data see sql below, what I
need is to total all gearbox types that are R and 37 and R and 27 and R and
47 etc. an example R and RF 27 total = 492

1 2 4 SubTotals
R 27 20 26 303 12 361
R 37 6 66 307 8 387
R 47 8 87 424 2 521
R47R 37 2 2
R57R 37 3 2 5
R67R 37 1 1 2
R77R 37 19 19
RF 27 4 17 109 1 131
RF 37 1 11 76 88
RF 47 1 25 39 65
TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r* 27*")) OR (((InspectionLog.
GearboxType) Like "r* 37*")) OR (((InspectionLog.GearboxType) Like "r* 47*"))
GROUP BY InspectionLog.GearboxType
PIVOT InspectionLog.AssyLineNumberInspection;

Thanks,
Jerry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

.

Jbair -

This answer assumes you are only looking at the final two characters for the
27, 37, ot 47. Not sure where you would put the 'R47R 37' record - I assumed
it would be grouped with 'R 37'. Try this out (untested) and let us know:

TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals, "R " &
Right(InspectionLog.GearboxType,2) AS GearBoxTypeGroup
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r*")
AND ((Right(InspectionLog.GearboxType,2) In ("27","37","47")))
GROUP BY ("R " & Right(InspectionLog.GearboxType,2))
PIVOT InspectionLog.AssyLineNumberInspection;

--
Daryl S


"jbair via AccessMonster.com" wrote:

Need some help,
I have a crosstab query that returns the following data see sql below, what I
need is to total all gearbox types that are R and 37 and R and 27 and R and
47 etc. an example R and RF 27 total = 492

1 2 4 SubTotals
R 27 20 26 303 12 361
R 37 6 66 307 8 387
R 47 8 87 424 2 521
R47R 37 2 2
R57R 37 3 2 5
R67R 37 1 1 2
R77R 37 19 19
RF 27 4 17 109 1 131
RF 37 1 11 76 88
RF 47 1 25 39 65
TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals, InspectionLog.GearboxType
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r* 27*")) OR (((InspectionLog.
GearboxType) Like "r* 37*")) OR (((InspectionLog.GearboxType) Like "r* 47*"))
GROUP BY InspectionLog.GearboxType
PIVOT InspectionLog.AssyLineNumberInspection;

Thanks,
Jerry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1

.

Thanks Daryl that worked great!

Daryl S wrote:
Jbair -

This answer assumes you are only looking at the final two characters for the
27, 37, ot 47. Not sure where you would put the 'R47R 37' record - I assumed
it would be grouped with 'R 37'. Try this out (untested) and let us know:

TRANSFORM Sum(InspectionLog.Quantity) AS SumOfQuantity
SELECT Sum(InspectionLog.Quantity) AS SubTotals, "R " &
Right(InspectionLog.GearboxType,2) AS GearBoxTypeGroup
FROM InspectionLog
WHERE (((InspectionLog.GearboxType) Like "r*")
AND ((Right(InspectionLog.GearboxType,2) In ("27","37","47")))
GROUP BY ("R " & Right(InspectionLog.GearboxType,2))
PIVOT InspectionLog.AssyLineNumberInspection;

Need some help,
I have a crosstab query that returns the following data see sql below, what I
[quoted text clipped - 22 lines]
Thanks,
Jerry

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201004/1