PDA

View Full Version : MS Access report problem



Brucem
26-01-2013, 04:07 PM
I am using MS Access, and Win XP. I have developed a database to do a club's accounts. I have a table "Purchases" which lists purchase #, when, who, what, how much, and payment #. There is another table "Payments", which lists payment #, payment date, who, cheque #, approval date, and cheque clearance date. Obviously there can be more that one puchase for each payment. I produced a query with pretty much all af the data from the two tables. I have made a report which for each payment lists the purchases. This looks good except that the grand total ot the purchases does not equal that of the payments. In thsi case the purchases total is $8531.22, which is correct, but the payment total is $11889.13! I have check added both columns and they should both be $8531.22.

can anyone give me any suggestions on how to fix this?

Paul.Cov
26-01-2013, 07:42 PM
When you add tables to a query there needs to be a join between the tables. The joins can specify whether the relataionship is One To One, One To Many, or Many To Many (no join). Many to Many data sets can be tricky beasts

With Many To Many queries you can get massive numbers, as Access attemtps to create a complete copy of the contents of Table1 with every record of Table2.

Example, if Table1 has 50 records, and Table2 has 11 records, a query lacking relationship joins will have 50 x 11 = 605 records, with many duplicates of the data being summed.

If you make two seperate queries with only one table in each do you then get the correct sums?

Is there any reference in Table Purchases to a Primary Key from the table Payments, or vice versa? If not you will have to avoid placing both tables into a single query and work with a query for each table.

Brucem
27-01-2013, 11:25 AM
Thanks Paul.Cov, I have a 1:1 relationship between the payment reference fields in the two tables, and everything in the report is correct, except the grand totals. I have edited a report, saved it as PDF but can't add it to this so that wont help. I tried to export via Excel, but that gave the correct totals!

Paul.Cov
27-01-2013, 03:31 PM
Do you have purchases that haven't yet had payments made against them? eg an unbanked cheque situation?

One strategy I use is to do a grouping query on each cheque number (or purchase reference), comparing the sums for each group of payments against the sums for each purchase.

Another option is an 'Is Not Matched' query, to track down a record from one table that doesn't have a matching entry in another table.

Or try a variation on the queries to exclude data where the [ClearanceDate] is Null or IsEmpty.

Brucem
30-01-2013, 09:54 AM
While I include cheque numbers and clearance dates in the report, they are not used as criteria.

I used a copy of my query and amended it to select one payment only. I have retyped the resulting report into a .doc file as I could not get a migrated file into my reply. I have attached this file, and as you will see it is quite old, and the cheque amount $166.69 agrees with the total of the purchases, but the sum of the Cheque amounts 1s $666.76.

Sorry but the .doc file comes up an an invalid file as have previos attempts.

Paul.Cov
30-01-2013, 02:18 PM
666.76/166.96 = 4

There's a big clue - you've got 4 copies (or references to) of that particular item being included in your query.

I'm guessing they are 4 different purchases being paid with this cheque.

So you've got 4 purchases totalling $166.96, and (I'm guessing) 4 references to a single cheque of $166.96, and your query is adding up the purchases, and getting $166.96, and adding up the 4 matching values of the entire cheque (payments), giving 4 x $166.96

One solution, if it is a Summing / Grouping query is to get it to do Max / Min / First / Last / Average on the column for the cheque value, rather than Sum.

The alternative is to do a query on your Purchases table, where you group the data down on cheque number, sum the amount, then use that query to link to your payments table, linking on the cheque number, then your totals should be OK, as each table only presents on entry per cheque.

An alternative to the attachment dilemma is to take a screenshot of your query / report, and turn the screenshot into an image, which you can attach.