1. The first thing I think you need to do is define your "fields" better.
IF
[Staging Count] = [Success] - [Created] - [Errors] - [Exceptions] -
[Exclusions]
[Staging Count] = ([1] + [2]) - [Created] - [Errors] - [Exceptions] -
[Exclusions] where [1] and [2] are the number of records that have
PositionProcessStatus = 1 or 2, and since [Created] = -[2] then
[Staging Count] = [1] + 2 * [2] - [Errors] - [Exceptions] - [Exclusions]
Then
[Variance] = [Staging Count] + [Exclusions] + [Exceptions] + [Errors] +
[Created] - [Success]
= ([Success] - [Created] - [Errors] - [Exceptions] -
[Exclusions]) + [Exclusions] + [Exceptions] + [Errors] + [Created] -
[Success]
= 0 always,
2. This may seem stupid, but what about a value of 3 in the
PositionProcessStatus field?
3. I think I would start by creating a new table (tbl_lookup_PosProStat)
which contains three fields (StatusName, PositionProcessStatus, and
Multiplier) and has values as shown below, which correspond to the
coefficients of the letters shown in your statement. Forgive me if the
Multiplier values are not what they should be, but I think I got them right
based on the query you added to the previous post.
(I've abbreviated the PositionProcessStatus field as PPS)
SN PPS Multiplier
Success 1 1
Success 2 1
Created 2 1
Errors 4 -1
Exceptions 5 -1
Exclusions 6 -1
Since your equation calls for -[errors]-[exception]-[exclusions], and since
all of these values are already negative, I assumed that the coefficient of
these values is supposed to be positive. If this is incorrect, then just
change the Multiplier value for the appropriate values of
PositionProcessStatus
Staging Count 1 1
Staging Count 2 2
Staging Count 4 1
Staging Count 5 1
Staging Count 6 1
Once you figure out what the Variance equation actually looks like, you can
change the zeros in the records below to the appropriate values:
Variance 1 0
Variance 2 0
Variance 4 0
Variance 5 0
Variance 6 0
3. Now, create a crosstab query. Stay with me here, because this could get
confusing.
Join the tbl_MasterPosition (alias as M) to the tbl_lookup_PPS (alias as L)
on the business date, then add the m.BusinessDate, m.SourceSys,
L.StatusName, and L.Multiplier fields to the query grid. Change the query
to a crosstab and make the first two fields "Row Headers", the StatusName
field as a "Column Header", and the Multiplier field as a "Value", then
change the GroupBy in the Multiplier field to Sum (this Sum takes the place
of counting the records, since you are summing a number). Then, you need to
add the column headings (this might be easier in the SQL view), making sure
that the valuse in the PIVOT ... IN clause match exactly with the names you
entered in the StatusName field of the lookup table. Your SQL statement
should look something like:
TRANSFORM Sum(tbl_lookup_PPS.Multiplier) AS Expr1
SELECT tbl_MasterPositions.BusinessDate, tbl_MasterPositions.SourceSys
FROM tbl_MasterPositions INNER JOIN tbl_lookup_PPS ON
tbl_MasterPositions.PositionProcessStatus =
tbl_lookup_PPS.PositionProcessStatus
GROUP BY tbl_MasterPositions.BusinessDate, tbl_MasterPositions.SourceSys
PIVOT tbl_lookup_PPS.StatusName In ("Staging
Count","Exclusions","Exceptions","Errors","Created","Success","Variance");
For each of the StatusNames that is listed more than once in the lookup
table, you will be basically summing record counts.
To filter this for the date you want, add a WHERE clause between the FROM
and Group By lines.
FROM ...
WHERE tbl_MasterPositions.BusinessDate = #7/23/2007#
GROUP BY ....
HTH
Dale
mwhaley73 said:
Sure.
SELECT m.businessDate, m.SourceSys,
[success]-[created]-[errors]-[exceptions]-[exclusions] AS [Staging Count],
DCount("[positionid]","tbl_MasterPosition","[PositionProcessStatus]=6 and
[businessdate]=#7/23/2007#")*-1 AS Exclusions,
DCount("[positionid]","tbl_masterpositions","[PositionProcessStatus]=5 and
[businessdate]=#7/23/2007#")*-1 AS Exceptions,
DCount("[positionid]","tbl_masterpositions","[PositionProcessStatus]=4 and
[businessdate]=#7/23/2007#")*-1 AS Errors,
DCount("[positionid]","tbl_masterpositions","[PositionProcessStatus]=2 and
[businessdate]=#7/23/2007#") AS Created,
DCount("[positionid]","tbl_masterpositions","[PositionProcessStatus]=1 and
[businessdate]=#7/23/2007#") +
DCount("[positionid]","tbl_masterpositions","[PositionProcessStatus]=2
and [businessdate]=#7/23/2007#") AS Success,
[Staging Count]+[Exclusions]+[Exceptions]+[Errors]+[Created]-[success] AS
Variance
