Average Need on Payment Methods

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe Cilinceon - 16 Nov 2005 16:17 GMT

What I'm looking for is an average (Monthly) for payment methods. For
example what percentage of payments for this month are in Cash, Check,
Credit Card, Money Order etc.

The Tables this is on are:

LEDGER
Transaction (to Payments.Transaction)
PaymentDate

PAYMENTS
Transaction
PayTypeID (1=Cash, 2=Check, 3=MoneyOrder, 4=Visa, 5=MasterCard)

tlkpPayMethods
PayTypeID (to Payments.PayTypeID)
PaymentGroup (1=cash, 2=check,3=MoneyOrder, 4=Credit Cards (both MC &
Visa)

Signature

Thanks

Joe Cilinceon

Reply to this Message

[MVP] S.Clark - 16 Nov 2005 20:12 GMT

Average = Part / Whole

Part Query:
Select PaytypeID, Count(Transaction) AS Part from Payments Inner join Ledger
on Payments.Transaction = Ledger.Transaction GROUP BY PayTypeID WHERE
PaymentDate Between [ThisDate] and [ThatDate]

Whole Query
Select Count(Transaction) as Whole from Payments Inner join Ledger on
Payments.Transaction = Ledger.Transaction WHERE PaymentDate Between
[ThisDate] and [ThatDate]

Average Query: Link Part and Whole queries together, then divide part by
whole in a new field.

Signature

Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

> What I'm looking for is an average (Monthly) for payment methods. For
> example what percentage of payments for this month are in Cash, Check,
[quoted text clipped - 14 lines]
> PaymentGroup (1=cash, 2=check,3=MoneyOrder, 4=Credit Cards (both MC &
> Visa)

Reply to this Message

Joe Cilinceon - 16 Nov 2005 20:31 GMT

Thanks for responding Steve. If I'm correctly reading what you posted, I
need to make 3 different queries, with the 3 combining the first 2.

Signature

Thanks

Joe Cilinceon

> Average = Part / Whole
>
[quoted text clipped - 34 lines]
>>
>> Joe Cilinceon

Reply to this Message

[MVP] S.Clark - 18 Nov 2005 14:58 GMT

No, two should do it. Link Part to Whole.

> Thanks for responding Steve. If I'm correctly reading what you posted, I
> need to make 3 different queries, with the 3 combining the first 2.
[quoted text clipped - 37 lines]
>>>
>>> Joe Cilinceon

Reply to this Message

Joe Cilinceon - 18 Nov 2005 23:47 GMT

Thanks Steve but what I did was have one use the LEDGER.Transaction >
Payments.Transaction , Payments.PayAmount. I then did another with
LEDGER.Transaction > Charges.Transaction, Charges.PayAmt. In both I
grouped on the Transaction # and sum on the Pay Amounts. In a 3rd query I
linked them together. It seemed to work fine and gives me away to double
that every thing balances.

> No, two should do it. Link Part to Whole.
>
[quoted text clipped - 43 lines]
>>>>
>>>> Joe Cilinceon

Signature

Joe Cilinceon

Reply to this Message

[MVP] S.Clark - 21 Nov 2005 23:52 GMT

Great. The only message that I was attempting to convey was... do it with
multiple queries.

> Thanks Steve but what I did was have one use the LEDGER.Transaction >
> Payments.Transaction , Payments.PayAmount. I then did another with
[quoted text clipped - 50 lines]
>>>>>
>>>>> Joe Cilinceon

Reply to this Message