Grouping with parameters

[Ryan]

I have a query with 4 fields, [Physician], [AccountNumber], [Charges], and
[CPTCODE]. Each account number can have multiple charges, so one account
number can be listed in 2 or more records. Each charge per account number
will have a different [CPTCODE]. What I need to produce is this.

Physician Total Patients Total Charges
DR Smith 52 10,000
DR James 72 80,000
DR Brian 85 90,000

To get the total patients I take only the [AccountNumber] and Group and
count it. Here is the tricky part. If the [CPTCode] = 80010, then I dont
want to see those accounts. The problem is that there can be multiple
[Charges] per [AccountNumber], with different [CPTCODES], but if one of
thoses [CPTCODES] = 80010, then I dont want to see any of the [Charges] for
that [AccountNumber].

I hope this makes sense, and any help or suggestions would be greatly
apprieciated. Thanks in advance.
--

 

[Michel Walsh]

Start with:

SELECT physician, accountNumber, charges, cptCode
FROM myTable As a
LEFT JOIN
(SELECT accountNumber
FROM myTable
WHERE CPTCode = 80010) AS b
ON a.accountNumber = b.accountNumber
WHERE b.accountNumber IS NULL


and add the GROUPing, as required.


Hoping it may help,
Vanderghast, Access MVP

 

[KARL DEWEY]

Try these two queries --
Account_with_80010 ---
SELECT Ryan.AccountNumber, Ryan.CPTCODE
FROM Ryan
WHERE (((Ryan.CPTCODE)="80010"));

SELECT Ryan.Physician, Count(Ryan.AccountNumber) AS [Total Patients],
Sum([Charges]) AS [Total Charges]
FROM Ryan LEFT JOIN Account_with_80010 ON Ryan.AccountNumber =
Account_with_80010.AccountNumber
WHERE (((Account_with_80010.AccountNumber) Is Null))
GROUP BY Ryan.Physician;

 

[Ryan]

Ok, I think your solution is what I need, but I dont fully understand the
syntax and I am getting some strange results. Here is my SQL.

SELECT a.Physician, a.ACCOUNTNUMBER, a.TRAMT, a.CPTCodes
FROM 06qryPhysicianAssistantOnlyAccounts AS a LEFT JOIN (SELECT
AccountNumber FROM 06qryPhysicianAssistantOnlyAccounts WHERE CPTCodes=80010 )
AS b ON a.ACCOUNTNUMBER = b.AccountNumber
WHERE (((b.AccountNumber) Is Null));

When I checked to see if the results were correct, I filtered
06qryPhysicianAssistantOnlyAccounts with Where CPTCodes = 80010 and there
were 19 results. However, when I applied the code above, there was a
difference of 65 results. Could you explain exactly what is happening with
the syntax of the multiple select and if I missed something?

--
Please remember to mark this post as answered if this solves your problem.

Start with:

SELECT physician, accountNumber, charges, cptCode
FROM myTable As a
LEFT JOIN
(SELECT accountNumber
FROM myTable
WHERE CPTCode = 80010) AS b
ON a.accountNumber = b.accountNumber
WHERE b.accountNumber IS NULL


and add the GROUPing, as required.


Hoping it may help,
Vanderghast, Access MVP

I have a query with 4 fields, [Physician], [AccountNumber], [Charges], and
[CPTCODE]. Each account number can have multiple charges, so one account
number can be listed in 2 or more records. Each charge per account number
will have a different [CPTCODE]. What I need to produce is this.

Physician Total Patients Total Charges
DR Smith 52 10,000
DR James 72 80,000
DR Brian 85 90,000

To get the total patients I take only the [AccountNumber] and Group and
count it. Here is the tricky part. If the [CPTCode] = 80010, then I dont
want to see those accounts. The problem is that there can be multiple
[Charges] per [AccountNumber], with different [CPTCODES], but if one of
thoses [CPTCODES] = 80010, then I dont want to see any of the [Charges]
for
that [AccountNumber].

I hope this makes sense, and any help or suggestions would be greatly
apprieciated. Thanks in advance.
--

 

[Ryan]

After further review, your query works perfect. There were only 19 accounts
with CPTCodes = 80010, but those 19 accounts had a total of 65 charges. This
is exactly what I needed. Thank you very much. Just one last question.
Could you explain how each step of the both selects produced this result so I
can understand how to reproduce this kind of query in the future?
--
Please remember to mark this post as answered if this solves your problem.

Start with:

SELECT physician, accountNumber, charges, cptCode
FROM myTable As a
LEFT JOIN
(SELECT accountNumber
FROM myTable
WHERE CPTCode = 80010) AS b
ON a.accountNumber = b.accountNumber
WHERE b.accountNumber IS NULL


and add the GROUPing, as required.


Hoping it may help,
Vanderghast, Access MVP

I have a query with 4 fields, [Physician], [AccountNumber], [Charges], and
[CPTCODE]. Each account number can have multiple charges, so one account
number can be listed in 2 or more records. Each charge per account number
will have a different [CPTCODE]. What I need to produce is this.

