RE: How do I eliminate rows where field value count is =>10?
- From: Duane Hookom <duanehookom@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 17 Apr 2008 17:36:02 -0700
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;
--
Duane Hookom
Microsoft Access MVP
"Penny" wrote:
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
"Duane Hookom" wrote:
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.
--
Duane Hookom
Microsoft Access MVP
"Penny" wrote:
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
- References:
- How do I eliminate rows where field value count is =>10?
- From: Penny
- RE: How do I eliminate rows where field value count is =>10?
- From: Duane Hookom
- RE: How do I eliminate rows where field value count is =>10?
- From: Penny
- How do I eliminate rows where field value count is =>10?
- Prev by Date: Re: Move subform
- Next by Date: Re: Font size shrinks beyond what's specified
- Previous by thread: RE: How do I eliminate rows where field value count is =>10?
- Next by thread: Re: ConvertReport2PDF Function
- Index(es):
Relevant Pages
|
