If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Need help in this report
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. |
#2
|
|||
|
|||
Need help in this report
On Aug 8, 6:34 am, Jack wrote:
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. |
#3
|
|||
|
|||
Need help in this report
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. "krissco" wrote: On Aug 8, 6:34 am, Jack wrote: 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. |
#4
|
|||
|
|||
Need help in this report
On Aug 8, 11:42 am, Jack wrote:
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 |
Thread Tools | |
Display Modes | |
|
|