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

[Penny]

Hi. 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

 

[a a r o n . k e m p f]

you need a delete statement

make a new query; open it in SQL view (under the VIEW menu, select
SQL)

Delete From Table Where RecordID IN (Select RecordID From
qryIDYouWantToDelete)

-Aaron

 

[Penny]

Hi Aaron. I thank you so much for your response! However, I don't think I
explained this well enough.....

I have a huge table. The identifier field contains duplicate values. I
want to eliminate all rows where an identifier number appears 10 times or
more in the field. I'm not referring to only one record, but to several. So
I cannot enter the ID for one person only; I need to remove ALL the people
who have their ID number in the table 10 times or more.

I can create a query to remove the duplicates of 10+ in the table, as you
suggested, if I can figure out how to write the code in the query to remove
these records.

Is this any clearer? Please forgive my poor wording, and thanks again for
your help.

It's late here... almost midnight, so I will check again in the morning to
see if you have replied.

Have a good night!
Penny

 

[George]

Penny:

There is a huge difference between deleting records, and suppressing them so
you only show one instance in a report.

For goodness sake I hope you didn't listen to any foolish, dangerous advice
about actually deleting duplicate records.

You can construct a query that will include records based on the number of
times a unique value appears in a field, and I'll come back shortly with an
example, but for now, please do NOT start deleting records.

George

 

[George]

You say that your requirement is to SHOW certain records, but not others.
Your requirement is NOT to delete those records, is it?

That's a huge difference. Please post back with your exact requirement, so a
valid suggestion can be made.

George

 

[George]

Penny:

Your requirement was: Return a recordset including only records in which the
"uniqueidentifier" field appears fewer than 10 times in the table. You can
do this with a query stuctured like this.


SELECT tblYourTable.UniqueIdentifier
FROM tblYourTable
GROUP BY tblYourTable.UniqueIdentifier
HAVING Count(tblYourTable.UniqueIdentifier)<10

This query will return one field: the field containing the UniqueIdentifier.
It will only return values which appear 9 or fewer times in the table named
tblYourTable. Any other UniqueIdentfier values will NOT be returned.

Unfortunately, the exact way you'll use this query depends a little bit on
other factors, so I can only hypothesize how it might work

I assume that you need other fields from tblYourTable in the report, so this
query by itself would be insufficient. However, you could use it as a
SUB-query in the query that provides records to the report, something like
this.

Select tblYourTable.UniqueIdentifier , tblYourTable.FieldOne,
tblYourTable.FieldTwo, tblYourTable.FieldThree
FROM tblYourTable
WHERE tblYourTable.UniqueIdentifier IN (SELECT
tblYourTable.UniqueIdentifier
FROM tblYourTable
GROUP BY tblYourTable.UniqueIdentifier
HAVING Count(tblYourTable.UniqueIdentifier)<10)

This query, as the recordsource for your report, will show records that meet
your criteria WITHOUT having to delete any records. You can display the
fields returned in controls on the report.

HTH

George

 

[BruceM]

I agree it is unclear whether "eliminate" means "delete" or "not show". To
the OP, I agree with George that you can filter the recordset without
actually deleting records. I do not know how your database is put together,
or the nature of the records, but there likely is no reason to eliminate
records unless they literally are duplicate records (from importing the same
data several times, for instance), but in that case it seems unlikely that 9
duplicates is OK but 10 is too many.

 

[George]

Precisely, the point, Bruce.

In my opinion, the difference between "do not include" and "delete" is clear
and unabigiuous. The poster asked for a way to SHOW the records having a
count of the uniqueidentifier field less than ten while NOT INCLUDING
records having a count of the uniqueidentifier field greater than ten.

Although anyone reading only the subject line could have been misled, the
actual situation is quite clear in the body of the question, "But if 1034
appears 10 times or more in the same field, do not include these rows in the
report."

If the poster had asked for a way to eliminate duplicate records whenever
the number of duplicates was less than 10 (or more than 10), a delete query
would have been relevant.

As it stands, Aaron's suggestion that the poster create a delete query was
irresponsible and potentially dangerous. Had the poster started deleting
records based on that suggestion, she would have destroyed her own data
based on that suggestion.

That kind of recklessness is unforgivable.

 

[BruceM]

It would seem the reply suggesting a delete query was based on a quick
reading of the subject line. I agree with you that a closer reading of the
message argues against deleting records.

 

[George Hepworth]

Bruce:

