Need help in this report

[Jack]

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.

Any help is appreciated. Thanks.

 

[krissco]

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.

Any help is appreciated. Thanks.

I suppose you could use a series of union queries to get the desired
result.

Group Description Amount
3000 Net Sales 100000
5000 Total Var Cost 75000
7000 Total Other Cost 20000

select [Group], Description, Amount
from tblName
union
select "6000", "Gross Margin", myAmt as Amount
from (select sum(iif([group] = "3000", 1, -1) * Amount) as myAmt from
tblName where [Group] in ("3000", "5000")) as mySubQry
.. . .

I admit, the subquery/union approach is a little nasty to look at.
Another approach could be to use a temporary table:

select * into myTempTable
from tblName

Now insert new records into the table:

insert into myTempTable (Group, Description, Amount)
select "6000", "Gross Margin", myAmt
from (select sum(iif([group] = "3000", 1, -1) * Amount) as myAmt from
myTempTable where [Group] in ("3000", "5000")) as mySubQry

insert into myTempTable (Group, Description, Amount)
select "9000", "Net Profit", myAmt
from (select sum(iif([group] = "6000", 1, -1) * Amount) as myAmt from
myTempTable where [Group] in ("6000", "7000")) as mySubQry

Now base your report on myTempTable


-Kris

PS. Avoid using reserved words as field names (Group) - that just
causes issues.

 

[Jack]

Hi Krissco,
I ran the script according to the one you have suggested. Unfortunately it
did not work as it gave error stating "Syntax Error in From Clause'.So you
have any idea why this is so. THanks again.

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.

Any help is appreciated. Thanks.

I suppose you could use a series of union queries to get the desired
result.

Group Description Amount
3000 Net Sales 100000
5000 Total Var Cost 75000
7000 Total Other Cost 20000

select [Group], Description, Amount
from tblName
union
select "6000", "Gross Margin", myAmt as Amount
from (select sum(iif([group] = "3000", 1, -1) * Amount) as myAmt from
tblName where [Group] in ("3000", "5000")) as mySubQry
.. . .

I admit, the subquery/union approach is a little nasty to look at.
Another approach could be to use a temporary table:

select * into myTempTable
from tblName

Now insert new records into the table:

insert into myTempTable (Group, Description, Amount)
select "6000", "Gross Margin", myAmt
from (select sum(iif([group] = "3000", 1, -1) * Amount) as myAmt from
myTempTable where [Group] in ("3000", "5000")) as mySubQry

insert into myTempTable (Group, Description, Amount)
select "9000", "Net Profit", myAmt
from (select sum(iif([group] = "6000", 1, -1) * Amount) as myAmt from
myTempTable where [Group] in ("6000", "7000")) as mySubQry

Now base your report on myTempTable


-Kris

PS. Avoid using reserved words as field names (Group) - that just
causes issues.

 

[krissco]

Hi Krissco,
I ran the script according to the one you have suggested. Unfortunately it
did not work as it gave error stating "Syntax Error in From Clause'.So you
have any idea why this is so. THanks again.

Yeah, Access likes to mess w/ subqueries (re-write them into its own
syntax to make them impossible to execute). . .
Take the subqueries and save them as individual select queries, refer
to the saved query instead of the subquery:

Save this as "mySubQuery"
select sum(iif([group] = "3000", 1, -1) * Amount) as myAmt from
myTempTable where [Group] in ("3000", "5000")

Remove the subquery from this query and reference it as a view
instead.
insert into myTempTable (Group, Description, Amount)
select "6000", "Gross Margin", myAmt
from mySubQuery

Additionally, watch out for "Group"

-Kris