J
jmoore
Thanks to Jeff boyce for his response to my question (do I need more
indexes?) in the database deisgn group. Individual reports created throughout
the year have been working fine. But, an aggregate annual report is very slow
to open, and edits in design view take a very long time to accomplish. The
report is based on qCrosstabs/combined. Jeff, here is the SQL you requested
for all of the queries involved. Thanks to all for your help.
qUnion
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1a" as
Question, [A1a] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1b" as
Question, [A1b] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A3" as
Question, [A3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A4" as
Question, [A4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5a" as
Question, [A5a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5b" as
Question, [A5b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5c" as
Question, [A5c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A6" as
Question, [A6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A7" as
Question, [A7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B1" as
Question, [B1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B2" as
Question, [B2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B3" as
Question, [B3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B4" as
Question, [B4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B5" as
Question, [B5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B6" as
Question, [B6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C1" as
Question, [C1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C2" as
Question, [C2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C3" as
Question, [C3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C4" as
Question, [C4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C5" as
Question, [C5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C6" as
Question, [C6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C7" as
Question, [C7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1a" as
Question, [D1a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1b" as
Question, [D1b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1c" as
Question, [D1c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D2" as
Question, [D2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D3" as
Question, [D3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D4" as
Question, [D4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D5" as
Question, [D5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E1" as
Question, [E1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E2" as
Question, [E2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E3" as
Question, [E3] as Answer
FROM [qReview Sample];
UNION ALL SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E4"
as Question, [E4] as Answer
FROM [qReview Sample];
qUnionCrosstab
TRANSFORM Count(qUnion.ReviewKey) AS CountOfReviewKey
SELECT qUnion.Question, qUnion.Answer, Count(qUnion.ReviewKey) AS
CountOfReviewKey1
FROM qUnion
GROUP BY qUnion.Question, qUnion.Answer
PIVOT qUnion.CNTYNAME In
("ALGER","ALPENA","BARAGA","BARRY","BENZIE","CASS","CLARE","CLINTON","GLADWIN","GRAND
TRAVERSE","HILLSDALE","HOUGHTON","INGHAM","IOSCO","JACKSON","KEWEENAW","LEELANAU","LENAWEE","LUCE","MACOMB","MASON","MONTCALM","MONTMORENCY","OGEMAW","OTSEGO","SAGINAW","SANILAC","SCHOOLCRAFT","SHIAWASSEE","WASHTENAW");
qReviewSampleCount
SELECT DISTINCT [qReview Sample].CNTYNAME, Count([qReview Sample].ID) AS
TotalReviews
FROM [qReview Sample]
GROUP BY [qReview Sample].CNTYNAME;
qReviewSampleCountCrosstab
TRANSFORM Sum(qReviewSampleCount.TotalReviews) AS SumOfTotalReviews
SELECT "RowHead" AS RowHead
FROM qReviewSampleCount
GROUP BY "RowHead"
PIVOT qReviewSampleCount.CNTYNAME;
qCrosstabsCombined
SELECT qUnionCrosstab.Question, qUnionCrosstab.Answer,
qUnionCrosstab.CountOfReviewKey1, qUnionCrosstab.ALGER,
qUnionCrosstab.ALPENA, qUnionCrosstab.BARAGA, qUnionCrosstab.BARRY,
qUnionCrosstab.BENZIE, qUnionCrosstab.CASS, qUnionCrosstab.CLARE,
qUnionCrosstab.CLINTON, qUnionCrosstab.GLADWIN, qUnionCrosstab.[GRAND
TRAVERSE], qUnionCrosstab.HILLSDALE, qUnionCrosstab.HOUGHTON,
qUnionCrosstab.INGHAM, qUnionCrosstab.IOSCO, qUnionCrosstab.JACKSON,
qUnionCrosstab.KEWEENAW, qUnionCrosstab.LEELANAU, qUnionCrosstab.LENAWEE,
qUnionCrosstab.LUCE, qUnionCrosstab.MACOMB, qUnionCrosstab.MASON,
qUnionCrosstab.MONTCALM, qUnionCrosstab.MONTMORENCY, qUnionCrosstab.OGEMAW,
qUnionCrosstab.OTSEGO, qUnionCrosstab.SAGINAW, qUnionCrosstab.SANILAC,
qUnionCrosstab.SCHOOLCRAFT, qUnionCrosstab.SHIAWASSEE,
qUnionCrosstab.WASHTENAW, qReviewSampleCountCrosstab.ALGER,
qReviewSampleCountCrosstab.ALPENA, qReviewSampleCountCrosstab.BARAGA,
qReviewSampleCountCrosstab.BARRY, qReviewSampleCountCrosstab.BENZIE,
qReviewSampleCountCrosstab.CASS, qReviewSampleCountCrosstab.CLARE,
qReviewSampleCountCrosstab.CLINTON, qReviewSampleCountCrosstab.GLADWIN,
qReviewSampleCountCrosstab.[GRAND TRAVERSE],
qReviewSampleCountCrosstab.HILLSDALE, qReviewSampleCountCrosstab.HOUGHTON,
qReviewSampleCountCrosstab.INGHAM, qReviewSampleCountCrosstab.IOSCO,
qReviewSampleCountCrosstab.JACKSON, qReviewSampleCountCrosstab.KEWEENAW,
qReviewSampleCountCrosstab.LEELANAU, qReviewSampleCountCrosstab.LENAWEE,
qReviewSampleCountCrosstab.LUCE, qReviewSampleCountCrosstab.MACOMB,
qReviewSampleCountCrosstab.MASON, qReviewSampleCountCrosstab.MONTCALM,
qReviewSampleCountCrosstab.MONTMORENCY, qReviewSampleCountCrosstab.OGEMAW,
qReviewSampleCountCrosstab.OTSEGO, qReviewSampleCountCrosstab.SAGINAW,
qReviewSampleCountCrosstab.SANILAC, qReviewSampleCountCrosstab.SCHOOLCRAFT,
qReviewSampleCountCrosstab.SHIAWASSEE, qReviewSampleCountCrosstab.WASHTENAW
FROM qReviewSampleCountCrosstab, qUnionCrosstab;
indexes?) in the database deisgn group. Individual reports created throughout
the year have been working fine. But, an aggregate annual report is very slow
to open, and edits in design view take a very long time to accomplish. The
report is based on qCrosstabs/combined. Jeff, here is the SQL you requested
for all of the queries involved. Thanks to all for your help.
qUnion
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1a" as
Question, [A1a] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A1b" as
Question, [A1b] as Answer
FROM [qReview Sample]
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A2" as
Question, [A2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A3" as
Question, [A3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A4" as
Question, [A4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5a" as
Question, [A5a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5b" as
Question, [A5b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A5c" as
Question, [A5c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A6" as
Question, [A6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "A7" as
Question, [A7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B1" as
Question, [B1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B2" as
Question, [B2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B3" as
Question, [B3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B4" as
Question, [B4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B5" as
Question, [B5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "B6" as
Question, [B6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C1" as
Question, [C1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C2" as
Question, [C2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C3" as
Question, [C3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C4" as
Question, [C4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C5" as
Question, [C5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C6" as
Question, [C6] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "C7" as
Question, [C7] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1a" as
Question, [D1a] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1b" as
Question, [D1b] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D1c" as
Question, [D1c] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D2" as
Question, [D2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D3" as
Question, [D3] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D4" as
Question, [D4] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "D5" as
Question, [D5] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E1" as
Question, [E1] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E2" as
Question, [E2] as Answer
FROM [qReview Sample];
UNION ALL
SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E3" as
Question, [E3] as Answer
FROM [qReview Sample];
UNION ALL SELECT [qReview Sample].CNTYNAME, [qReview Sample].ReviewKey, "E4"
as Question, [E4] as Answer
FROM [qReview Sample];
qUnionCrosstab
TRANSFORM Count(qUnion.ReviewKey) AS CountOfReviewKey
SELECT qUnion.Question, qUnion.Answer, Count(qUnion.ReviewKey) AS
CountOfReviewKey1
FROM qUnion
GROUP BY qUnion.Question, qUnion.Answer
PIVOT qUnion.CNTYNAME In
("ALGER","ALPENA","BARAGA","BARRY","BENZIE","CASS","CLARE","CLINTON","GLADWIN","GRAND
TRAVERSE","HILLSDALE","HOUGHTON","INGHAM","IOSCO","JACKSON","KEWEENAW","LEELANAU","LENAWEE","LUCE","MACOMB","MASON","MONTCALM","MONTMORENCY","OGEMAW","OTSEGO","SAGINAW","SANILAC","SCHOOLCRAFT","SHIAWASSEE","WASHTENAW");
qReviewSampleCount
SELECT DISTINCT [qReview Sample].CNTYNAME, Count([qReview Sample].ID) AS
TotalReviews
FROM [qReview Sample]
GROUP BY [qReview Sample].CNTYNAME;
qReviewSampleCountCrosstab
TRANSFORM Sum(qReviewSampleCount.TotalReviews) AS SumOfTotalReviews
SELECT "RowHead" AS RowHead
FROM qReviewSampleCount
GROUP BY "RowHead"
PIVOT qReviewSampleCount.CNTYNAME;
qCrosstabsCombined
SELECT qUnionCrosstab.Question, qUnionCrosstab.Answer,
qUnionCrosstab.CountOfReviewKey1, qUnionCrosstab.ALGER,
qUnionCrosstab.ALPENA, qUnionCrosstab.BARAGA, qUnionCrosstab.BARRY,
qUnionCrosstab.BENZIE, qUnionCrosstab.CASS, qUnionCrosstab.CLARE,
qUnionCrosstab.CLINTON, qUnionCrosstab.GLADWIN, qUnionCrosstab.[GRAND
TRAVERSE], qUnionCrosstab.HILLSDALE, qUnionCrosstab.HOUGHTON,
qUnionCrosstab.INGHAM, qUnionCrosstab.IOSCO, qUnionCrosstab.JACKSON,
qUnionCrosstab.KEWEENAW, qUnionCrosstab.LEELANAU, qUnionCrosstab.LENAWEE,
qUnionCrosstab.LUCE, qUnionCrosstab.MACOMB, qUnionCrosstab.MASON,
qUnionCrosstab.MONTCALM, qUnionCrosstab.MONTMORENCY, qUnionCrosstab.OGEMAW,
qUnionCrosstab.OTSEGO, qUnionCrosstab.SAGINAW, qUnionCrosstab.SANILAC,
qUnionCrosstab.SCHOOLCRAFT, qUnionCrosstab.SHIAWASSEE,
qUnionCrosstab.WASHTENAW, qReviewSampleCountCrosstab.ALGER,
qReviewSampleCountCrosstab.ALPENA, qReviewSampleCountCrosstab.BARAGA,
qReviewSampleCountCrosstab.BARRY, qReviewSampleCountCrosstab.BENZIE,
qReviewSampleCountCrosstab.CASS, qReviewSampleCountCrosstab.CLARE,
qReviewSampleCountCrosstab.CLINTON, qReviewSampleCountCrosstab.GLADWIN,
qReviewSampleCountCrosstab.[GRAND TRAVERSE],
qReviewSampleCountCrosstab.HILLSDALE, qReviewSampleCountCrosstab.HOUGHTON,
qReviewSampleCountCrosstab.INGHAM, qReviewSampleCountCrosstab.IOSCO,
qReviewSampleCountCrosstab.JACKSON, qReviewSampleCountCrosstab.KEWEENAW,
qReviewSampleCountCrosstab.LEELANAU, qReviewSampleCountCrosstab.LENAWEE,
qReviewSampleCountCrosstab.LUCE, qReviewSampleCountCrosstab.MACOMB,
qReviewSampleCountCrosstab.MASON, qReviewSampleCountCrosstab.MONTCALM,
qReviewSampleCountCrosstab.MONTMORENCY, qReviewSampleCountCrosstab.OGEMAW,
qReviewSampleCountCrosstab.OTSEGO, qReviewSampleCountCrosstab.SAGINAW,
qReviewSampleCountCrosstab.SANILAC, qReviewSampleCountCrosstab.SCHOOLCRAFT,
qReviewSampleCountCrosstab.SHIAWASSEE, qReviewSampleCountCrosstab.WASHTENAW
FROM qReviewSampleCountCrosstab, qUnionCrosstab;