Need help with writing a query

[Leah]

I am stuck with writing this query and need some
help....It is pulling together a master list of all
customers, orders, pmts and outstanding balances for my
accounting dept. Problem is with orders that have
multiple line items. It doesn't apply the payments
correctly and when I pull the query, it shows a credit on
one and a balance on the other. So, I have figured out
that I need to write an expression that will sum the
invoiced amount (quantity*price)+(freight), then sum the
payments, then subtract the sum of the payments from the
sum of the invoiced amount.

Does this make sense and can someone tell me how to write
this expression? When I try I get some error about not
having the customer ID in the aggregate function.

Thanks!
Leah

 

[Tom Ellison]

Dear Leah:

From my experience with accounting, the first thing you must do is
determine the accounting method used.

At first, you say, "It does not apply the payments correctly . . ."

Are the payments to be applied to the individual lines of orders (I
assume you mean invoices, as orders are usually not billable till
filled, but perhaps it is the case that payment is made with an
order). This is the case with "open item" accounting of receivables.
It may be the case that Payments are associated with Billing in a
chronological order. However, I have also had to create receivables
accounting where the users had to apply each payment to specific lines
of specific orders. This is all based on the expectation of your
accounting system (and the person/people in charge of it).

Later you say, "sum the invoiced amount . . ., then sum the payments,
then subtract). This is typical of a balance forward accounting
system. You would not them be able to create a running sum except in
chronological order. There can be no question of "applying a payment
to a line of an order." There is just a running balance and a current
total. In this case, you would typically not be interested in the
lines of an order, or the lines of an invoice, but just with the grand
total of the invoice.

I strongly recommend that you get a good understanding of the
accounting practices you are going to match before going farther.
Also, try to get a close idea how the reporting is going to look.

Next, a review of the data you are storing is in line, to make sure it
will support what you have learned you will need.

I'd be glad to help with your finishing touches, but I'm a bit afraid
I'd confuse you with details that don't match what you need done.
Your original post seems slightly contradictory on this point.

I hope this helps in some way!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Leah]

Tom,

Thanks for trying to help - I'm not sure how to phrase
what I need done (hence the confusing post!)

Basically my accountant needs a report that shows all the
customer contact info, what they ordered, how much they
paid and if they have a balance owing.

The way I process the order is that I put in an order for
that person, and if they order a set of books, that is one
line item, and then they order an extra individual book
that is the second line item. The order specifically I'm
having trouble with is one line item for 164.95 which is
the set, and then he needed a single book for 109.95. The
total payment was 274.90. I put in payment #1 as 164.95
since that was for the set, and payment #2 as 109.95.
When I run the query, it shows both line items correctly,
but show's both payments as 164.95. It's like the second
payment is not being picked up by the query...that's where
I got the idea to sum and then subtract the sums...does
that make more sense or did I just babble again?

 

[Tom Ellison]

Dear Leah:

From what you're telling me, you are trying to perform an open item
accounting system here. You didn't just put in a payment of 274.90,
but you split it up into two payments, dividing it between the two
lines of order.

In order for this to work, there has to be an association between each
line of order and the payment(s) that are attached to it. Do you have
a unique key to each line of each order? Have you included that key
in the individual payment records? If so, you can join them in this
way, with a LEFT JOIN to the payments from the orders.

Be sure you can handle all combinations of things happening. This
includes:

- payments made that cannot be associated with a line of an order.
- payments that cover shipping.
- a line of an order that may have multiple payments associated with
it.

Your problem seems to me that it may be one of table design first,
then queries to follow. But I don't really have enough information to
say for sure.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts