Combining records in Select Query

Topic

New Reply

I need to create a report with “table” (It has vertical and horizional lines) so that visually disabled people can read the report…

The problem I am having is that when I run the report, it shows same person several times because that particular person had many to many data such as communication preferences (English, French, Etc.). I could tell report to not print duplicate fields, but that creates blank lines in report with the gridlines. Currently, it looks like this:

John|Doe|German
|Spanish
Jane|Doe|French

I’d like to combine those “several” communication preferences into one single line so that there would be a line that looks like this:

John|Doe|French, English, German
Jane|Doe|French, Spanish
Jeff|Doe|Spanish

Reply | Quote

Replies

The March 2001 issue of Smart Access has the following available online at Smart Access

An Access E-mail Application (free) Keith Bombard
Editorial: We Get Mail (free) Peter Vogel
The Continuous Line Conundrum (online subscribers only) Rich Gorvin
Upsizing Access to Oracle (online subscribers only)

Extract from “The Continuous Line Conundrum”

Invoices frequently have columns for various details and values. How can you make an invoice in Access that’s as smart-looking as a word-processed invoice? The main problem is the age-old one of vertical lines on reports

Reply | Quote

Smart Access didn’t have what I needed. I’m attaching a snapshot of the report that I’m having problems with. If you check out camper named Clarice Thomas, you will see two blank rows after the first row with her name. In that column named “Comm Pref” you will see two additional comm pref. that is under Clarice’s name. I want those to be concantated with the first row.

Brent

Reply | Quote

I’m not certain I totally understand the structure of your report but, if you have several “Comm Pref” fields i.e. [CommPref1], [CommPref2], [CommPref3] etc. you could combine them into just one text box =[CommPref1]&”, “&[CommPref2]&”,”&[CommPref3] Does that make sense?

Reply | Quote

Unfortunately, I cannot set up my table to do such because I have approximately 10 different communication preferences. I certainly don’t want to set up “Com Pref 1, 2, 3” etc…So, I created a many to many relationship between Participant and Comm. Pref table.

Reply | Quote