Help!! Combine two identical columns in a table.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I have two combo boxes in a table. Method of Payments and Method of
Payments #2. These combo boxes have the following options "Check", "Wire",
"Adjustment", and "CC."

Example: 1. Method of Payment = Check and Method of Payment #2 = Adjustment.
2. Method of Payment = Adjustment.

Is it possible on a form or report to pull identical payments in the same
column? So if I had a column called Adjustments in the form or report, that
the Method of Payment Amount and Method of Payment #2 Amount would pull under
one column?

I hope that makes sense.

Thanks
AccessFitz
 
Hi,
i think you can first make a union query in order to put 2 columns in one,
and then use a cross-tab query with fixed columns
 
Hi,
so if you have a table:
Payment PID Person Amount Payment1 Payment2
1 Alex 1 Check

2 John 2 Adjustment Check
3 Tim 3 Cash Check
4 Mark 4 Adjustment Cash


then you make a query qPayment:
SELECT Payment.Person, Payment.Amount, Payment.Payment1 AS Payment
FROM Payment
UNION ALL SELECT Payment.Person, Payment.Amount, Payment.Payment2 AS Payment
FROM Payment;

and then one more:
TRANSFORM Sum(qPayment.Amount) AS SumOfAmount
SELECT qPayment.Person
FROM qPayment
GROUP BY qPayment.Person
PIVOT qPayment.Payment;

result will be:
Query2 Person <> Adjustment Cash Check
Alex 1

1
John
2
2
Mark
4 4

Tim

3 3


at least this is how i understand you
 
Back
Top