Physician Total Patients Total Charges
DR Smith 52 10,000
DR James 72 80,000
DR Brian 85 90,000

To get the total patients I take only the [AccountNumber] and Group and
count it. Here is the tricky part. If the [CPTCode] = 80010, then I dont
want to see those accounts. The problem is that there can be multiple
[Charges] per [AccountNumber], with different [CPTCODES], but if one of
thoses [CPTCODES] = 80010, then I dont want to see any of the [Charges]
for
that [AccountNumber].

I hope this makes sense, and any help or suggestions would be greatly
apprieciated. Thanks in advance.
--

 

[Michel Walsh]

The innermost query make a 'virtual' table with the accounts numbers having
a cptCode = 80010.

The outer most query is what the query wizard would produce if you ask about
finding unmatched records: which account number have no match (are NOT IN)
the 'virtual' table the inner most query returned. The code is based on the
fact that if there is no match, the outer LEFT join have to supply a null
for the RIGHT table, here, aliased as "b", so, b.accountNumber IS NULL
indicates that rows from table aliased as 'a' have no match in table "b".
Also, alias are just a simple name useful to replace more complex
expression.


Vanderghast, Access MVP

After further review, your query works perfect. There were only 19
accounts
with CPTCodes = 80010, but those 19 accounts had a total of 65 charges.
This
is exactly what I needed. Thank you very much. Just one last question.
Could you explain how each step of the both selects produced this result
so I
can understand how to reproduce this kind of query in the future?
--
Please remember to mark this post as answered if this solves your problem.

Start with:

SELECT physician, accountNumber, charges, cptCode
FROM myTable As a
LEFT JOIN
(SELECT accountNumber
FROM myTable
WHERE CPTCode = 80010) AS b
ON a.accountNumber = b.accountNumber
WHERE b.accountNumber IS NULL


and add the GROUPing, as required.


Hoping it may help,
Vanderghast, Access MVP

I have a query with 4 fields, [Physician], [AccountNumber], [Charges],
and
[CPTCODE]. Each account number can have multiple charges, so one
account
number can be listed in 2 or more records. Each charge per account
number
will have a different [CPTCODE]. What I need to produce is this.

Physician Total Patients Total Charges
DR Smith 52 10,000
DR James 72 80,000
DR Brian 85 90,000

To get the total patients I take only the [AccountNumber] and Group and
count it. Here is the tricky part. If the [CPTCode] = 80010, then I
dont
want to see those accounts. The problem is that there can be multiple
[Charges] per [AccountNumber], with different [CPTCODES], but if one of
thoses [CPTCODES] = 80010, then I dont want to see any of the [Charges]
for
that [AccountNumber].

I hope this makes sense, and any help or suggestions would be greatly
apprieciated. Thanks in advance.
--

 

[Ryan]

I kinda figured out how it works before you replied. Thank you so much for
the query, I can use it in so many places. I have been making 2 or three
queries to produce what you have given me in just one. Thanks again.!.
--
Please remember to mark this post as answered if this solves your problem.

The innermost query make a 'virtual' table with the accounts numbers having
a cptCode = 80010.

The outer most query is what the query wizard would produce if you ask about
finding unmatched records: which account number have no match (are NOT IN)
the 'virtual' table the inner most query returned. The code is based on the
fact that if there is no match, the outer LEFT join have to supply a null
for the RIGHT table, here, aliased as "b", so, b.accountNumber IS NULL
indicates that rows from table aliased as 'a' have no match in table "b".
Also, alias are just a simple name useful to replace more complex
expression.


Vanderghast, Access MVP

After further review, your query works perfect. There were only 19
accounts
with CPTCodes = 80010, but those 19 accounts had a total of 65 charges.
This
is exactly what I needed. Thank you very much. Just one last question.
Could you explain how each step of the both selects produced this result
so I
can understand how to reproduce this kind of query in the future?
--
Please remember to mark this post as answered if this solves your problem.

Start with:

SELECT physician, accountNumber, charges, cptCode
FROM myTable As a
LEFT JOIN
(SELECT accountNumber
FROM myTable
WHERE CPTCode = 80010) AS b
ON a.accountNumber = b.accountNumber
WHERE b.accountNumber IS NULL


and add the GROUPing, as required.


Hoping it may help,
Vanderghast, Access MVP


I have a query with 4 fields, [Physician], [AccountNumber], [Charges],
and
[CPTCODE]. Each account number can have multiple charges, so one
account
number can be listed in 2 or more records. Each charge per account
number
will have a different [CPTCODE]. What I need to produce is this.

Physician Total Patients Total Charges
DR Smith 52 10,000
DR James 72 80,000
DR Brian 85 90,000

To get the total patients I take only the [AccountNumber] and Group and
count it. Here is the tricky part. If the [CPTCode] = 80010, then I
dont
want to see those accounts. The problem is that there can be multiple
[Charges] per [AccountNumber], with different [CPTCODES], but if one of
thoses [CPTCODES] = 80010, then I dont want to see any of the [Charges]
for
that [AccountNumber].

I hope this makes sense, and any help or suggestions would be greatly
apprieciated. Thanks in advance.