removing duplicates in a query?

  • Thread starter Thread starter Orv
  • Start date Start date
O

Orv

I have a query where I need to count the records (sum). This works great
however one of my fields has a case number (ie 03-001). This number is
associated with either one or more names. So when I run the query and do a
count, it's actually counting the duplicate case numbers as well.

Is there a way to do a count/sum and not count the duplicate numbers?

Thanks,

Orv
 
Probably something like

Select Distinct Case_Num, sum(sales), Count(*) from mytable.
(e-mail address removed)
 
Do you need to count both the cases and the names? Do you need to display
the names?
Create a CaseNumber header and add a text box:
Name: txtCountCase
Control Source: =1
Running Sum: Over All
Visible: No
Add a text box to the Report Footer section:
Control Source: =txtCountCase
 
Not sure where to place your possible solution. In the table, query or
report?

I'm just counting the number of cases by way of counting the case
numbers.(which shows up multiple times in my table when more than one name
is associated with this record)
I think I may be able to place "Number of Dups: Case Number" into the
query field, Total: count and Criteria: >1. Then all I would have to do is
subtract the dups (duplicates) from the total count.
Is this correct? Am I at least on the right track?
 
Back
Top