Appending data based on CustID into designated columns - 1-to-M ex

[Amy]

I have one table with CustID's and contact information, I have another table
with CustID's and answer codes that represent demograpical information. It
is a one to many relationship. I need to output a file that is fixed length
and appends the answercodes to specific fields based on the prefix of the
answer code.

For example, the expected fields on the output file are called SQ001,
SQ002, SQ003, etc.
The contact table looks like a contact table

ID FName LName
1 Any Body

The answer table looks like

ID answer_code
1 001A025
1 002A013
1 004A001

I need to append these to the designated columns and insert "-"s instead of
nulls.

I need it to look like

ID Fname LName SQ001 SQ002 SQ003 SQ004
1 Any Body 001A025 002A013 -
004A001

I followed a similiar thread's answer below which works great but that
solution isn't exactly what I need, it doesn't sort before it concantenates
and it doesn't insert for nulls.

3/22/2007 5:42 PM PST -Appending data based on CustomerID in Access Database
New users

I need to do this monthly. Last month I used Excel and lookup tables but I
don't think that is the best approach...

I've tried to figure it out or find a similiar solution in multiple places
for about 2 months and I can't, I need help. Thank you in advance

 

[Andy Hull]

Hi Amy

Try the following query...

SELECT tblContact.ID, tblContact.FName, tblContact.LName,
nz(max(iif(left(tblAnswer.answer_code,3)="001",tblAnswer.answer_code,null)),"-") AS SQ001,
nz(max(iif(left(tblAnswer.answer_code,3)="002",tblAnswer.answer_code,null)),"-") AS SQ002,
nz(max(iif(left(tblAnswer.answer_code,3)="003",tblAnswer.answer_code,null)),"-") AS SQ003,
nz(max(iif(left(tblAnswer.answer_code,3)="004",tblAnswer.answer_code,null)),"-") AS SQ004
FROM tblAnswer INNER JOIN tblContact ON tblAnswer.ID = tblContact.ID
GROUP BY tblContact.ID, tblContact.FName, tblContact.LName;


....substituting your own table and columnn names.
Then export using DoCmd.TransferText or DoCmd.OutputTo

Regards

Andy Hull