G
Guest
Firstly, i am new to access, and secondly i am new to 2007.
(Sorry for the long question / information)
I have a database (MS Access 2007) that contains many tables of data (linked
via SQL from another software program "Pronto").
I have culled the data into one query. Copied the query and went back into
each query and put in a criteria for query 1: income categories only and
query 2: expenditure categories only.
(I did have only one query with no criterias set, but then i couldn’t alter
my report to split the data into income and expenditure - each with subtotals
and then a grandtotal – as described below) – maybe this is where i’m going
wrong to start with.
So, now I have two queries. Query 1 contains income data and Query 2
contains Expenditure data. They both have the same headings "Ref", "Proj.
Desc", "Proj Mgr", "Category", "Budget", "Actual", "Diff" (diff = budget -
actual).
So i have created a report with the expenditure query (#2) and this report
is exactly how i want it to look. With "Ref", "Proj. Desc" & "Proj Mgr" at
the top of the report in "Stacked" formation. and "Category", "Budget",
"Actual" & "Diff" in tabular formation underneath.
But then i need the same format but with the income lines shown on the same
report.
P&L Details
Ref: ABC
Project: Suburb, Street
Proj Mgr: John Smith
Cat Budget Actual Diff $ Diff %
3100 26,172 5,000 21,172 (??% calculation)
3200 3,316 2,858 458
Income Ttl 29,488 7,858 21,630
3300 1,520 0 1,520
3301 591 691 -100
3504 7,400 6,000 1,400
Expend Ttl 9,511 6,691 2,820
Profit/(Loss) 19,977 1,167 18,810
Margin 67.75 14.85
(Sorry for the long question / information)
I have a database (MS Access 2007) that contains many tables of data (linked
via SQL from another software program "Pronto").
I have culled the data into one query. Copied the query and went back into
each query and put in a criteria for query 1: income categories only and
query 2: expenditure categories only.
(I did have only one query with no criterias set, but then i couldn’t alter
my report to split the data into income and expenditure - each with subtotals
and then a grandtotal – as described below) – maybe this is where i’m going
wrong to start with.
So, now I have two queries. Query 1 contains income data and Query 2
contains Expenditure data. They both have the same headings "Ref", "Proj.
Desc", "Proj Mgr", "Category", "Budget", "Actual", "Diff" (diff = budget -
actual).
So i have created a report with the expenditure query (#2) and this report
is exactly how i want it to look. With "Ref", "Proj. Desc" & "Proj Mgr" at
the top of the report in "Stacked" formation. and "Category", "Budget",
"Actual" & "Diff" in tabular formation underneath.
But then i need the same format but with the income lines shown on the same
report.
P&L Details
Ref: ABC
Project: Suburb, Street
Proj Mgr: John Smith
Cat Budget Actual Diff $ Diff %
3100 26,172 5,000 21,172 (??% calculation)
3200 3,316 2,858 458
Income Ttl 29,488 7,858 21,630
3300 1,520 0 1,520
3301 591 691 -100
3504 7,400 6,000 1,400
Expend Ttl 9,511 6,691 2,820
Profit/(Loss) 19,977 1,167 18,810
Margin 67.75 14.85