That's the whole crux of this problem. This continues a pattern of behavior
we've seen here for weeks. Sometimes Aaron's responses are merely silly and
funny. Sometimes they are vulgar and insulting. This one was potentially
harmful.

Here's the thing. Most of us are mindful of the huge responsibility we
assume when attempting to provide useful, relevant, appropriate answers to
posters' questions. Like the majority of people who post here, when I am not
clear about the actual goals and requirements behind a question, I usually
ask clarifying questions to be sure I

a) understand what the user needs and wants, and
b) can offer something of value as an answer.

The rare exception is this troll who cares nothing about the poster, who
cares not at all whether his answers are relevant, helpful or even coherent.

If we could safely ignore him, that would be the best course of action.
Unfortunately, as this example illustrates, it is not safe to do so.

 

[Penny]

Hi Bruce, George, and all.

I really appreciate the time you have spent assisting me. Thank you for that.

When Aaron suggested that I delete records in my table, I know enough to not
do that. I would have made a copy of the table to use as a source for my
report. However, you are correct that his solution is not what I asked for.

I have already created a report that shows all the ID numbers, duplicates
and all. As you realize, I only want to see ID numbers that occur less than
10 times in the field. This report is 1200 pages long, and we want to make
it shorter by suppressing the people we don't need to see (those whose ID
number appears 10 times or more).

You are correct that I have added other fields from the table to the report,
and also the table is linked to another table from where I needed one field.
They are linked by this uniqueidentifier field (which we know is not actually
a unique field as it contains duplicate ID number values!).

While the code you wrote sounds perfect, as you suggested, you do not have
all the information.

I didn't base my report on a query. The table is the direct record source.
This table is not linked to a live db, but rather a static table I created,
by merging several tables into one and adding fields, etc. So, I could copy
the table, create a query on the copy and then build the report based on the
query. However, since the report is already built, can I use this code in
the report itself, to save me some time?

Someone else has suggested I use this in my report:

SELECT UniqueIdentifier
FROM tblwithUniqueIdentifier
GROUP BY UniqueIdentifer
HAVING Count(*) <10;

Would this work? It seems very close to yours but yours is more complete.
Why does the example above show an asterisk, but yours below does not? What
does the asterisk indicate, please? If you could tell me which way to go,
and how to enter it in the report, I will give it a try.

Sorry to be such a newbie to writing code.... lol.... I guess we all start
somewhere! I really need to take a course!!

Thanks again.
Penny


Or, do I need another

 

[George Hepworth]

You have no need to apologize. We all started somewhere and we're all trying
to learn more as we go.

And I didn't mean to insult you by suggesting you'd be silly enough to
actually start deleting records. I apologize for that.

COUNT(*) returns the number of items in a group. This includes NULL values
and duplicates.
COUNT( expression) evaluates expression for each row in a group and returns
the number of NONNULL values.

Count(*), I believe, should be faster and it will include records where
there is a NULL in the UniqueIdentifier field in your table.

I doubt that you have Nonnull values in the relevant field in your table,
but that is a possibility and you should choose the option best suited to
your needs. Actually, go ahead and try it both ways to see what differences,
if any, you can see. Given the number of records you have (120 pages in the
report!) , it would be worthwhile to experiment for both speed and results.

Given your expanded explanation of the recordsource for the report, I would
recommend that you can use static reporting temp table you have in a query,
just as I showed you with the subquery:



Select * from tblYourStaticTable
Where tblYourStaticTable.UniqueIdentifier IN
(SELECT tblYourStaticTable .UniqueIdentifier
FROM tblYourStaticTable
GROUP BY tblYourStaticTable .UniqueIdentifier
HAVING Count(*)<10)



Save this as a new query and make it the recordsource for the report instead
of the static table.

 

[Penny]

Hi George. This is fantastic!

You in no way insulted me, by the way. I understand your concern. Someone
with even less knowledge that I have might have actually deleted their own
records.

All the records have a number in this uniqueidentifier field, so there are
no null values. I know this for sure because I created the table.

This should do the trick, assuming I do it right. If I understand you
correctly, I am to create a query using the code you gave me. I will enter
it in the SQL view and run the query. Then I will use the query rather than
my static table, as a source for my report. I will try this today, and let
you know how it goes.

Thanks again,
Penny

 

[Penny]

Hi George. I seem to have a new problem.

