Access as financial reporting tool

[Guest]

We would like to set up Access as our reporting tool in the absence of good
reporting from our financial system.

In the P&L report we need to see month actual, month budget, month budget
variance and the same for YTD (AC, BU and BU var.) We have different
departments: new units, used units, workshop, service, hire, parts and admin.
We also have different branches: Sydney, Melbourne etc...

We also need to look at sub totals like gross margin, contribution, etc,
i.e. normal subtotals in a P&L.

At the moment we can generate a trial balance file with all the information
above, and we've created tables for department, branch, account, and also for
the separate month values linked to the above.

a) how do I build my P&L? I've started off with a query but got stuck since
I need subtotals...

b) what is the best set up in this scenario, maybe we've started off on the
wrong foot?

Thankful for replies,

 

[Pieter Wijnen]

You can't have subtotals in a query (well, you shouldn't), you can however
have subtotals in a report.
Assuming you want a subtotal for a field Called "Amount"
Add a Hidden Control to the forms Detail section & call it RAmount
(ControlSource: Amount) & set it's Running Sum Property to 'Over Group'
In The Group Footer For Department Add A Control DeptAmount (ControlSource:
RAmount)

HTH

Pieter

 

[Pieter Wijnen]

You can't have subtotals in a query (well, you shouldn't), you can however
have subtotals in a report.
Assuming you want a subtotal for a field Called "Amount"
Add a Hidden Control to the forms Detail section & call it RAmount
(ControlSource: Amount) & set it's Running Sum Property to 'Over Group'
In The Group Footer For Department Add A Control DeptAmount (ControlSource:
RAmount)

HTH

Pieter

We would like to set up Access as our reporting tool in the absence of
good
reporting from our financial system.

In the P&L report we need to see month actual, month budget, month budget
variance and the same for YTD (AC, BU and BU var.) We have different
departments: new units, used units, workshop, service, hire, parts and
admin.
We also have different branches: Sydney, Melbourne etc...

We also need to look at sub totals like gross margin, contribution, etc,
i.e. normal subtotals in a P&L.

At the moment we can generate a trial balance file with all the
information
above, and we've created tables for department, branch, account, and also
for
the separate month values linked to the above.

a) how do I build my P&L? I've started off with a query but got stuck
since
I need subtotals...

b) what is the best set up in this scenario, maybe we've started off on
the
wrong foot?

Thankful for replies,

--

 

[Guest]

Thanks Pieter,

In reports can I have many subtotals, since I'm looking at approx. 10
subtotals in our current Excel format?

Cheers,

 

[Pieter Wijnen]

Yes, by repeating the same approach for each group level

Good luck

Pieter

 

[Pieter Wijnen]

Yes, by repeating the same approach for each group level

Good luck

Pieter

Thanks Pieter,

In reports can I have many subtotals, since I'm looking at approx. 10
subtotals in our current Excel format?

Cheers,

--