Report - Profit / Loss

[sundaram]

I have two tables
1. Purchase - a. Date, Code, Item, Qty, Unit, Rate, Total Amt
2. Sales - a. Date, Code, Item, Qty, Unit, Rate, Total Amt

I need a report comprehsive, showing the updated qty and sales - Purcase.

Thanks in advance.

Regards,
RAJAMALLU R

 

[vbaInet]

But why do you have separate tables for Sales and Purchase if they contain the same fields?

You could have ONE table with ONE field that will either indicate Sales OR Purchase.

Normalise

 

[sundaram]

Thanks for your kind response.
1. Table - TblMasterInv
1.ItCode and 2.Item (ItCode is the primary key)

2.TblPurTrn
DateItCodeItemPUnitPQtyPRatePTtlAmtPAvgRate

3.TblSalTrn
DateItCodeItemSUnitSQtySRateSTtlAmtSAvgRate
From the above table no. 1 and table no. 2.
I need report showing :
Itcode, Item, Punit, PQty,PRate,PTtlAmt, Sunit, SQty, SRate and STtlAmt.
one additional Column showing Profit/Loss with sign mark(+ or -)

I hope i have cleared you my question. Please help me out the question.

I Thanks in advance.
RAJAMALLU

 

[vbaInet]

The question hasn't really been answered. I asked, why are you using two tables, Purchase and Sales, which have the same fields.

1. Create one table
2. Add one field to the table and call it TransactType
3. TransactType will have two values, Sale or Purchase
4. Copy your Sale table into this new table and under the TransactType field right Sale for all the Sale records
5. Do the same for your purchase table

Read the link.

 

[sundaram]

As per your advise I have added additional column and named it as TTranscatType.

I have merged all the transcations in one table as TblTran
Date,ItCode,Item,TUnit,TQty,TRate,TTtlAmt,TTranscatType

I have created a report with the helpe of report wizard.
I got the sum of Total of TQty and Total of TTtlAmt at end of each item code.

Now my question is that I should get a consolidated statement showing the profit and loss of the each item codes like:
Item Code TQty TTtlAmt TQty TTtlAmt Profit or lost
A001 200 2500 180 1890 xyz

Please help me out.
Regards,
RAJAMALLU

 

[vbaInet]

How is Profit or Loss supposed to be calculated? In mathematical terms.

 

[sundaram]

I mean to ask you that
is there any syntex that it should show Sales Total amt - Purchase Total Amt = xym amt (Number) in the reprot.

Or
Is it possible to get the above answer in query. If yes then can you please guide me.

I appreciate in Advance.

RAJAMALLU

 

[vbaInet]

To calculate profit/loss where you have (+, - or 0), you:

1. Create another alias field and maybe call ProfitLoss
2. Your expression will look like this:

ProfitLoss: IIF(IsNull([Sales Total Amt] - [Purchase Total Amt]), Null, IIF(([Sales Total Amt] - [Purchase Total Amt]) = 0, 0, IIF(([Sales Total Amt] - [Purchase Total Amt]) < 0,"-","+")))

If you just want the actual Profit/Loss amount, then:

Create a new alias and just do a subtraction:
[Sales Total Amt] - [Purchase Total Amt]

 

[sundaram]

Thanks for your kind response.
I have copied your code and tried but I am not getting the result. In query. Please help me.

Also i have tried to make a form. trying to open it. It is showing error.

I am attaching the database file.

Thanks&Regards,
RAJAMALLU

 

[vbaInet]

I've had a look at your db and I don't see any place where you've done something?

 

[sundaram]

My question here is that, you see the report which is created with the help of wizard. Is it possible to edit it to get a report of Total (Sales) - Total (Purchase) to get profit or loss (amount).

In the mean time I have created one query, trying get the above result in the query.

But i could not get, can you please check my query and help me in getting the said result.

Thanks & Regards,
RAJAMALLU R B

 

[vbaInet]

I've seen that but I don't see your attempt in the query. Reread my post #8, follow it from steps 1 and if you're finding a particular step hard, ask questions.

 

[sundaram]

1. ProfitLoss: IIF(IsNull([Sales Total Amt] - [Purchase Total Amt]), Null, IIF(([Sales Total Amt] - [Purchase Total Amt]) = 0, 0, IIF(([Sales Total Amt] - [Purchase Total Amt]) < 0,"-","+")))


I have tried to run the query it is asking me SalesTotalAmt, PurchseTotalAmt


