How to solve footer summation problem?

[wizardchef]

I have been away from Access programming for several months. I have a query run (in an ap I designed) on a typical customer database where on a given date, a customer may make more than one purchase. I also record a single payment amount for each order.

When I create the report, with the report bound that that query, and try to sum the payments in the footer, I get an incorrect sum for the total payments over a period (multiple purchases on a single date result in two instances of the payment in the query). The reason is because the query produces a payment entry for each transaction on a given date. So, if a customer has more than one purchase on a given date, the payment amount appears multiple times and appears in the summation.

What is the easiest solution to this problem (I have tried using two separate queries ...one for details and one for payment ... and a subreport in the main report, but can't get that to work)?

 

[dhookom]

Are you suggesting you have separate tables for payments and purchases? Are you sure you can never have more than one payment a day?

I would probably create a union query of payments and purchases so the report will calculate correctly.

If you can't figure this out, come back with some table structures, a few sample records, and desire output in the report.


Duane
Hook'D on Access
MS Access MVP

 

[wizardchef]

There is a Payments table with OrderID, PaymentAmount,PaymentDate, DepositDate,CheckNumber and a few other things. There is an Orders table with customerID, OrderDate, shipping details, FreightCharge, Tax, and other things. There is an Order Details table with OrderID, ProductID, Quantity, UnitPrice, and Memo. There is a Products table with things that describe the product. And there are other tables. Very relational db.

I have a query that uses the following tables: Customers, Payments, Orders, Order Details, and Products where I link things like ProductID, OrderID, CustomerID. I have a report that produces, for a given deposit date, the following transactions:

Customer last name
Customer first name product descript.(1) $cost
product descript. (2)$cost
Payment Total cost $xxx

I get one of these for each customer transaction that occurred for that deposit date. (e.g., I might have 20 customer entries like above).

All the above works fine. Then, in the footer I tried to do a sum on the Payment field. That is when I got the wrong sum. When I looked at the query, because it produced a row for each product (description, cost, quantity, etc.) purchased, it included in that row the payment amount. So, if there were 2 items purchased by customer A (like the above example), there were 2 payments shown, when in fact there was only one physical payment. When summed in the footer, I would get 2X the amount in the sum for that customer's transaction.

Hope this makes sense.

I then tried to do a query on the payments only where I did a unique select on the payments to eliminate the redundancy, and then tried to insert this as a subreport. I couldn't make that work. I also thought of writing some VB code to sum the purchases and ignore dupes, but I am not that swift at writing VB code.

Must be an easy way to do this that I am overlooking.

 

[dhookom]

I would put either the Orders or payments in a subreport or build a union query from the orders and payments.

Duane
Hook'D on Access
MS Access MVP