Weird Result from Grouping in Query

[sleepguy]

Please Help, I am running the following series of sql scrips and should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script 3 is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between [Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector, qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;

 

[sleepguy]

One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];

 

[John Spencer]

GROUPING combines records, so what you should be seeing is
13 different CPT Codes and the Sum of the values for Occurance and Rate
associated with those codes. Is that not what you are expecting?

One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];

Please Help, I am running the following series of sql scrips and should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script 3 is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between [Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector, qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;

 

[sleepguy]

You are correct, in that I am seeing the grouping, but the summation of
the data does not equal the amount I would expect if I added them
manually. For example, the occurance feild is a number 1 to be used as
a checksum. when I run a count or sum on that feild, it gives me 8
records of CPT x and 4 records of CPT y, not 72 total for the record
set.

GROUPING combines records, so what you should be seeing is
13 different CPT Codes and the Sum of the values for Occurance and Rate
associated with those codes. Is that not what you are expecting?

One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];

Please Help, I am running the following series of sql scrips and should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script 3 is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between [Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector, qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;

 

[John Spencer]

That is because it is counting the number of records that have CPT x, not
the total number of records that were returned before aggregation.

The total of all the counts should be 72.

You are correct, in that I am seeing the grouping, but the summation of
the data does not equal the amount I would expect if I added them
manually. For example, the occurance feild is a number 1 to be used as
a checksum. when I run a count or sum on that feild, it gives me 8
records of CPT x and 4 records of CPT y, not 72 total for the record
set.

GROUPING combines records, so what you should be seeing is
13 different CPT Codes and the Sum of the values for Occurance and Rate
associated with those codes. Is that not what you are expecting?

One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];


sleepguy wrote:
Please Help, I am running the following series of sql scrips and
should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script 3
is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between
[Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector, qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;

 

[sleepguy]

We are in agreement regarding the grouping and the result, but I am not
communicating clearly.

I can use a count or sum function for the Occurance, rate, or CPT
feilds, and the results do not reflect the contents of the 72 records.
For example, I would expect the occurance count or sum (after it is
grouped) to = 72, but it does not.

I use grouping and summing regularly, for some reason beyond me, it is
not working in this particular query. I think it may have something to
do with the data type. CPT is text, occurance is number and rate is
currency.

That is because it is counting the number of records that have CPT x, not
the total number of records that were returned before aggregation.

The total of all the counts should be 72.

You are correct, in that I am seeing the grouping, but the summation of
the data does not equal the amount I would expect if I added them
manually. For example, the occurance feild is a number 1 to be used as
a checksum. when I run a count or sum on that feild, it gives me 8
records of CPT x and 4 records of CPT y, not 72 total for the record
set.

GROUPING combines records, so what you should be seeing is
13 different CPT Codes and the Sum of the values for Occurance and Rate
associated with those codes. Is that not what you are expecting?


One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];


sleepguy wrote:
Please Help, I am running the following series of sql scrips and
should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script 3
is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between
[Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector, qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;

 

[John Spencer]

Well, I don't have any further advice to give you.

Without having the actual data,
the time to explore it, and
what you expect the results to be, I can't say anything other than good
luck.

Perhaps you can narrow the data to just one CPT and post the rows returned
by query 3 both in the grouped version and the non-grouped version of the
data. Then tell us what is wrong with the grouped version of the data?

Try the following query
SELECT qry_billing_2.[CPT Code],
, Count([CPT Code]) as CountOccurancesOfCPT
, Sum(qry_billing_2.Occurance) AS SumOfOccurance
, Count(Rate) as CountOccurancesOfRate
, First(Rate) as ActualRate
, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];

That should give you some more information on what is happening in the
aggregate query. For instance, CountOccurancesOfCPT should be equal to
SumOfOccurance and CountOfOccurancesOfRate * Count(Rate) should be equal to
SumOfRate

We are in agreement regarding the grouping and the result, but I am not
communicating clearly.

I can use a count or sum function for the Occurance, rate, or CPT
feilds, and the results do not reflect the contents of the 72 records.
For example, I would expect the occurance count or sum (after it is
grouped) to = 72, but it does not.

I use grouping and summing regularly, for some reason beyond me, it is
not working in this particular query. I think it may have something to
do with the data type. CPT is text, occurance is number and rate is
currency.

That is because it is counting the number of records that have CPT x, not
the total number of records that were returned before aggregation.

The total of all the counts should be 72.

You are correct, in that I am seeing the grouping, but the summation of
the data does not equal the amount I would expect if I added them
manually. For example, the occurance feild is a number 1 to be used as
a checksum. when I run a count or sum on that feild, it gives me 8
records of CPT x and 4 records of CPT y, not 72 total for the record
set.

John Spencer wrote:
GROUPING combines records, so what you should be seeing is
13 different CPT Codes and the Sum of the values for Occurance and
Rate
associated with those codes. Is that not what you are expecting?


One additional note, when ungroup (SQL - 3 below) I get 72 records,
when I group as follows I only get results representing 13 records.

SELECT qry_billing_2.[CPT Code], Sum(qry_billing_2.Occurance) AS
SumOfOccurance, Sum(qry_billing_2.Rate) AS SumOfRate
FROM qry_billing_2
GROUP BY qry_billing_2.[CPT Code];


sleepguy wrote:
Please Help, I am running the following series of sql scrips and
should
be receiving 73 records. Scripts 1 & 2 return 73 records. Script
3
is
only returnig results representing 13 of the 72 records. I have
validated the groupings to ensure that they are common. Can
someone
help me understand why I only get 13 of the 72 records.

SQL -1
SELECT DISTINCT Left(dbo_tblStudy!AcquisitionInstrument,20) AS
Collector, dbo_tblStudy.CreationTime, dbo_tblStudy.StudyGUID,
Left(dbo_tblStudy!StudyName,8) AS StudyName
FROM dbo_tblStudy
WHERE (((dbo_tblStudy.CreationTime) Between
[Forms]![Form1]![Calendar0]
And [Forms]![Form1]![Calendar1]));

SQL -2
SELECT DISTINCTROW qry_Biling_1.Collector,
qry_Biling_1.CreationTime,
qry_Biling_1.StudyGUID, qry_Biling_1.StudyName,
tbl_collector_to_customer.[Customer ID], Tbl_Rates.Rate, 1 AS
Occurance, Tbl_Rates.[CPT Code]
FROM (qry_Biling_1 INNER JOIN tbl_collector_to_customer ON
qry_Biling_1.Collector = tbl_collector_to_customer.[Collector
Name])
INNER JOIN Tbl_Rates ON (tbl_collector_to_customer.[Customer ID] =
Tbl_Rates.[Customer ID]) AND (qry_Biling_1.StudyName =
Tbl_Rates.StudyName)
ORDER BY qry_Biling_1.CreationTime;

SQL-3
SELECT qry_billing_2.[CPT Code], qry_billing_2.Occurance,
qry_billing_2.Rate
FROM qry_billing_2;