How not to count duplicate records in report

[qfillmart]

I have a simple report which looks as given below:


ID Number Products
122 Cups
133 Pencils
122 Cups
133 Pencils

When I use the count function I get the total count of the record as 4
what I want is count the product varieties which should be 2

Thanks for your help

ps I am not that efficient in VBS

 

[Minty]

If the report is based on a query make sure you it starts with SELECT DISTINCT.

If you post up the SQL of the record source we can check it further.

 

[qfillmart]

Thanks a lot I will try this

 

[qfillmart]

I did what you had told me and it worked. Thanks for the tip.

However I forgot to mention one thing which I realized once I used the Select Distinct. My query had one more column Color

so now my actual query looks like this

ID Number Products color
122 Cups White
133 Pencils red
122 Cups white
133 Pencils red

how to include third column "Color" in the result.

When I did Select Distinct ID from the table name it gave me

ID
122
133

I want the result to be as given below:

ID Number Products color
122 Cups White
133 Pencils red

Thanks

 

[Minty]

Please post up your query as it is now.

 

[qfillmart]

I kept playing with it and finally figured it out. Tks for your offer.