Hi All
Wondered if you could help.
Basically I have 2 tables that contain all the data I want for my report,
but I need to put it in a particular way and I need to display it in an ASP
page so my queries got to be manual rather than an MS Excel/Query 'munge'.
To be honest, the report itself is very basic, so hopefully my ramble will
make sense. I need to report that shows 6 columns at best, 4 columns if
it's easier. Report looks like the following:
Account Code - standard text field
Account Name - standard text field
Turnover (Year to Date) 2002 - standard decimal number (single??) field
Turnover (Year to Date) 2003 - standard decimal number (single??) field
(not vital, but would be nice)
Deviation as a straight figure between what the 2003 turnover figure is
against the 2002 figure - ad hoc calc field
Deviation as a % between what the 2003 turnover figure is against the 2002
figure - ad hoc calc field
My table structure is as follows:
TABLE 1 - ACCOUNTS
This contains all of the account codes (PK) and names as ACCOUNTID and NAME
respectively. It also contains a field called LEDGER which I would need to
use in a Where clause to set this to 1 (1 = sales and 2 = purchases).
TABLE 2 - ACCOUNTBUDGETS
This contains the Turnover details and is linked to the ACCOUNTS table by an
ACCOUNTID (FK??). This table contains a TURNOVER field for the actual
turnover amount, a PERIOD field for the months of a year (eg, 1 - 12 - that
being Jan to Dec) and a YEAR field for the relevant years.
In this ACCOUNTBUDGETS table it basically keeps a line-by-line record of
each turnover for each period and year for each account code. As you
probably would with any transaction line storing table.
An important part of this is that I want to show all of the Account codes
and names that relate to LEDGER=1 even if they have no entries in the
ACCOUNTSBUDGETS table. The reason being is that I want to be able to show
that an account might not have traded for 2002 and therefore their 2003 Year
to date might be really good cos they've sold loads in 2003.
So the final part is that I need summarise the Year to date figures next to
each account code and name. To do this I need to take what the month number
is now (eg, Dec = 12) and summarise all the turnover figures for 2002 upto
12 and then all the turnover figures for 2003 upto 12. If you can imagine
when this report is run in March the report would summarize from 1 to 3
rather than 1 - 12 in the last examples.
The nice bits on the end would be that they would give 2 different calcs
against what the 2 turnover figures stated.
The result should be something like the following:
December Turnover YTD
Deviation
Acc No: Account Name 2003 2002 £
%
A001 Alpha Motor 9,250 5,398 3,852
71%
A003 A J V Secure 0
94 -1,294 -100%
A004 Aye Gee 0
39 -4,239 -100%
A007 P H Allin 2,235
08 -1,973 -47%
Any ideas??
Rgds
Robbie
Wondered if you could help.
Basically I have 2 tables that contain all the data I want for my report,
but I need to put it in a particular way and I need to display it in an ASP
page so my queries got to be manual rather than an MS Excel/Query 'munge'.
To be honest, the report itself is very basic, so hopefully my ramble will
make sense. I need to report that shows 6 columns at best, 4 columns if
it's easier. Report looks like the following:
Account Code - standard text field
Account Name - standard text field
Turnover (Year to Date) 2002 - standard decimal number (single??) field
Turnover (Year to Date) 2003 - standard decimal number (single??) field
(not vital, but would be nice)
Deviation as a straight figure between what the 2003 turnover figure is
against the 2002 figure - ad hoc calc field
Deviation as a % between what the 2003 turnover figure is against the 2002
figure - ad hoc calc field
My table structure is as follows:
TABLE 1 - ACCOUNTS
This contains all of the account codes (PK) and names as ACCOUNTID and NAME
respectively. It also contains a field called LEDGER which I would need to
use in a Where clause to set this to 1 (1 = sales and 2 = purchases).
TABLE 2 - ACCOUNTBUDGETS
This contains the Turnover details and is linked to the ACCOUNTS table by an
ACCOUNTID (FK??). This table contains a TURNOVER field for the actual
turnover amount, a PERIOD field for the months of a year (eg, 1 - 12 - that
being Jan to Dec) and a YEAR field for the relevant years.
In this ACCOUNTBUDGETS table it basically keeps a line-by-line record of
each turnover for each period and year for each account code. As you
probably would with any transaction line storing table.
An important part of this is that I want to show all of the Account codes
and names that relate to LEDGER=1 even if they have no entries in the
ACCOUNTSBUDGETS table. The reason being is that I want to be able to show
that an account might not have traded for 2002 and therefore their 2003 Year
to date might be really good cos they've sold loads in 2003.
So the final part is that I need summarise the Year to date figures next to
each account code and name. To do this I need to take what the month number
is now (eg, Dec = 12) and summarise all the turnover figures for 2002 upto
12 and then all the turnover figures for 2003 upto 12. If you can imagine
when this report is run in March the report would summarize from 1 to 3
rather than 1 - 12 in the last examples.
The nice bits on the end would be that they would give 2 different calcs
against what the 2 turnover figures stated.
The result should be something like the following:
December Turnover YTD
Deviation
Acc No: Account Name 2003 2002 £
%
A001 Alpha Motor 9,250 5,398 3,852
71%
A003 A J V Secure 0
94 -1,294 -100%
A004 Aye Gee 0
39 -4,239 -100%
A007 P H Allin 2,235
08 -1,973 -47%
Any ideas??
Rgds
Robbie
Comment