Average Need on Payment Methods

[Joe Cilinceon]

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)

 

[[MVP] S.Clark]

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.

 

[Joe Cilinceon]

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.

--
Thanks

Joe Cilinceon

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.

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)

 

[[MVP] S.Clark]

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.

--
Thanks

Joe Cilinceon

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.

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)

 

[Joe Cilinceon]

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.

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. --
Thanks

Joe Cilinceon

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.


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)

 

[[MVP] S.Clark]

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
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.

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. --
Thanks

Joe Cilinceon


[MVP] S.Clark wrote:
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.


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)