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.

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

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.

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