I am relatively new to dcounts.  I have a table with one field containing 
values a,b,c,d,e,f.  In the query I have 3 calculated fields 
(Staging,Success,Variance) which is a combination of record counts (i.e. a+c, 
a-d-e-c+b,etc).

I used the dcount for each (a-f) and when I return the query, it ignores the 
group by sourcesys set up in a previous field.

Here is an example of the return 
businessDate	SourceSys	Staging 
Count	Exclusions	Exceptions	Errors	Created	Success	Variance
7/23/2007	Amnet	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
7/23/2007	Beta	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
7/23/2007	CalypsoBO	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
7/23/2007	CalypsoCredit	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00


Notice that the total of ALL records appears in every Sourcesys.  any idea 
how to execute this

Can you post your SQL statement, so we can see what you are doing now?
-- 
Email address is not valid.
Please reply to newsgroup only.


"mwhaley73" wrote:

> I am relatively new to dcounts.  I have a table with one field containing 
> values a,b,c,d,e,f.  In the query I have 3 calculated fields 
> (Staging,Success,Variance) which is a combination of record counts (i.e. a+c, 
> a-d-e-c+b,etc).
> 
> I used the dcount for each (a-f) and when I return the query, it ignores the 
> group by sourcesys set up in a previous field.
> 
> Here is an example of the return 
> businessDate	SourceSys	Staging 
> Count	Exclusions	Exceptions	Errors	Created	Success	Variance
> 7/23/2007	Amnet	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> 7/23/2007	Beta	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> 7/23/2007	CalypsoBO	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> 7/23/2007	CalypsoCredit	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> 
> 
> Notice that the total of ALL records appears in every Sourcesys.  any idea 
> how to execute this

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
FROM tbl_MasterPositions m
WHERE m.businessDate=#7/23/2007#
GROUP BY m.businessDate, m.SourceSys
;
Thanks
"Dale Fye" wrote:

> Can you post your SQL statement, so we can see what you are doing now?
> -- 
> Email address is not valid.
> Please reply to newsgroup only.
> 
> 
> "mwhaley73" wrote:
> 
> > I am relatively new to dcounts.  I have a table with one field containing 
> > values a,b,c,d,e,f.  In the query I have 3 calculated fields 
> > (Staging,Success,Variance) which is a combination of record counts (i.e. a+c, 
> > a-d-e-c+b,etc).
> > 
> > I used the dcount for each (a-f) and when I return the query, it ignores the 
> > group by sourcesys set up in a previous field.
> > 
> > Here is an example of the return 
> > businessDate	SourceSys	Staging 
> > Count	Exclusions	Exceptions	Errors	Created	Success	Variance
> > 7/23/2007	Amnet	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> > 7/23/2007	Beta	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> > 7/23/2007	CalypsoBO	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> > 7/23/2007	CalypsoCredit	239,537.00	-28,379.00	-8,000.00	-11,630.00	1	191,529.00	0.00
> > 
> > 
> > Notice that the total of ALL records appears in every Sourcesys.  any idea 
> > how to execute this

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 am relatively new to dcounts.  I have a table with one field 
>> > containing
>> > values a,b,c,d,e,f.  In the query I have 3 calculated fields
>> > (Staging,Success,Variance) which is a combination of record counts 
>> > (i.e. a+c,
>> > a-d-e-c+b,etc).

(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" <mwhaley73@discussions.microsoft.com> wrote in message 
news:4B2D2978-E101-4B46-96FC-5EF991559D8E@microsoft.com...
> 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
> FROM tbl_MasterPositions m
> WHERE m.businessDate=#7/23/2007#
> GROUP BY m.businessDate, m.SourceSys
> ;
> Thanks
> "Dale Fye" wrote:
>
>> Can you post your SQL statement, so we can see what you are doing now?
>> -- 
>> Email address is not valid.
>> Please reply to newsgroup only.
>>
>>
>> "mwhaley73" wrote:
>>
>> > I am relatively new to dcounts.  I have a table with one field 
>> > containing
>> > values a,b,c,d,e,f.  In the query I have 3 calculated fields
>> > (Staging,Success,Variance) which is a combination of record counts 
>> > (i.e. a+c,
>> > a-d-e-c+b,etc).
>> >
>> > I used the dcount for each (a-f) and when I return the query, it 
>> > ignores the
>> > group by sourcesys set up in a previous field.
>> >
>> > Here is an example of the return
>> > businessDate SourceSys Staging
>> > Count Exclusions Exceptions Errors Created Success Variance
>> > 7/23/2007 Amnet 239,537.00 -28,379.00 -8,000.00 -11,630.00 1 191,529.00 
>> > 0.00
>> > 7/23/2007 Beta 239,537.00 -28,379.00 -8,000.00 -11,630.00 1 191,529.00 
>> > 0.00
>> > 7/23/2007 CalypsoBO 239,537.00 -28,379.00 -8,000.00 -11,630.00 1 
>> > 191,529.00 0.00
>> > 7/23/2007 CalypsoCredit 239,537.00 -28,379.00 -8,000.00 -11,630.00 1 
>> > 191,529.00 0.00
>> >
>> >
>> > Notice that the total of ALL records appears in every Sourcesys.  any 
>> > idea
>> > how to execute this