Calculation to eliminate duplicate counting in report

[cav]

I have a report with these fields represented. [Product] which is grouped, [Ordered#],and [#Sent] I have the sum of the ordered# and the #sent. When I use =Count[Product] it counts the total mentioned including duplicates. How do I get a count of the Products eliminating the the duplicates. Thanks for your help.cav

 

[DougP]

Well first you have to eliminate the dulicates<br>
you don't have to delete them out of your database.<br>
Now you can create a query that looks at one field at time.<br>
say Ordered<br>
Open the query grid and add just the &quot;Ordered&quot; field and nothing else<br>
click &quot;Query&quot; Menu<br>
click &quot;Crosstab query&quot; which will add a parameter<br>
Then<br>
click &quot;Select Query&quot; what this does is remove one paramter<br>
<br>
Run your query and you'll see that are no duplicates.<br>
<br>
Now you need to some to some how use the &quot;group by&quot; clause in your field statement.

 

[DougP]

Sorry the last line has a slip of the fingers. It should read.<br>
&quot;Now you need to some how use the Group by in your field&quot;<br>
<br>
Also do you know how to create a &quot;Function&quot; in Access?<br>
Try adding another unbound text box to your report<br>
its control source can be something like<br>
=MyCount([Product])<br>
<br>
Now in the function you will count all of the instances but have a &quot;group by&quot; SQL statement that looks at all of the products you pass to it.<br>
<br>
NOTE:<br>
IF you have a lot of records this may slow your report way down.<br>

 

[DougP]

I got this while working on something else<br>
<br>
In (SELECT [SerialNum] FROM [Models] As Tmp GROUP BY [SerialNum] HAVING Count(*)&gt;1 )<br>
<br>
Substitue your fields and give it a try<br>

 

[cav]

Thank you for the direction.

 

[Husein]

Why don't you try using DISTINCT to remove the duplicates?

 

[cav]

How, Where, When do I use DISTINCT? Report? Query? Will the end result on a report eliminate the Product name duplications giving me a count of the product...but still sum the orders# and sent#? <br>
<br>
I ran the query and was able to eliminate the duplicate product name but now I'm not sure of the next step. As you can see I need all the help I can get.

 

[elizabeth]

If you are using the query grid, select the &quot;Unique Values&quot; property of the query (under View) and DISTINCT will be inserted into your SQL code.

 

[Husein]

You can also place DISTINCT in SQL text window:<br>
SELECT DISTINCT (the rest goes here), but if you are doing counting then<br>
SELECT COUNT(DISTINCT *) .......