Omitting certain returned values

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

EddieLampert - 22 Apr 2008 17:57 GMT

Hello all. I have a query that breaks down comments I have made by company.
So let's say I have 10 comments per company. How do I make it to where only
the first comment of each company has all the information (such as state,
contact person, phone number, etc), and then the following 9 comments leave
out that info, and only have the comment? Same thing for the following
companies: the first row has all the info, the remaining rows only have the
last column (comment) filled out. Thanks a lot.

Reply to this Message

John Spencer - 22 Apr 2008 18:28 GMT

In a query, you don't. Query rows are all indentical.

In a report you have some options -
Suppress duplicates on the controls
or
use a report for all fields except the comments and sub-report for the comments
or
use grouping to put all the information in the group level and just the
comments at the detail level.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Hello all. I have a query that breaks down comments I have made by company.
> So let's say I have 10 comments per company. How do I make it to where only
[quoted text clipped - 3 lines]
> companies: the first row has all the info, the remaining rows only have the
> last column (comment) filled out. Thanks a lot.

Reply to this Message

EddieLampert - 22 Apr 2008 18:49 GMT

Thanks, a lot, John. I am in the process of constructing a report based on
certain fields from various tables and queries that I would like to display
in an organized format. One of the tables I have has a list of companies,
while another table has a list of comments and the respective comments for
many of the companies that are in the other table. When I construct a report
I get an output like the following

Company Month Comment
XYZ Jan **********
XYZ Feb **********
XYZ Feb **********
WER Jan **********
WER Feb **********
WER Feb **********
WER Mar **********
ZXY Feb **********
ZXY Feb **********
ZXY Mar **********
ZXY Apr **********

What I would like is for each company to be on its own separate page like
the following:

Company - XYZ

Month Comment
Jan **********
Feb **********
Feb **********

<next page>

Company - WER

Month Comment
Jan **********
Feb **********
Feb **********
Mar **********

> In a query, you don't. Query rows are all indentical.
>
[quoted text clipped - 18 lines]
> > companies: the first row has all the info, the remaining rows only have the
> > last column (comment) filled out. Thanks a lot.

Reply to this Message

John Spencer - 22 Apr 2008 19:46 GMT

In the report set up a group on Company

In design view,
--select View: Sorting and Grouping from the menu
--Select Company as your field expression
--Set Group Header to YES
--Close the dialog
-- Drag the Company control from the detail section into the header
-- Select View Properties from the menu
-- Click on the group header line
-- On the property dialog, click format and set Force New Page to Before Section

I'm not sure how to handle the Month since sorting by the month abbreviation
will be alphabetical instead of chronologic. If the field is really a date
field then

--select View: Sorting and Grouping from the menu
--Select theMonthDatefield as your field expression
--Close the dialog

If you want to suppress the repeating months, then click on the control and
set Hide Duplicates for the control to Yes.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

> Thanks, a lot, John. I am in the process of constructing a report based on
> certain fields from various tables and queries that I would like to display
[quoted text clipped - 58 lines]
>>> companies: the first row has all the info, the remaining rows only have the
>>> last column (comment) filled out. Thanks a lot.

Reply to this Message