If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How do I modify a query and consolidate data?
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 |
#2
|
|||
|
|||
How do I modify a query and consolidate data?
Hi Destiny,
First, the good news: 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 Destiny wrote: 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 |
Thread Tools | |
Display Modes | |
|
|