Query Problem

M

Martin

I have two tables--DOCMASTER and DOCOFFICES--linked by a DocId field. This
is a one to many relationship, one doc can have many offices. I need to
create an extract of this information to supply an outside vendor. The
problem, as I see it, is creating the format of the file they want.

What the vendor wants is a file that would have a structure like the
following:
DocId
Office 1 ID
Office 1 Address
[Other fields for Office 1]
Office 2 ID
Office 2 Address
[Other fields for Office 2]
Office 3 ID
Office 3 Address
[Other fields for Office 3]

I have simplified the structure of the output file, but in total there are
234 fields that are on each record. (I cannot get around this file
structure.)

I can create a query that will produce a list of offices, but each office is
on a separate record. Is there a way that I can get the office information
on one record? How can I determine that one doc may have 1 office, and
another has 3 offices and to populate the export fields accordingly?

Thanks.
 
D

Duane Hookom

I would probably write code to open two DAO recordsets. You could then loop
through all the records in your current table and append/update records in
the "wider" table to send to your vendor.
 
Top