My users want an excel spreadsheet where every row in the table is a row on the report. No problem. Today they tell me I need to join to another table that is one to many (up to 10 rows on the table we’re joining to for each record on the primary table), and each of the rows in the joined table needs to be a column on the excel spreadsheet (instead of a row). So, for each account we still only have 1 row, but we may have 10 extra columns if there are 10 rows in the joined table. I can force it to always be 10 columns (in case there are only 3 rows for example on the joined table) if that is easier.

How would I do this? Make a row a column without it getting super complicated? In the table we are joining to, they would like the account # that is found as one field in the data to be the column heading and the data in the field will be the amount for that account #. Both of these fields are in the table we are joining to.

Any suggestions?

Have you considered the McGyver technique and a series of defines? Would it suit your needs. Can the columns be collapsed or is the data unique in each.

What about ACROSS?

I've heard of the Mcgyver technique, but I've never seen it or used it. What do you mean by "can the columns be collapsed"? The data in the columns can be unique or is the data unique in each ... are you meaning the table I'm joining to? It is possible that the data may NOT be unique in the table I'm joining to (I'm still trying to get confirmation on this).

Across was what I thought I would use but I get an error (FOC031) THE MAXIMUM NUMBER OF 'ACROSS' VALUES HAS BEEN EXCEEDED. I've never used across before, so I've been reading about it to see what I'm doing wrong.

how about using
Copy and Transpose in EXCEL....

Pam,
How many fields are there in the second file and what are their data types?

this is the master for the 2nd file. I join to it using lumpsumid. The combo of lumpsumid and srceacctnbr are unique. I'm grabbing srceacctnbr and srceamt for my excel spreadsheet from this table.

FILE=NBTBLSRC, SUFFIX=DB2 ,$ SEGNAME=NBTBLSRC, SEGTYPE=S0 ,$ FIELD=LUMPSUMID ,LUMPSUMID,
A20 ,A20 ,MISSING=OFF,$
FIELD=SRCEACCTNBR ,SRCEACCTNBR,
A12 ,A12 ,MISSING=OFF,$
FIELD=PLANNBR ,PLANNBR, A12 ,A12 ,MISSING=OFF,$
FIELD=FEDTAXID ,FEDTAXID,
A9 ,A9 ,MISSING=OFF,$
FIELD=PYMTTYPE ,PYMTTYPE,
A2 ,A2 ,MISSING=OFF,$
FIELD=SRCEAMT ,SRCEAMT, P14.2 ,P7 ,MISSING=OFF,$
FIELD=PSTGDTE ,PSTGDTE, P8 ,P4

Here's a CAR-based example that may give you an idea or two.

TABLE FILE CAR
SUM COUNTRY
BY COUNTRY NOPRINT
ON TABLE HOLD AS H001
END

TABLE FILE CAR
SUM
WHEELBASE
COMPUTE WBNO/I4 = IF COUNTRY EQ LAST COUNTRY THEN WBNO + 1 ELSE 1;
BY COUNTRY
BY WHEELBASE NOPRINT
ON TABLE HOLD AS H002
END

JOIN COUNTRY IN H001 TO ALL COUNTRY IN H002 AS J1

TABLE FILE H001
SUM
WHEELBASE
BY COUNTRY
ACROSS WBNO
END

thanks Francis. That made sense.

Pam,
So you want for each record in the first file all the SRCEAMT fields from the second file on the same row.
You can do the following:
TABLE FILE NBTBLSRC
LIST SRCEAMT
BY LUMPSUMID
ON TABLE HOLD AS PAM FORMAT FOCUS INDEX LUMPSUMID
END
JOIN LUMPSUMID IN firstfile TO ALL LUMPSUMID IN PAM AS A_
TABLE FILE firstfile
SUM fieldsfromfirstfile
BY LUMPSUMID
SUM SRCEAMT
ACROSS LIST AS ''
BY LUMPSUMID
END

Pam,
Now if you want to have more than 1 field from NBTBLSRC, and have each field on a different line, I have a solution for that too...