Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 8th, 2007, 09:39 AM
Authorized User
 
Join Date: Jul 2007
Location: , , .
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.
Reply With Quote
  #2 (permalink)  
Old August 8th, 2007, 10:22 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
  #3 (permalink)  
Old August 8th, 2007, 04:56 PM
Authorized User
 
Join Date: Jul 2007
Location: , , .
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.
Reply With Quote
  #4 (permalink)  
Old August 9th, 2007, 06:46 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 05:50 PM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.