need help - single report of labels with multiple addresses in the same row

[Sherron1]

[FONT=&quot]Hello, everyone. I am trying to create mailing labels. The main table is set up to contain several contacts (in columns) for each client (in rows), contact1name, contact1address1, contact1address2, contact1city, contact1state, contact1zip, contact2name, etc. There are 5 possible contacts for each client. We have 4 different mailings, for which there is a checkbox associated with each possible contact, like a checkbox for mailing1contact1, for mailing2contact1, etc. In addition, all these clients are served by 4 different reps.

I am trying to create a single report of labels for each mailing, but that contains the addresses for all contacts 1-5, and sorted by zip code, but I haven't even made it close to worrying about that yet.

Eventually, I will also need to create one report for each mailing for all 5 contacts, but for each rep's individual client list (and again sorted by zip code). Some mailings are done by the individual reps, others are done by one person.

I am stumped. I have tried a query that shows contact1's info and contact 2's info (figured no need to make it more complicated by adding more if I can't get 2 to work), for mailing1, but I don't know how to pull both over into labels. My knowledge is limited, so I have relied on the label wizard, which does not offer a solution to my dilemma.

Please let me know if you need more information than I have provided, and thank you in advance for your help and suggestions. [/FONT]

 

[Sherron1]

Not sure if it is easier to understand if you can see what I am trying to say, so I've attached a very simplified version with 3 dummy records. Again, thank you any assistance you can provide.

 

[vbaInet]

Perhaps you should show us a word version of what exactly you're trying to achieve.

 

[Sherron1]

I'm trying to achieve a set of labels. 2 columns, 7 rows of labels. What the report will look like in the end is not even critical at this point... I can do the label portion of it, but I don't know how to get multiple contacts for a single client across, and all sorted by zip for a mass mailing.

I'm trying a work around and changed the set up of the contacts table, instead of info for contacts 1-5 in separate fields, I have only one field for contacts, address1, etc, that will contain all the contacts as separate records... regardless of what kind of contact they are, and I added another field to select the type of contact. I think this will solve the label problem, since there is only one field containing zip codes in the entire table, and sorting should be easy.

Unfortunately, I cannot test my theory, since I now have issues with my form to enter contacts. I'm thinking subform is the way to go, client in the main form, contacts in the sub? I have a one-to-many relationship set up from clients to contacts, and checked the enforce referential integrity, cascade update, and cascade delete boxes. I am now stuck trying to figure out how force the contacts fkey field to update as soon as a client record is created. An append query seemed to be the way to go, but I can't get the warnings to turn off, even after shamelessly stealing code online.

Hope this explains my problem more clearly...


ETA:
There are round 300 clients with up to 5 contacts each. Think:
ClientA – Contact1, Contact2, Contact3, Contact4, Contact5
ClientB – Contact1, Contact2, Contact3, Contact4, Contact5

To clarify, my use of the word "Contact" here actually represents several fields in the table, such as ContactFirst, ContactLast, ContactTitle, ContactAddress1, etc...The labels in the end need to have the addresses ClientA/Contact1, ClientA/Contact2 [...] ClientZZ/Contact1, and so on... and all sorted by zip code

There are also Reps1-4 in the mix. I will also need to separate some of the mailings by Reps... think, Rep1/ClientA/Contact1 [...] Rep4/ClientZZ/Contact5... etc. Again, sorted by ZIP, but 4 sets of labels, one for each Rep.

I am not "married" to any solution, so if there are different suggestions on how to set this all up to get the desired result... I am all ears... again, thank you for your time.

 

[vbaInet]

But I still need to see a Word version of the expected output. We're dealing with a database here, this is an important aspect of the solution.