2. ProfitLoss: IIf(IsNull([TTranscatType(SalesTotalAmt)]-[TTranscatType(PurchaseTotalAmt)]),Null,IIf(([TTranscatType(SalesTotalAmt)]-[TTranscatType(PurchaseTotalAmt)])=0,0,IIf(([TTranscatType(SalesTotalAmt]-[TTranscatType(PurchaseTotalAmt])<0,"-","+")))

TTranscatType(SalesTotalAmt)
TTranscatType(PurchaseTotalAmt)
TTranscatType(SalesTotalAmt], 3 [Unknown] = [TTranscatType(PurchaseTotalAmt
TTranscatType(SalesTotalAmt)
TTranscatType(PurchaseTotalAmt)
TTranscatType(SalesTotalAmt], 3 [Unknown] = [TTranscatType(PurchaseTotalAmt
TTranscatType(SalesTotalAmt)
TTranscatType(PurchaseTotalAmt)
TTranscatType(SalesTotalAmt], 7 [Unknown] = [TTranscatType(PurchaseTotalAmt


3. ProfitLoss: IIf(IsNull([TTranscatType/SalesTotalAmt)]-[TTranscatType/PurchaseTotalAmt)]),Null,IIf(([TTranscatType/SalesTotalAmt)]-[TTranscatType/PurchaseTotalAmt)])=0,0,IIf(([TTranscatType/SalesTotalAmt]-[TTranscatType/PurchaseTotalAmt])<0,"-","+")))

TTranscatType/SalesTotalAmt)], 1 [Unknown] = [TTranscatType/PurchaseTotalAmt)], 2 [Unknown] = [TTranscatType/SalesTotalAmt], 3 [Unknown] = [TTranscatType/PurchaseTotalAmt
TTranscatType/SalesTotalAmt)], 1 [Unknown] = [TTranscatType/PurchaseTotalAmt)], 2 [Unknown] = [TTranscatType/SalesTotalAmt], 3 [Unknown] = [TTranscatType/PurchaseTotalAmt
TTranscatType/SalesTotalAmt)], 5 [Unknown] = [TTranscatType/PurchaseTotalAmt)], 6 [Unknown] = [TTranscatType/SalesTotalAmt], 7 [Unknown] = [TTranscatType/PurchaseTotalAmt

Too few parameters. Expected 0


4. [Sales Total Amt] - [Purchase Total Amt]
Enter parameter value
SalesTotalAmt
PurchaseTotalAmt


I think Sales, Purchase are from field : TTranscatType for the reason it may be showing error.

Can you please help.

Further in alias TotalAmt the data shown is fraction of two digits only can you explain me pelase.

 

[vbaInet]

The fields [Sales Total Amt] and [Purchase Total Amt] do not exist in your query. You need two queries, one to calculate the Sales Total amount and the other to calculate the Purchase Total amount. Create a Totals query for these and Sum.

 

[sundaram]

SELECT TblTran.ItCode, TblTran.Item, TblTran.TUnit, Sum(TblTran.TQty) AS SumOfTQty, Avg(TblTran.TRate) AS AvgOfTRate, [sumofTqty]*[AvgofTrate] AS TotalAmt, TblTran.TTranscatType
FROM TblTran
GROUP BY TblTran.ItCode, TblTran.Item, TblTran.TUnit, TblTran.TTranscatType;

Sir,
I have done as per above query and also tried TTranscatType=[Sales/Puchase]

It is showing row wise sales and purchase

I would like to know ItCode, Item, as aliase TotalPurchaseAmt, as aliase TotalSales and then Profit or Loss as aliase. Could help me out. I have tried nearly 6 hours.

Please do the needful.
Thanks in advance.
RAJAMALLU

 

[sundaram]

Dear,
I have done as per the blow code, But the out put is not correct.

SELECT TblTran.ItCode, TblTran.Item, TblTran.TUnit, Sum(TblTran.TQty) AS SumOfTQty, Avg(TblTran.TRate) AS AvgOfTRate, [SumofTQty]*[AvgofTrate] AS SalesTAmt, [SumofTQty]*[AvgTRate] AS PurchaseTAmt
FROM TblTran
GROUP BY TblTran.ItCode, TblTran.Item, TblTran.TUnit;


Please help me out
RAJAMALLU

 

[sundaram]

SELECT TblTran.ItCode, TblTran.Item, TblTran.TUnit, Sum(TblTran.TQty) AS SumOfTQty, Avg(TblTran.TRate) AS AvgOfTRate, [SumofTQty]*[AvgofTrate] AS SalesTAmt, [SumofTQty]*[AvgTRate] AS PurchaseTAmt, TblTran.TTranscatType
FROM TblTran
GROUP BY TblTran.ItCode, TblTran.Item, TblTran.TUnit, TblTran.TTranscatType;

Sir,
This is another way now could you please help me to get the right out put please.

I appreciate in advance.

Best Regards,
RAJAMALLU