Query for mailing title -> this is killing me

[anewor]

Ok so, attempting to write a query for a mail merge...

in my table i'm running it off i have a field for salutation/title, one for first name and one for surname...with ones later on for mailing address (street address, suburb, postcode/zipcode)

i've worked out how to do the query where it picks up the person's title, followed by the first letter of their first name, but how do i do it so it ends up looking like "Mr J & Mrs E Smith" with 2 people on the one line...

can't work out how to say "mr comes before mrs, ms or miss"

Any suggestions?????

Thank you

 

[neileg]

Not sure how you are holding your data. Do you have a separate record for each of the individuals linked to an address in a different table?

Anyway, you could attach a new field to the record where you hold the title for a sort order, so that, for instance, Mr could be 1, Mrs = 2 etc.

However, you are going to run into problems with doctors, professors, etc, where the title is not gender specific, unless you make some sort of compound index to include the gender.

In any event, isn't this a bit old fashioned? I would not be offended if my wife and I received a letter addressed to Mrs P & Mr N

 

[anewor]

Sorry for not being specific......

I'm having difficulty working out how to put 2 or more persons on the one mailing title line.....

unfortunately, the records are kept so that all the personal information is in the one table (in the one record), so it looks like
title, first name, surname, address....

in hindsight i wish it was set it up to link several people to the one address, as this would possible leave less room for errors, but what can you do with several thousand records...

any suggestions in light of my more detailed information??

 

[neileg]

At this point, I don't have a practical answer to your problem. I will give it some thought, however.

in hindsight i wish it was set it up to link several people to the one address, as this would possible leave less room for errors, but what can you do with several thousand records...

Well, Access is designed to deal with thousands of records, so that's what I would use. Here's how I would do it.

1) Run a query that extracts the addresses. You'll have to create a unique key for the address, but I don't know how Aussie post codes, or whatever, work. You might have to compund code and house number. Use this key to extract unique records.

2) Use this query as an append query to a new table that you set up with an autonumber as PK.

3) Run a query between this new table and your original data to identify the relevant address autonumber ID for each person in the data.

4) Make this an append, or make table query, to result in a new table with personal details and a link to the relevant address record.

5) You now have a more properly normalised structure.

This process may throw up some data cleansing issues, but these will be a problem for you sooner or later if you don't tackle it now.

 

[neileg]

Having done a bit of research, it appears that what you want is pretty tricky. You could look at MS support article 141624 but this relates to reports, not queries.

Try a search on this forum based on 'concatenate records' at you'll find some coding solutions to your problem. All a bit beyond me, I'm afraid.

 

[anewor]

Will do.

Thank you very much for your help Neil.

Rowena

 

[dabowery]

Hi Anewor

If I may ask, how did you resolve this issue? Neileg suggested that I look at your post for guidance as I have a similar issue code or query for household mailing scenarios.