Microsoft Access - Scrambling Totals

0
votes

I'm currently in the process of coding a summary report in Access XP for a client and while for the most part I've gotten 99% of the fields to match correctly with the original report (created in Corel Paradox 4.0), I'm having an issue with a sum field where despite using the same set of data, I'm getting invalid answers.

For example, in one table, the sum of the fee field comes to 9,050 in the Paradox report. In Access, the table containing the data also comes to that same amount. Despite this, when I enter the expression in my Summary report, Access will always display the total as 9,005.

I've been trying to use the NZ function to cancel any nulls, however that hasn't helped. Changing the number format to currency, general, and other formats also hasn't been helping.

Any assistance/insights are greatly appreciated.

EDIT: As requested below are some of the expressions from my Access report. Everything up till now has strictly been Access code, but as I have SQL experience, I am open to implementing that type of code if needed (it actually has been on my mind for a bit)

=nz(Sum([Fee]))

Calculates just the total fees

=nz(Sum([Room & Meals]))+(nz(Sum([Commutter & Meals])))+(nz(Sum([Fee])))

This we use to calculate the grand total. It also comes out identical to the fee total for my dummy data since the first two fields don't have any totals added to them.

1
ms-access
Posting your queries would help. - Jacob
Ok, so you are not writing the queries yourself. Are you familiar with SQL? What do you mean with . In Access, the table containing the data also comes to that same amount. Did you count it per hand? - Jacob
At the moment I've been putting the expressions together based on Access functions since when I was working with Corel Paradox it was easier to just go from PAL to Access for most of the reports since they didn't involve complex totals. About the remark, I was meaning that when viewing the tables, if you total the fees of all records, it comes out to 9,050 but when I view the summary report the fee shows as 9,005 so I'm thinking this is a report format issue rather than a coding issue. I am fairly familiar with SQL so I could try writing my queries that way if needed. - theonlylos

1 Answers

1
votes

Not sure this will help, but you need to break this down to trouble-shoot.

  1. Capture the detail data from this report and compare to the Corel Paradox data. Don't just test the totals. Maybe you can put them in Excel.
  2. Break out the three fields individually and sum those: Sum([Room & Meals, Sum([Commutter & Meals, Sum([Fee]). Don't format. Manually add them together to test.
  3. Are these integers or is there some rounding going on?
  4. Are the text boxes on the report large enough to display the entire number?

1 may show you're comparing apples to oranges.