Extract total cost from another table 1

[ShabanaHafiz]

I am using Microsoft Access 2003.

I created two tables to enter details of Vehicle Running and Maintenance; LogBook and VehicleMaint.

LogBook table records Vehicle Running Details:
OfficeID
VehicleID
SerialNumber
DateOfTravel
KMBeforeTravel
KMAfterTravel

VehicleMaint table records Fuel, Maintenance and Repair Cost.
VehicleID
InvoiceNumber
InvoiceDate
FuelCost
RepairCost

I need to create a query to use as report’s Record Source. In the report, I need to show total running per vehicle and cost. I need to show the following fields:
OfficeID
VehicleID
KMTraveled: [KMAfterTravel – KMBeforeTravel]
TotalCost: [FuelCost] + [RepairCost]

In the Design View of the query, I selected OfficeID, VehicleID, KMTraveled:[KMAfterTravel] – [KMBeforeTravel] and TotalCost: [FuelCost] + [RepairCost] in the field line. Then I clicked Totals button, and selected Group By, Group By, Sum, Sum for the four fields respectively.

I did not get the sum amount accurate. The joining operation is inserting total cost for each vehicle running entry. For example, if there is one invoice entered for Vehicle VH1 with total cost 50, in the query I am getting:
OfficeID VehicleID KMTraveled TotalCost
HO VH1 100 50
HO VH1 200 50
HO VH1 300 50

And after applying group by clause, I am getting:
HO VH1 600 150

I need the following result:
HO VH1 600 50

There can be more than one invoices entered for a vehicle. For example, vehicle VH2 has three invoices entered with total cost 20, 1000 and 200. In that case, for each logbook entry, I am getting:

HO VH2 25 20
HO VH2 25 1000
HO VH2 25 200
HO VH2 475 20
HO VH2 475 1000
HO VH2 475 200

On applying Group By, I am getting:
HO VH2 1500 2440

I need the following result:
HO VH2 500 1220

 

[JerryKlmns]

In your LogBook table add a column for TravelID
In your VehicleMaint table add a column for TravelID

Then query
SELECT OfficeID, VehicleID, Sum([KMAfterTravel] – [KMBeforeTravel]) As KMTraveled, SUM([FuelCost] + [RepairCost]) As TotalCost
FROM LogBook Inner Join VehicleMaint On
(LogBook.VehicleID=VehicleMaint.VehicleID) AND
(LogBook.TravelID=VehicleMaint.TravelID)
GROUP By OfficeID, VehicleID

 

[AlexCuse]

I think your problem is that you need to SUM on the right side of your join (costs and mileage (km-age?)) from vehiclemaint table to get total cost/mileage. Try something like this:

Select l.OfficeID
, l.VehicleID
, sum(v.KMAfterTravel - v.KMBeforeTravel)
, sum(v.FuelCost + v.RepairCost)
from LogBook l
inner join 
VehicleMaint v
on l.VehicleID = v.VehicleID
group by l.OfficeID, l.VehicleID

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[ShabanaHafiz]

Thanks.

I tried the following:

Select l.OfficeID
, l.VehicleID
, sum([blue]l[/blue].KMAfterTravel - [blue]l[/blue].KMBeforeTravel)
, sum(v.FuelCost + v.RepairCost)
from LogBook l
inner join 
VehicleMaint v
on l.VehicleID = v.VehicleID
group by l.OfficeID, l.VehicleID

But got the same result:
HO VH1 600 150
HO VH2 1500 2440

JerryKlmns:
TravelID addition is not possible as Vehicle Running and Vehicle Maintenance processes are happening independently. Monthly Report will not show the exact cost but approximate cost as follows:

1. From the LogBook table, for the VehicleID, get Sum(KMAfterTravel - KMBeforeTravel) for DateOfTravel falling in a particular month
2. From the VehicleMaint table, for the VehicleID, get Sum(FuelCost+RepairCost) for InvoiceDate falling in the same month.

So, in my query, I need to show the columns as:
OfficeID VehicleID KMTraveled(from LogBook) TotalCost(from VehicleMaint)

 

[JerryKlmns]

Another try

Query1
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT OfficeID, VehicleID, Sum([KMAfterTravel]–[KMBeforeTravel]) As KMTraveled 
FROM LogBook
WHERE DateOfTravel BETWEEN [StartDate] AND [EndDate]
GROUP By OfficeID, VehicleID;

Query2
PARAMETERS StartDate DateTime, EndDate DateTime;
SELECT VehicleID, Sum([FuelCost]+[RepairCost]) As TotalCost
FROM VehicleMaint 
WHERE InvoiceDate BETWEEN [StartDate] AND [EndDate]
GROUP By VehicleID;

Query3
SELECT Q1.OfficeID, Q1.VehicleID, Q1.KMTraveled, Q2.TotalCost
FROM Query1 As Q1 Inner Join Query2 As Q2 On
     Q1.VehicleID=Q2.VehicleID;

 

[ShabanaHafiz]

Thanks a lot.

It worked perfectly.

Thanks once again