I created the query as you suggested, and it ran fine (thanks). I then
tried to create the report. I had to add that second table to get the one
field I mentioned (field is tbl2.ID). The report wouldn't let me do it. I
got this error message:

"You have chosen fields from these tables: tbl1, tbl2; One or more of the
tables isn't related to the others. Click OK to edit system relationships.
You'll need to restart the wizard. Click Cancel to return to the wizard and
remove some fields.

So, I decided to try to link the second table in my query instead of in the
report wizard. I added the tbl2 table to the query and linked it to my first
static table. I then tried to run the query again. This time it would not
run, and I received this message:

"The specified field '[Query for Report].UniqueIdentifier' could refer to
more than one table listed in the FROM clause of your SQL statement."

When I go back to the SQL view in the query, it has changed now as a result
of my trying to link the second table, to this below. I have changed the
table names to tbl1 and tbl2 for privacy:

SELECT tbl2.ID, *
FROM tbl1 LEFT JOIN tbl2 ON tbl1.UniqueIdentifier = tbl2.UniqueIdentifier
WHERE (((tbl1.UniqueIdentifier) In (SELECT tbl1.UniqueIdentifier
FROM tbl1
GROUP BY tbl1.UniqueIdentifier
HAVING Count(*)<10)));


Hmm.... any idea what I've done wrong, or how I can fix this so I can add
this second table?

Thank you very much.
Penny

 

[George]

Hi,

If the field you want to filter on is in both tables, then the subquery
should work like this.



SELECT tbl2.*
FROM tbl2 WHERE
tbl2.UniqueIdentifier In (SELECT tbl1.UniqueIdentifier
FROM tbl1
GROUP BY tbl1.UniqueIdentifier
HAVING Count(*)<10)





"Penny" <Penny@discuss

ions.microsoft.com> wrote in message

Hi George. I seem to have a new problem.

I created the query as you suggested, and it ran fine (thanks). I then
tried to create the report. I had to add that second table to get the one
field I mentioned (field is tbl2.ID). The report wouldn't let me do it.
I
got this error message:

"You have chosen fields from these tables: tbl1, tbl2; One or more of the
tables isn't related to the others. Click OK to edit system
relationships.
You'll need to restart the wizard. Click Cancel to return to the wizard
and
remove some fields.

So, I decided to try to link the second table in my query instead of in
the
report wizard. I added the tbl2 table to the query and linked it to my
first
static table. I then tried to run the query again. This time it would
not
run, and I received this message:

"The specified field '[Query for Report].UniqueIdentifier' could refer to
more than one table listed in the FROM clause of your SQL statement."

When I go back to the SQL view in the query, it has changed now as a
result
of my trying to link the second table, to this below. I have changed the
table names to tbl1 and tbl2 for privacy:

SELECT tbl2.ID, *
FROM tbl1 LEFT JOIN tbl2 ON tbl1.UniqueIdentifier = tbl2.UniqueIdentifier
WHERE (((tbl1.UniqueIdentifier) In (SELECT tbl1.UniqueIdentifier
FROM tbl1
GROUP BY tbl1.UniqueIdentifier
HAVING Count(*)<10)));


Hmm.... any idea what I've done wrong, or how I can fix this so I can add
this second table?

Thank you very much.
Penny

You have no need to apologize. We all started somewhere and we're all
trying
to learn more as we go.

And I didn't mean to insult you by suggesting you'd be silly enough to
actually start deleting records. I apologize for that.

COUNT(*) returns the number of items in a group. This includes NULL
values
and duplicates.
COUNT( expression) evaluates expression for each row in a group and
returns
the number of NONNULL values.

Count(*), I believe, should be faster and it will include records where
there is a NULL in the UniqueIdentifier field in your table.

I doubt that you have Nonnull values in the relevant field in your table,
but that is a possibility and you should choose the option best suited to
your needs. Actually, go ahead and try it both ways to see what
differences,
if any, you can see. Given the number of records you have (120 pages in
the
report!) , it would be worthwhile to experiment for both speed and
results.

Given your expanded explanation of the recordsource for the report, I
would
recommend that you can use static reporting temp table you have in a
query,
just as I showed you with the subquery:



Select * from tblYourStaticTable
Where tblYourStaticTable.UniqueIdentifier IN
(SELECT tblYourStaticTable .UniqueIdentifier
FROM tblYourStaticTable
GROUP BY tblYourStaticTable .UniqueIdentifier
HAVING Count(*)<10)



Save this as a new query and make it the recordsource for the report
instead
of the static table.