query problem?

[John]

I have a small one man business and use Access 2000 for my stock control and
bookeeping. I have a payments table which takes all payments in and out of
the business. There are two linked tables that describe the payment.
tblProductCategory, (what the payment is for) and tblTransactionType (how it
is paid, cheque, card, cash etc.)

tblPayments
pkPaymentID
PaymentNumber
PaymentDate
PaymentAmount
fkProductCategory
fkTransactionType


tblProductCategory
pkCategoryID
ProductCategory

tblTransactionType
pkTransactionID
TransactionType

My problem is that I want to design a report that will show some of the
payments in bank statement format. For example, all payments in and out of a
particular account. This would use two colums, with transaction type to
include all payments received (chequeIn, cashIn, cardIn) in one column and
all payments out in another column. I thought it would be easy to create a
query to do this, but not so. When I try this, either amounts are being
duplicated or ignored.

Any help on this appreciated

John

 

[Guest]

You will need two columns for your query to do this. Both will use the
PaymentAmount field, but different definitions. Since I don't know all your
Transaction types, I will make some up for example purposes. You will need
to modifiy these to suit your transaction types.
PaidIn: IIf([tblPayments]![TransactionType] IN ("chequeIn", "cashIn,
"cardIn"), [tblPayments]![PaymentAmount],0)
PaidOut: IIf([tblPayments]![TransactionType] IN ("chequeOut", "cashOut,
"cardOut"), [tblPayments]![PaymentAmount],0)