Queries that count duplicates

[nina200]

Hi All
I'm trying to write a query that count the individual values in a field e.g a field may have 2003 10 times and 3211 2 times...I would like count (without duplicates)...how do I do this?
Thanks. I tried using COUNT() withing the IIF() statement to no avail.

Nina

 

[orna]

SELECT Table.FieldName, Count(Table.FieldName) AS num
FROM Table
GROUP BY Table.FieldName;

 

[nina200]

hi,
actually i'm trying not to use group-by there. Can't I get this done in the SELECT starement?

like
Select
(iif(tablename.field = tablename.field, count(tablename.fieldname),0)
From Tablename
Where tablename.month = [enter month] AND tablename.year = [enter year];

I would like to have this done in the Select field...Thanks.


Nina

 

[JerryKlmns]

SELECT DISTINCT FieldName, DCount("*", "Table","FieldName=" & [FieldName])
FROM Table

But just to mention that orna's approach is much more efficient and I also recomended it.

 

[lespaul]

and why would you want to avoid using GROUP BY?

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[nina200]

When/if I group this field it influences the values of the summary report...



Nina

 

[PHV]

it influences the values of the summary report
Why not simply do the math in the report ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nina200]

I have to find the number of items (exported and imported) and number of customers (imported and exported) then find the total of all these fields….

Here’s the details…when it comes to items there two types Warehoue and non-warehouse items…Warehouse items decs = “s”. Import/Export = either “E” or “I”.

The table looks like this:

----Imports----------Exports----------Total
Customers (<>s) #of Imports #ofExports #Trade
Item (<> s) # of Imports #ofExports #Trade

Customers (=s) # of Imports #of Exports #Trade
Item (=s) # of Imports # of Exports #Trade


I’ve tried tried the following to no avail:

SELECT DISTINCTROW ASYEXTR_MTH.DECLT
FROM ASYEXTR_MTH
WHERE ASYEXTR_MTH.MONTH = [ENTER MONTH] AND ASYEXTR_MTH.YEAR = [ENTER YEAR] AND ASYEXTR_MTH.DECS <> “S”;

This particular query (above) generates the total Customers Import/Export for a specified period that DID NOT deal with warehouse items.


SELECT DISTINCTROW ASYEXTR_MTH.DECLT
FROM ASYEXTR_MTH
WHERE ASYEXTR_MTH.MONTH = [ENTER MONTH] AND ASYEXTR_MTH.YEAR = [ENTER YEAR] AND ASYEXTR_MTH.DECS = “S”
GROUP BY ASYEXTR_MTH.DECLT;

This particular query (above) generates the total Customers Import/Export for a specified period that dealt with warehouse items).

Can somebody please help me.
Thanks.


Nina

 

[lespaul]

Unclear on what the problem is? Do you want to combine these and know if it was warehouse or not?

SELECT DISTINCTROW ASYEXTR_MTH.DECLT, "No Warehouse"
FROM ASYEXTR_MTH
WHERE ASYEXTR_MTH.MONTH = [ENTER MONTH] AND ASYEXTR_MTH.YEAR = [ENTER YEAR] AND ASYEXTR_MTH.DECS <> “S”
UNION
SELECT DISTINCTROW ASYEXTR_MTH.DECLT, "Warehouse"
FROM ASYEXTR_MTH
WHERE ASYEXTR_MTH.MONTH = [ENTER MONTH] AND ASYEXTR_MTH.YEAR = [ENTER YEAR] AND ASYEXTR_MTH.DECS = “S”





Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[PHV]

Anyway, the DISTINCTROW predicate is meaningless when only one table is used ...

Have you considered something like this:
SELECT IIf([DECS]='S',[DECLT],Null) AS WarehouseCLT, IIf([DECS]='S',Null,[DECLT]) AS NoWarehouseCLT
FROM ASYEXTR_MTH
WHERE [MONTH] = [ENTER MONTH] AND [YEAR] = [ENTER YEAR]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[nina200]

Thanks Yall...I got it working by using the
DCount() function...
eg:
DCount("[fieldname]", "TableName", "criteria")

Thanks again.



Nina