Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old August 8th, 2007, 09:39 AM
Authorized User
 
Join Date: Jul 2007
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default Grouping with calculated field in report

Hi,
I got a access table which is like the following

Group Description Amount
3000 Net Sales 100000
5000 Total Var Cost 75000
7000 Total Other Cost 20000

Now I need to make a Profit and loss statment out of the above table

The statement should look something like this

3000 Net Sales 100000
5000 Total Variable Cost 75000

6000 Gross Margin 25000

7000 Total Other Cost 20000

9000 Net Profit 5000

The 6000 and the 9000 group are not coming out of the table but are lables and the values of the amount are calculated field.

I have no idea how to get the calculated field in the report format. So far I have put all the three fields in the detail section of the report.

Any help is appreciated. Thanks.
 
Old August 8th, 2007, 10:22 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If there is only one record in your detail section, then just drop some text boxes on the detail section and add this text:


6000 Gross Margin: =Sum([3000]-[5000)

9000 Net Profit: =Sum(([3000]-[5000])-[7000])

I am assuming the field names.

Did that help?


mmcdonal
 
Old August 8th, 2007, 04:56 PM
Authorized User
 
Join Date: Jul 2007
Posts: 14
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Actually I am looking to have this done without hardcoding as once I am done with this report format, I need to pass paramter to manipulate few other fields that i do not have here. Any more thoughts. Thanks mmcdonal.
 
Old August 9th, 2007, 06:46 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Yes. Do this with a query then. Put all of your results either in a query, or in a temporary table, and base the report on the source you use.

For example, you could create a query that is:

SELECT tblMyTable.3000, tblMyTable.5000, tblMyTable.7000, Sum([3000]-[5000) AS 6000, Sum(([3000]-[5000])-[7000]) AS 9000 FROM tblMyTable

Then make this an append query and post it to a table where you are accumulating other data for you report. It would be flat with one record to source the report, but it is only a temp.

Anyway, there are lots of ways around this.



mmcdonal





Similar Threads
Thread Thread Starter Forum Replies Last Post
How to create a Calculated Field fdtoo SQL Server 2000 0 April 11th, 2006 08:34 PM
Automatically select value from a calculated field jimmy0305 Access 0 June 21st, 2005 10:43 AM
Auto Calculated Field in Access anubhav.kumar Access 1 March 15th, 2005 08:37 AM
how to added a calculated field in pivot table ndenhere BOOK: Access 2003 VBA Programmer's Reference 0 May 27th, 2004 02:45 AM
Validate calculated field in sub form MG76 Access 4 March 18th, 2004 04:26 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.