Single record, multiple contacts - repeat address

[Debbi]

Hi there

I have a single Access table containing names of companies
and their addresses with up to 4 contact & job title
fields for each company. (Sometimes there are less).

I want to be able to create mailing labels for each
contact if that contact exists. If the next contact field
is empty then move onto the next record.

I have scratched my head repeatedly and the most useful
thing I've come up with is splinters! Any ideas
gratefully appreciated.

Many thanks

 

[Peter Jamieson]

I'd consider creating a UNION query in Access and using that as the data
source.

For example, if the fields you needed were name1, title1, address1, name2,
title2, address2, etc. and
a blank name1, name2, name3, name4 indicated that no contact existed in that
slot, the query SQL would look something like

SELECT name1 AS 'Name', title1 AS 'Title', address1 AS 'Address'
FROM mytable
WHERE name1 <> ''
UNION
SELECT name2 AS 'Name', title2 AS 'Title', address2 AS 'Address'
FROM mytable
WHERE name2 <> ''
UNION
SELECT name3 AS 'Name', title3 AS 'Title', address3 AS 'Address'
FROM mytable
WHERE name3 <> ''
UNION
SELECT name4 AS 'Name', title4 AS 'Title', address4 AS 'Address'
FROM mytable
WHERE name4 <> ''

You could add stuff to sequence the records - perhaps

ORDER BY 1

would be enough.

You would then use that query as the data source. However, the chances that
if you are using Word 2002, Word will not let you use a UNION query with its
default connection method (OLEDB) so you would also need to check WOrd
Tools|Options|General|Confirm conversions at open, then select connection
method DDE or ODBC when prompted during the connection setup process.

You can type SQL directly into the Access query designer if you right-click
in the grey area where the tables/relationship graphics are displayed, and
selec the SQL option. You may also need to use table aliases to qualify the
names, e.g.

SELECT t.name1 AS 'Name', t.title1 AS 'Title', t.address1 AS 'Address'
FROM mytable t