Link to home
Start Free TrialLog in
Avatar of jeff09
jeff09Flag for United Kingdom of Great Britain and Northern Ireland

asked on

Access 2007 report query calculations for individual sets of results

I am trying to build a query for a report that will perform the same calculation on each set of results drawn from a table and then display these values on a seperate page for each result.

Overview:
The idea being is that a main contractor will have a number of sub-contractors and in turn these subcontractors will have entries made int a payroll table on a weekly basis.

I have a "Payroll" form where the gross amount earned by a sub-contractor are entered on a weekly basis and then the tax and other deductions are calculated into a datasheet thereafter.

What I am trying to achieve now is to run a query for the month end which will pull all entries for that month for each sub-contractor, add the values together (for each sub-contractor and display them on a seperate page in a report for each result.

For information the contractor table is linked to a subcontractor table which in turn is linked to the payroll table.

I can upload a sample of the database if required
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<What I am trying to achieve now is to run a query for the month end which will pull all entries for that month for each sub-contractor, add the values together (for each sub-contractor and display them on a seperate page in a report for each result.>>

  Build a query with all that detail.  Don't worry about the calculation as yet.

  Now in the report, you want to use the sorting and grouping option to create a group on the main contractor/sub combination or simply on the sub contractor.

  When you do that, you'll have the opportunity to have a header and/or footer each time the group value changes (the sub contractor or what ever you based the group on).

  In the group header, you can place the Name, ID, etc.   Let the detail section have controls for the amounts.  Then in the footer, you can place controls and set them to =SUM([<some field>]) and your get the total for just that group.

  So the report engine will do your calculations for you.

  Each group header/footer also has various properties, like forcing a page break before or after the section prints.  In this case I'd do a page break after the footer.

  There is also a report header/footer, where you can total over the entire report.

  Give it a whirl, it's easier then you might think.

JimD.
Avatar of jeff09

ASKER

I have tried the =SUM([field]) and this gives the total of all the subcontractors put together. What I am trying to achieve is a total for each subcontractor for that month and each subcontractor to appear only once.

For instance, the test figures I am running are on 3 subcontractors with 4 weekly entries. However, the results I am getting is not 3 pages (1 for each subcontractor) with a sum of 4 weeks wages. It is 12 pages (4 for each subcontractor) and the SUM is the sum of all 12 entries.

I am confused

<<I have tried the =SUM([field]) and this gives the total of all the subcontractors put together. What I am trying to achieve is a total for each subcontractor for that month and each subcontractor to appear only once.>>

  You need to make sure that your putting that in the Group footer.  If you want it broken down by month as well, that will also need a grouping.

  Is the DB something you can post?

JimD.
Avatar of jeff09

ASKER

Yeah certainly, I have attached a copy for you.

If you open the form "frmContractorDetails", then select the "Reports" tab, I have marked with a label the report I am stuck on (this opens the report "rptSubCMonthStatement" by way of the query "qrySubCMonthStatement".

Thanks for looking

Jeff Sample-Copy.accdb
Can you do a compact & repair to reduce the size, create an mdb version, and upload it here?
Avatar of jeff09

ASKER

there you go
Sample-Copy.accdb
Avatar of jeff09

ASKER

although this website changes the file extension to .accdb for some reason
After clicking Tools, Databse Utilities, Convert Database?
Avatar of jeff09

ASKER

Hi GRayL,

I cannot convert the database to an ".mdb" file type. I get an error message stating.

"You cannot save this database in an earlier version format because it uses features that require the current file format"

Unfortunately therefore you will need access 2007 or greater to view this database.
Unfortunately, Jim Dettman at http:#a34422235 pretty well said it all.
I should not have said 'unfortunately' - more a case of I can't help you any more.
Jeff,

  Just getting back to things and I'll have an answer for you shortly on this.

JimD.
Avatar of jeff09

ASKER

Thanks Jim,

Xmas holidays i know, so don't worry about any delays. They're totally expected

Jeff
Jeff,

  You didn't add any groups to the report.

  Below is a series of screen shots to show what I am talking about.  First you need to click the group and sort button in report design:


Region-Capture1.jpg

 Next is the resulting wizard at the bottom: User generated image
  This allows you to group and sort on fields in the report and optionally, set options (which can also be set with the property page.  Note the settings I've used.  As a result, the report now contains a Group Header:

 User generated image
  Which you can use to place things such as the Contractor name, address, tax ID, etc.  

  Next show the group footer and because of the way I answered the options in the Group and Sort wizard, note that it has already included a total for [Fee].  This will be the total just for one contractor.

 User generated image
  Last shot shows the property page where you can set options for each of the sections.  This is the property page for the footer.  Note that I've told it that I want a new page after it prints the section.  This means each contractor will be on it's own page.

 User generated image
  Look through that and see if you can work through it on your own.  Holler if you get stuck again.

Jim.
Avatar of jeff09

ASKER

Thanks for that.

However, I may still be doing something wrong here I think.  After following your step by step solution, when I run the report for a date range from 6/12/2010 - 5/01/2011 the result I would hope to have been displayed in the whole report should have been just 2 pages long showing the totals for each subcontractor on a single page. What is happening is I am getting 3 pages of results for the first subcontractor and 2 pages of results for the second subcontractor and the totals are only appearing on the last page for each subcontractor.

Am I missing something here?
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jeff09

ASKER

Thanks Jim,

I only wanted the totals for each sub contractor on a page for each sum. So i followed your instructions in your final para and this worked a treat.

Thanks a lot