D
DMUM via AccessMonster.com
Hello
I have a table that shows the following information
intJobControlID
JobControlName
Defect
Opps
Sigma
DPMO
I needed to create a report that shows all of the fields for each JobName/ID
by month. In order to do this I created 4 seperate crosstab queries for each
field - Defect, opps,SIGMA and DPMO. Each query looks like this with the
only difference being SUM(tblMetricScorecard.field) AS Sumoffield
TRANSFORM Sum(tblMetricScorecard.dblDefects) AS SumOfdblDefects
SELECT tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
FROM tblMetricJobsAndControls LEFT JOIN tblMetricScorecard ON
tblMetricJobsAndControls.intJobControlID = tblMetricScorecard.intJobControlID
GROUP BY tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
PIVOT tblMetricScorecard.MetricMonthStamp;
To create the report - I created another query joining all 4 queries by the
JobNameID which works like a dream when creating this manually. However I
have to manually go into the joined query and choose what months I want to
view form each individual query. To create my final result, I must know what
Month(s) I want to include AND more importantly what MONTHS of data I have.
This is where my problem starts. I want to create this report dynamicly -
allowing the user to choose what dates they want to see. I tried doing this
by having the individual queries correspond to a getStartDate and getEndDate
function on a form that will open when the user chooses to run this
partivular report. This part works, but when I get to the JOIN of the 4
queries, I'm at a lost as to how to dynamically have the correct months
populate the JOIN query.
This is my JOIN query:
SELECT qry_RT_Defect_CT.intJobControlID, qry_RT_Opps_CT.JobControlName,
qry_RT_Defect_CT.[200501] AS JanDefect, qry_RT_Defect_CT.[200502] AS
FebDefect, qry_RT_Defect_CT.[200503] AS MarDefect, qry_RT_Defect_CT.[200504]
AS AprDefect, qry_RT_Defect_CT.[200505] AS MayDefect, qry_RT_Defect_CT.
[200506] AS JunDefect, qry_RT_Defect_CT.[200507] AS JulDefect,
qry_RT_Defect_CT.[200508] AS AugDefect, qry_RT_Defect_CT.[200509] AS
SepDefect, qry_RT_Defect_CT.[200510] AS OctDefect, qry_RT_Opps_CT.[200501] AS
JanOpps, qry_RT_Opps_CT.[200502] AS FebOpps, qry_RT_Opps_CT.[200503] AS
MarOpps, qry_RT_Opps_CT.[200504] AS AprOpps, qry_RT_Opps_CT.[200505] AS
MayOpps, qry_RT_Opps_CT.[200506] AS JunOpps, qry_RT_Opps_CT.[200507] AS
JulOpps, qry_RT_Opps_CT.[200508] AS AugOpps, qry_RT_Opps_CT.[200509] AS
SepOpps, qry_RT_Opps_CT.[200510] AS OctOpps, qry_RT_Sigma_CT.[200501] AS
JanSigma, qry_RT_Sigma_CT.[200502] AS FebSigma, qry_RT_Sigma_CT.[200503] AS
MarSigma, qry_RT_Sigma_CT.[200504] AS AprSigma, qry_RT_Sigma_CT.[200505] AS
MaySigma, qry_RT_Sigma_CT.[200506] AS JunSigma, qry_RT_Sigma_CT.[200507] AS
JulSigma, qry_RT_Sigma_CT.[200508] AS AugSigma, qry_RT_Sigma_CT.[200509] AS
SepSigma, qry_RT_Sigma_CT.[200510] AS OctSigma, qry_RT_DPMO_CT.[200501] AS
JanDPMO, qry_RT_DPMO_CT.[200502] AS FebDPMO, qry_RT_DPMO_CT.[200503] AS
MarDPMO, qry_RT_DPMO_CT.[200504] AS AprDPMO, qry_RT_DPMO_CT.[200505] AS
MayDPMO, qry_RT_DPMO_CT.[200506] AS JunDPMO, qry_RT_DPMO_CT.[200507] AS
JulDPMO, qry_RT_DPMO_CT.[200508] AS AugDPMO, qry_RT_DPMO_CT.[200509] AS
SepDPMO, qry_RT_DPMO_CT.[200510] AS OctDPMO
FROM ((qry_RT_Defect_CT INNER JOIN qry_RT_Opps_CT ON qry_RT_Defect_CT.
JobControlName = qry_RT_Opps_CT.JobControlName) INNER JOIN qry_RT_Sigma_CT ON
qry_RT_Opps_CT.JobControlName = qry_RT_Sigma_CT.JobControlName) INNER JOIN
qry_RT_DPMO_CT ON qry_RT_Sigma_CT.JobControlName = qry_RT_DPMO_CT.
JobControlName;
The problem here is that If I need to add Novembers data - I have to manually
go in and add it to each individual query and then add it to the JOIN query.
Also, if I don't have data - say for JUN, I get an error because the JOIN
query is looking for that month
I've looked at many of the responses for crosstab queries and also looked at
a few examples that were provided. But, I don't see any that represent what
I need or either I am just not understanding the recommendation provided -
this is of course not a far fetched idea. Anyway, I would appreciate any
help someone can provide. I am a loss how to get this to work without a
manual change.
Thank you
I have a table that shows the following information
intJobControlID
JobControlName
Defect
Opps
Sigma
DPMO
I needed to create a report that shows all of the fields for each JobName/ID
by month. In order to do this I created 4 seperate crosstab queries for each
field - Defect, opps,SIGMA and DPMO. Each query looks like this with the
only difference being SUM(tblMetricScorecard.field) AS Sumoffield
TRANSFORM Sum(tblMetricScorecard.dblDefects) AS SumOfdblDefects
SELECT tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
FROM tblMetricJobsAndControls LEFT JOIN tblMetricScorecard ON
tblMetricJobsAndControls.intJobControlID = tblMetricScorecard.intJobControlID
GROUP BY tblMetricJobsAndControls.intJobControlID, tblMetricJobsAndControls.
JobControlName
PIVOT tblMetricScorecard.MetricMonthStamp;
To create the report - I created another query joining all 4 queries by the
JobNameID which works like a dream when creating this manually. However I
have to manually go into the joined query and choose what months I want to
view form each individual query. To create my final result, I must know what
Month(s) I want to include AND more importantly what MONTHS of data I have.
This is where my problem starts. I want to create this report dynamicly -
allowing the user to choose what dates they want to see. I tried doing this
by having the individual queries correspond to a getStartDate and getEndDate
function on a form that will open when the user chooses to run this
partivular report. This part works, but when I get to the JOIN of the 4
queries, I'm at a lost as to how to dynamically have the correct months
populate the JOIN query.
This is my JOIN query:
SELECT qry_RT_Defect_CT.intJobControlID, qry_RT_Opps_CT.JobControlName,
qry_RT_Defect_CT.[200501] AS JanDefect, qry_RT_Defect_CT.[200502] AS
FebDefect, qry_RT_Defect_CT.[200503] AS MarDefect, qry_RT_Defect_CT.[200504]
AS AprDefect, qry_RT_Defect_CT.[200505] AS MayDefect, qry_RT_Defect_CT.
[200506] AS JunDefect, qry_RT_Defect_CT.[200507] AS JulDefect,
qry_RT_Defect_CT.[200508] AS AugDefect, qry_RT_Defect_CT.[200509] AS
SepDefect, qry_RT_Defect_CT.[200510] AS OctDefect, qry_RT_Opps_CT.[200501] AS
JanOpps, qry_RT_Opps_CT.[200502] AS FebOpps, qry_RT_Opps_CT.[200503] AS
MarOpps, qry_RT_Opps_CT.[200504] AS AprOpps, qry_RT_Opps_CT.[200505] AS
MayOpps, qry_RT_Opps_CT.[200506] AS JunOpps, qry_RT_Opps_CT.[200507] AS
JulOpps, qry_RT_Opps_CT.[200508] AS AugOpps, qry_RT_Opps_CT.[200509] AS
SepOpps, qry_RT_Opps_CT.[200510] AS OctOpps, qry_RT_Sigma_CT.[200501] AS
JanSigma, qry_RT_Sigma_CT.[200502] AS FebSigma, qry_RT_Sigma_CT.[200503] AS
MarSigma, qry_RT_Sigma_CT.[200504] AS AprSigma, qry_RT_Sigma_CT.[200505] AS
MaySigma, qry_RT_Sigma_CT.[200506] AS JunSigma, qry_RT_Sigma_CT.[200507] AS
JulSigma, qry_RT_Sigma_CT.[200508] AS AugSigma, qry_RT_Sigma_CT.[200509] AS
SepSigma, qry_RT_Sigma_CT.[200510] AS OctSigma, qry_RT_DPMO_CT.[200501] AS
JanDPMO, qry_RT_DPMO_CT.[200502] AS FebDPMO, qry_RT_DPMO_CT.[200503] AS
MarDPMO, qry_RT_DPMO_CT.[200504] AS AprDPMO, qry_RT_DPMO_CT.[200505] AS
MayDPMO, qry_RT_DPMO_CT.[200506] AS JunDPMO, qry_RT_DPMO_CT.[200507] AS
JulDPMO, qry_RT_DPMO_CT.[200508] AS AugDPMO, qry_RT_DPMO_CT.[200509] AS
SepDPMO, qry_RT_DPMO_CT.[200510] AS OctDPMO
FROM ((qry_RT_Defect_CT INNER JOIN qry_RT_Opps_CT ON qry_RT_Defect_CT.
JobControlName = qry_RT_Opps_CT.JobControlName) INNER JOIN qry_RT_Sigma_CT ON
qry_RT_Opps_CT.JobControlName = qry_RT_Sigma_CT.JobControlName) INNER JOIN
qry_RT_DPMO_CT ON qry_RT_Sigma_CT.JobControlName = qry_RT_DPMO_CT.
JobControlName;
The problem here is that If I need to add Novembers data - I have to manually
go in and add it to each individual query and then add it to the JOIN query.
Also, if I don't have data - say for JUN, I get an error because the JOIN
query is looking for that month
I've looked at many of the responses for crosstab queries and also looked at
a few examples that were provided. But, I don't see any that represent what
I need or either I am just not understanding the recommendation provided -
this is of course not a far fetched idea. Anyway, I would appreciate any
help someone can provide. I am a loss how to get this to work without a
manual change.
Thank you