Find duplicates but not blank values

[Guest]

Is there a way to show duplicates from a query without blank fields showing
in list?
This is what I am using in query grid from duplicate query wizard:

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp GROUP BY [SERIAL #] HAVING
Count(*)>1)

Some records have no serial numbers, but I don't want to see those in results.

Thanks,
Pam

 

[John Spencer]

I would guess that some of the Serial # are zero-length strings or are
mutliple spaces
You might modify the subquery to string those out.

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp WHERE Trim([Serial #] & "")
<> "" GROUP BY [SERIAL #] HAVING
Count(*)>1)

 

[Guest]

John,

Thank you so much! It works perfect!

Pam

I would guess that some of the Serial # are zero-length strings or are
mutliple spaces
You might modify the subquery to string those out.

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp WHERE Trim([Serial #] & "")
<> "" GROUP BY [SERIAL #] HAVING
Count(*)>1)

Is there a way to show duplicates from a query without blank fields
showing
in list?
This is what I am using in query grid from duplicate query wizard:

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp GROUP BY [SERIAL #] HAVING
Count(*)>1)

Some records have no serial numbers, but I don't want to see those in
results.

Thanks,
Pam

 

[Guest]

I am trying something similar, but also want the query to return the count of
duplicate records as aggregates, maybe by creating a new field and returning
the number of duplicates in that field and displaying the original field of
the query as well. I used the query that PHisaw posted, only changing the
table and field names, which did return only duplicate records. I only now
would like the query to display the count of duplicate records for each
aggregate. My query is posted below. Can someone adapt it to fit my needs
explained above?

In (SELECT [NEWFIELD1] FROM [TABLE22] As Tmp GROUP BY [NEWFIELD1] HAVING
Count(*)>1)
--
Barry Guidry

John,

Thank you so much! It works perfect!

Pam

I would guess that some of the Serial # are zero-length strings or are
mutliple spaces
You might modify the subquery to string those out.

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp WHERE Trim([Serial #] & "")
<> "" GROUP BY [SERIAL #] HAVING
Count(*)>1)

Is there a way to show duplicates from a query without blank fields
showing
in list?
This is what I am using in query grid from duplicate query wizard:

In (SELECT [SERIAL #] FROM [DbArchives] As Tmp GROUP BY [SERIAL #] HAVING
Count(*)>1)

Some records have no serial numbers, but I don't want to see those in
results.

Thanks,
Pam

 

[Guest]

I have a similar situation in which I would like someone to help me take the
query (below) further to create a new field automatically and populate it
with the count of duplicates in each aggregate of duplicate records, while
displaying the field and record name, which contains the duplicates, beside
the new field.

In (SELECT [NEWFIELD1] FROM [TABLE22] As Tmp GROUP BY [NEWFIELD1] HAVING
Count(*)>1)