Using result of one column in another column 1

[ShabanaHafiz]

I am using Microsoft Access 2003.

In an Access Query, I have one table and one query; LogBook, VHCostPerKM (Vehicle Cost Per KM).

Fields in LogBook table are:
ProjectID
OfficeID
VehicleID
DateOfTravel
KMBeforeTravel
KMAfterTravel

Fields in VHCostPerKM query are:
OfficeID
VehicleID
UnitCost

For a given DateOfTravel, I need to calculate for each ProjectID, OfficeID and VehicleID the following:

Sum of KM Traveled
Cost per Project

KM Traveled is calculated as KMAfterTravel – KMBeforeTravel. I need to multiply the result of KM Traveled with UnitCost to get the Cost per Project. The SQL View of query before Cost per Project is as follows:

SELECT LogBook.ProjectID, LogBook.OfficeID, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled]
FROM VHCostPerKM INNER JOIN LogBook ON VHCostPerKM.VehicleID = LogBook.VehicleID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY LogBook.ProjectID, LogBook.OfficeID, LogBook.VehicleID;

For Cost per Project, in the Design View, I tried the following:
Field: Cost per Project: [KM Traveled]*[Unit Cost]
Total: Expression

But got the following error:
You tried to execute a query that does not include the specified expression ‘[KM Traveled]*[Unit Cost]’ as part of an aggregate function

 

[PHV]

Cost per Project: Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ShabanaHafiz]

I am facing one more problem in this query. I need to show description in place of ProjectID, OfficeID and VehicleID.

I added Project table and replaced ProjectID with Description field of Project Table. This worked fine and at this stage, SQL View was as follows:

SELECT Project.Description AS Project, LogBook.OfficeID, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM (VHCostPerKM INNER JOIN LogBook ON (VHCostPerKM.OfficeID = LogBook.OfficeID) AND (VHCostPerKM.VehicleID = LogBook.VehicleID)) INNER JOIN Project ON LogBook.ProjectID = Project.ProjectID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY Project.Description, LogBook.OfficeID, LogBook.VehicleID;

Then I added Office table and replaced OfficeID with Description field of Office Table. When I executed the query this time, Office column appeared blank. At this stage, SQL View was as follows:

SELECT Project.Description AS Project, Office.Description AS Office, LogBook.VehicleID, Sum([KMAfterTravel]-[KMBeforeTravel]) AS [KM Traveled], Sum(([KMAfterTravel]-[KMBeforeTravel])*[Unit Cost]) AS [Cost per Project]
FROM ((VHCostPerKM INNER JOIN LogBook ON (VHCostPerKM.OfficeID = LogBook.OfficeID) AND (VHCostPerKM.VehicleID = LogBook.VehicleID)) INNER JOIN Project ON LogBook.ProjectID = Project.ProjectID) INNER JOIN Office ON LogBook.OfficeID = Office.OfficeID
WHERE (((LogBook.DateOfTravel) Between [Forms]![rpt2Select]![cboFromDate] And [Forms]![rpt2Select]![cboToDate]))
GROUP BY Project.Description, Office.Description, LogBook.VehicleID;