Grouping

S

Stefan

I would like to run a grouping query which has three fields.

Chart# Date Transaction Type


I would like to group the chart# and the Date but the Transaction Type is
for filtering the query only. There are many transactions types and I only
want specific types returned.

At the same time I only want 1 instance of the chart# to be returned each
day.
When I use group by for all fields I get several instances for each patient
because of the multiple transaction types.

I can accomplish this with 2 queries. In the 1st query I have group by set
for all fields. In the Sub Query I use group by for the Chart# and Date
fields only from the 1st query. This works but seems to run very slowly.

Is there a way I can accomplish this with 1 query.
 
K

Ken Sheridan

Stephan:

Rather than grouping the query use;

SELECT DISTINCT [Chart#], [Date]
FROM etc.

In query design view you do this by selecting Yes fpr the Unique Values
property in the query's properties sheet.

Ken Sheridan
Stafford, England
 
J

John Spencer

Try changing the Totals for Transaction Type from "Group By" to "WHERE"

This will filter the records before it does the grouping and will not show
the transaction type.

SELECT [Chart#], [Date]
FROM YourTable
WHERE [Transaction Type] in (1,2,3,5,7)
GROUP BY [Chart#], [Date]
 
S

Stefan

Worked great!

Thank you
--

Stefan


John Spencer said:
Try changing the Totals for Transaction Type from "Group By" to "WHERE"

This will filter the records before it does the grouping and will not show
the transaction type.

SELECT [Chart#], [Date]
FROM YourTable
WHERE [Transaction Type] in (1,2,3,5,7)
GROUP BY [Chart#], [Date]
Click to expand...
 
Top