How do I eliminate rows where field value count is =>10?

  • Thread starter Thread starter Penny
  • Start date Start date
P

Penny

Hi. I posted this under general questions but then realized I should have
posted it here. If I have created a duplicate post, my apologies. I'm new
here and learning.

I am building a report in Access 2003 from a table.

I only want to show the records where a value in one field, named
"uniqueidentifier" appears less than 10 times in the table. For example, if
the number 1034 appears less than 10 times in the uniqueidentifier field,
then include these rows in the report. But if 1034 appears 10 times or more
in the same field, do not include these rows in the report.

Does it go something like this, uniqueidentifer =Count <10?
I'm sure I'm waay off base!

I not only need instructions about how to write this expression, but how do
I create it in my report? In the properties for the field? Which property?
Data source?

So many questions I know. I need this for work so if anyone out there has a
speedy response, I really would appreciate it.

Thank you very much for your time!

Penny
 
I'm a bit surprised that a field named "uniqueidentifier" is not a unique
identifier (it has duplicates). You can create a totals query like
SELECT UniqueIdentifier
FROM tblwithUniqueIdentifier
GROUP BY UniqueIdentifer
HAVING Count(*) <10;

Add this totals query to the Record Source of your report and join the
UniqueIdentifier fields.
 
Hello, and thank you for your reply! You're right; it's not a very good name
for a field that contains duplicates, but nevertheless, there certainly are
duplicates.

I have a question.. I don't understand what I am to do when you say, "and
join the
UniqueIdentifier fields." Can you explain this please? What exactly does
it mean?

Sorry, I'm pretty new to this.... Thanks again.
Penny
 
Ok, let's assume you want to query the OrderDetails table in the sample
Northwind mdb. There is an repeating OrderID field in this table and we only
want to return records with OrderID values with less than 3 similar OrderIDs.
The query would be:

SELECT [Order Details].OrderID
FROM [Order Details]
GROUP BY [Order Details].OrderID
HAVING Count([Order Details].OrderID)<3;

Save this query as "qgrpLessThan3OrderIDs". You could then add this query to
a new query with the Order Details table to return all records from the Order
Details where the OrderID repeats less than 3 times.

SELECT [Order Details].*
FROM qgrpLessThan3OrderIDs INNER JOIN [Order Details] ON
qgrpLessThan3OrderIDs.OrderID = [Order Details].OrderID;
 
Back
Top