Multiple Fields in one Report Column

[Laura]

I am trying to run a report showing payments received based on payment date.
The underlying table has three separate sets of payment fields (ie. payment
date, check number & payment amount). I am trying to run a report where each
payment is shown as a single row with all payment amounts in the same column.


For example, if someone sent three checks for the same record, there would
be amounts entered in Payment1, Payment2 and Payment3. I want to see these
payments on the report as three separate rows but the amounts all under the
same column heading. Any suggestions?

 

[phildenuh]

I am trying to run a report showing payments received based on payment date.
The underlying table has three separate sets of payment fields (ie. payment
date, check number & payment amount). I am trying to run a report where each
payment is shown as a single row with all payment amounts in the same column.

For example, if someone sent three checks for the same record, there would
be amounts entered in Payment1, Payment2 and Payment3. I want to see these
payments on the report as three separate rows but the amounts all under the
same column heading. Any suggestions?

Go under grouping and set the sort and gorup property to use the
payments as a sort crieteria. So all you do is go into sort and group
and then select payment as the Field/Expression and then sort anyway
you want it. Under group Head , make sure it shows yes.

 

[Laura]

Thanks for your response but I don't have just one Payment field. There are
three (Payment1, Payment2 or Payment3) and I need to consider all three
fields as having a potential entry for the report. How would I do this?

 

[Duane Hookom]

Your payments should each be stored in their own record. You should change
your table structure or use a UNION query to normalize your payments to
display them "where each payment is shown as a single row"

SELECT ID, [Pmt1] As Pmt, 1 as PmtNum, [Date1] as PmtDate, Chk1 as CheckNum
FROM tblNotNormalized
WHERE Pmt1 is not null
UNION ALL
SELECT ID, [Pmt2], 2, [Date2], Chk2
FROM tblNotNormalized
WHERE Pmt2 is not null
UNION ALL
SELECT ID, [Pmt3], 3, [Date3], Chk3
FROM tblNotNormalized
WHERE Pmt3 is not null;

 

[Laura]

I used the UNION query and it worked great. Thank you so much for your help.

Your payments should each be stored in their own record. You should change
your table structure or use a UNION query to normalize your payments to
display them "where each payment is shown as a single row"

SELECT ID, [Pmt1] As Pmt, 1 as PmtNum, [Date1] as PmtDate, Chk1 as CheckNum
FROM tblNotNormalized
WHERE Pmt1 is not null
UNION ALL
SELECT ID, [Pmt2], 2, [Date2], Chk2
FROM tblNotNormalized
WHERE Pmt2 is not null
UNION ALL
SELECT ID, [Pmt3], 3, [Date3], Chk3
FROM tblNotNormalized
WHERE Pmt3 is not null;


--
Duane Hookom
Microsoft Access MVP

I am trying to run a report showing payments received based on payment date.
The underlying table has three separate sets of payment fields (ie. payment
date, check number & payment amount). I am trying to run a report where each
payment is shown as a single row with all payment amounts in the same column.


For example, if someone sent three checks for the same record, there would
be amounts entered in Payment1, Payment2 and Payment3. I want to see these
payments on the report as three separate rows but the amounts all under the
same column heading. Any suggestions?