Grouping

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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]
 
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...
 
Back
Top