count all and uniques records in a table

try this query, using the id field of your table

select IdField, count(idField)
from TableX
group by IdField

You can use:

Select Count(*) From
    (Select Distinct Field1, Field2, Field3, ..., Fieldn From YourTable) As T

Select allOpen in new window

where you include the fields that should be unique. Exclude Memo fields.

/gustav

I have using both suggestions in a union query just cant understand the system resources issues

post the SQL statement what you are using to get the unique count

A union query? You neither mentioned that, nor need such one.

Or try with not "Union Select ..." but "Union All Select ..."

/gustav

Define "unique".  Create a new table with an autonumber primary key and a unique index to prevent logical duplicates from being added.  Create an append query to copy the imported records to the new table.  Only the first instance of a "duplicate" will be added.  The rest will be discarded.

this is what i am using that's giving me the system resource error
SELECT DISTINCT Count(t.Sequencenumber) AS CountOfSequencenumber
FROM (SELECT DISTINCT Sequencenumber FROM New_Results_list)  AS t
HAVING (((Count(t.Sequencenumber)) Is Not Null));
UNION SELECT Count(*) AS CountOfsequencenumber
FROM New_Results_list
WHERE (((New_Results_list.sequencenumber) Is Not Null));

try this, copy and paste then run. see if it gives you what you want

select Sequencenumber, Count(Sequencenumber) as CountOfSequencenumber
from New_Results_list
where Sequencenumber is not null
group by Sequencenumber
union
select 'Total Count', count((*)  as CountOfSequencenumber
from New_Results_list
where Sequencenumber is not null

syntax errors

oops, too many "("

select Sequencenumber, Count(Sequencenumber) as CountOfSequencenumber
 from New_Results_list
 where Sequencenumber is not null
 group by Sequencenumber
 union
 select 'Total Count', count(*)  as CountOfSequencenumber
 from New_Results_list
 where Sequencenumber is not null

it's not giving me the desired results

ok now post sample values and post desired result. use an excel file for the sample and desired result and upload here.

CountOfSequencenumber
44089
128591

one is a distinct value the other is the regular count

post the result returned by the query I posted?

Sequencenumber      CountOfSequencenumber
it has 44090 records

Total Count      128591

so, what is the result?

that's it Rey,I will try to see if i get the system resource error message
Thanks