Using Calculating field in report

aurosunil

Registered User.
Local time
Today, 11:23
Joined
Jan 20, 2003
Messages
14
Hi everyone,
i am facing a simple problem but too difficult for me. i am trying to devlop a assets maintenance application. i have a table named assets consisting of fields named acquiredquantity, acquiredprice, salequantity and salevalue. assets table also contains one field departmentID which contains the name of department to which the asset belongs. i want a report which contains only the current quantity and current value of the asset i.e.
current quantity = acquiredquantity-salequantity
current value =acquiredprice-salevalue
Report is sorted on the basis of departmentID
Is it possible to do it, i don,t want the four fields on which calculations are done to be shown?
sunil
 
Base the report on a query. Then in the query create fields for the calculations like this:

current quantity: acquiredquantity-salequantity
current value: acquiredprice-salevalue

Then you can place these fields in the report without the fields it uses to perform the calculation.
 
Rob.Mills said:
Base the report on a query. Then in the query create fields for the calculations like this:

current quantity: acquiredquantity-salequantity
current value: acquiredprice-salevalue

Then you can place these fields in the report without the fields it uses to perform the calculation.

Hi Rob,
I tried but it gives #error instead of data. however when the fields it uses are shown, it worked well. I am using MS access 2000.
sunil
 
Here is the query:
SELECT [Asset Categories].AssetCategory, Assets.AssetDescription, Assets.DateAcquired, Assets.AcquiredQuantity, Assets.AcquiredPrice, Departments.Department, Assets.SoldQuantity, Assets.SoldValue
FROM Departments RIGHT JOIN ([Asset Categories] RIGHT JOIN Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON Departments.DepartmentID = Assets.DepartmentID
WHERE (((Departments.Department)=[Forms]![Reportbydepartment]![Department]));

and here are the expressions:
=[Assets]!AcquiredPrice-[Assets]!SoldValue
=[Assets]![AcquiredQuantity]-[Assets]!SoldQuantity
 
You did not add the calculations to the query. Try the following. It has two new fields in it that you can bind to controls.

SELECT [Asset Categories].AssetCategory, Assets.AssetDescription, Assets.DateAcquired, Assets.AcquiredQuantity, Assets.AcquiredPrice, Departments.Department, Assets.SoldQuantity, Assets.SoldValue, Assets]!AcquiredPrice-[Assets]!SoldValue As NetPrice, [Assets]![AcquiredQuantity]-[Assets]!SoldQuantityAs NetQuantity
FROM Departments RIGHT JOIN ([Asset Categories] RIGHT JOIN Assets ON [Asset Categories].AssetCategoryID = Assets.AssetCategoryID) ON Departments.DepartmentID = Assets.DepartmentID
WHERE (((Departments. Department)=[Forms]![Reportbydepartment]![Departme
nt]));
 
Thanks a lot Pat. Ir did work. You see i have tried it before posting my problem but some how it was not working. Any way your support has given me a moral boost as it is my first program in access.
Thank You again.
sunil:)
 

Users who are viewing this thread

Back
Top Bottom