I have the following query:
SELECT DISTINCT D."Date", D.Amount, D.FamNo,D1.LastName, D1.FirstName
FROM ":FMS:Receipts.DB" D, ":FMS:Family.DB" D1
WHERE
(D.PayMethod = 'Cheque')
AND (D.Banked = 'TRUE')
AND (D1.FamNo = D.FamNo)
ORDER BY D."Date", D1.LastName, D1.FirstName, D.Amount

The Receipts Table has one 'Amount' field for both cheque and cash amounts
and I need to extract those amounts respectively for each customer family in
order to list them in a report under their respective 'Cheques' and 'Cash'
columns on the report.

Is there a way to do this?!

Can you help?

Thank you for your prompt reply as I need to complete this project to by
25/11/98 :-)

Shane

You don't say what type of database you are querying, so...
ASSUMING you are using MS-SQL server 6.5...
I also don't know how you determine if it's cash or cheque, so I'm
making up a field (type) here.

select distinct d."Date",
        case when d.type = "cash" then d.amount else 0.00 end as cash_amount,
        case when d.type <> "cash" then d.amount else 0.00 end as
cheque_amount,
... rest of your sql.

wayne

I think this is a SQL Question, not a Delphi one, but you can try this:

SELECT F.LastName, F.FirstName, R.Date, R.FamNo, R.PayMethod, R.Amount, 0
  FROM Receipts R, Family F
  WHERE R.PayMedthod = 'Cheque'
  AND R.Banked = 'TRUE'    --> Are you using a boolean or a char?
  AND F.FamNo = R.FamNo
  ORDER BY R.Date, F.LastName, F.FirstName, R.Amount
UNION
SELECT F.LastName, F.FirstName, R.Date, R.FamNo, R.PayMethod, 0, R.Amount
  FROM Receipts R, Family F
  WHERE R.PayMedthod = 'Cash'
  AND R.Banked = 'TRUE'    --> Are you using a boolean or a char?
  AND F.FamNo = R.FamNo
  ORDER BY R.Date, F.LastName, F.FirstName, R.Amount
--
Rafael Vargas
DBA,
Bratex Dominicana C. por A.
r...@oscarbbs.com

Shane Bekker <bekk...@netactive.co.za> wrote in article
<73ijja$c...@forums.borland.com>...