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