Skip a report record?

[Clownfish]

I have a report that joins 12 external tables, and works great.
However, there is a time when I wish to skip printing a particular
record.

Background: The 12 tables all have the same fields, and are "unioned".
One field is [statusbox] and one is [Office]. The report GROUPS the
list by Office.

The logic I wanted to do in the report detail is:

If me.count>1 AND me.[statusbox] = "NO CHANGES" then SKIP THIS RECORD

(in the above code, me.count is counting the number of records in the
group [Office], and [statusbox] is one field within that group).

Now I know I could set up the querry to ignore records who's
[statusbox] field = "NO CHANGES", but I only want to do this if there
is more than one record in this group. If the sole record in the group
happens to be the one who's [statusbox] = "NO CHANGES" then that is
fine, and this record should be printed.

Example one:

Office statusbox
---------------------------
RSO bla bla bla
RSO bla bla bla bla
RSO NO CHANGES
MGT bla bla
MGT bla bla
POL NO CHANGES

in this example, the report will NOT show the 3rd record, because there
are 3 records in the RSO group, AND this 3rd record has the words "NO
CHANGES". However, the last record would print, becuase it only has 1
record.
Thanks!

Pete

[ManningFan]

Why not just omit them from the query running behind the report? You
don't want reports making complex decisions for you, it's very
inefficient.

Clownfish wrote:

I have a report that joins 12 external tables, and works great.
However, there is a time when I wish to skip printing a particular
record.

Background: The 12 tables all have the same fields, and are "unioned".
One field is [statusbox] and one is [Office]. The report GROUPS the
list by Office.

The logic I wanted to do in the report detail is:

If me.count>1 AND me.[statusbox] = "NO CHANGES" then SKIP THIS RECORD

(in the above code, me.count is counting the number of records in the
group [Office], and [statusbox] is one field within that group).

Now I know I could set up the querry to ignore records who's
[statusbox] field = "NO CHANGES", but I only want to do this if there
is more than one record in this group. If the sole record in the group
happens to be the one who's [statusbox] = "NO CHANGES" then that is
fine, and this record should be printed.

Example one:

Office statusbox
---------------------------
RSO bla bla bla
RSO bla bla bla bla
RSO NO CHANGES
MGT bla bla
MGT bla bla
POL NO CHANGES

in this example, the report will NOT show the 3rd record, because there
are 3 records in the RSO group, AND this 3rd record has the words "NO
CHANGES". However, the last record would print, becuase it only has 1
record.
Thanks!

Pete

[Clownfish]

ManningFan wrote:

Why not just omit them from the query running behind the report? You
don't want reports making complex decisions for you, it's very
inefficient.

I'd love to.. I just do not know how to do it.

Peter

[Clownfish]

OK, solved it! I used a HAVING statement and it works perfect!

Pete

[Clownfish]

RATS.. I didn't solve it.

See my recent post, I can't figure out how to count the records
properly.

Clownfish wrote:

OK, solved it! I used a HAVING statement and it works perfect!

Pete

[Lyle Fairfield]

"Clownfish" <cl*********@yahoo.comwrote in news:1155031539.596366.54240@
75g2000cwc.googlegroups.com:

RATS.. I didn't solve it.

This query selects the records from your example data (entered into Test4):

SELECT * FROM Test4 T4
LEFT JOIN
[SELECT Count(*) AS Occurrences, First(Test4.office) AS Office
FROM Test4
GROUP BY Test4.Office]. SQ
ON T4.Office=SQ.Office
WHERE SQ.Occurrences>1

The []. alias syntax is unforgiving. It must be very precise.

You can avoid it by saving

SELECT Count(*) AS Occurrences, First(Test4.office) AS Office
FROM Test4
GROUP BY Test4.Office

as SubQueryforSuchAndSuchReport.

Then the query SQL can be:

SELECT * FROM Test4 T4
LEFT JOIN
SubQueryforSuchAndSuchReport SQ
ON T4.Office=SQ.Office
WHERE SQ.Occurrences>1

--
Lyle Fairfield