How do I modify a query and consolidate data?

  • Thread starter Thread starter Destiny
  • Start date Start date
D

Destiny

Let me preface this to say I am a Very new user.

I am looking at a query that has repeated records except for one piece of
information. I am assuming that it was created this way, so that someone
could create seperate labels with this piece of information.

I have been asked to find a way to consolidate the data onto one record, but
retain the data so that it may be pulled up when running a report. How does
one do that?

Also is this a case of having multiple records in one query and one report
or one query and multiple reports.

The goal is to have one record w/multiple data and one report. i.e.

LastName; FristName
Title
Address
Committee1
Committee2
Committee3


Thanks
 
Hi Destiny,

First, the good the fact that your query is returning multiple records
with (mostly) duplicate data indicates that our underlying table structure
is correct. I assume that, in the example you showed, the three CommitteeN
entries are multiple records, not multiple fields (which would indicate a
probem with the data structure).

You should not be trying to consolidate the data in a query, but should use
the standard features of a report to present the data with the required
(apparent) consolidation. There's at least two ways to do this:

1. Use Grouping in your report. In the Sorting/Grouing dialog, group by
PersonID (your field name will almost certainly be different - it's likely
to be the primary key field); put the repeating fields into the group
header, and the multiple field(s) into the detail section.

2. Leave all fields in the duplicate section, but set the Hide Duplicates
property of the controls for the fields you don't want repeated to Yes.
Also set the Can Shrink property of those controls to Yes, and the Can
Shrink property of the detail section itself to Yes. You'll need to sort by
your primary key, and it seems that you're already doing that (you should be
setting that sort order explicitly in the report's Sorting/Grouping dialog,
rather than relying on a sort order set in the query).

HTH,

Rob
 
Back
Top