Solved Why this Opening and Closing is Really Hard?

[prabha_friend]

Vehicles With Opening Details:

Vehicle ID​	Regn No​	DOO​	Opening​
101​	TN49BH1440	23-12-2022 16:44:01​	390585​
102​	TN49BK7621	23-12-2022 16:44:01​	270129​
103​	TN49BR0860	23-12-2022 16:44:01​	310801​
104​	TN49BP7060	23-12-2022 16:44:01​	395299​
105​	TN55AM6543	23-12-2022 16:44:01​	324352​
106​	TN49BK9946	23-12-2022 16:44:01​	236703​
107​	TN49BK2734	23-12-2022 16:44:01​	230889​
201​	TN49BS6022	23-12-2022 16:44:01​	179263​
202​	TN49BT9983	23-12-2022 16:44:01​	164212​
203​	TN49BM0397	23-12-2022 16:44:01​	279937​
204​	TN49BP8947	23-12-2022 16:44:01​	247762​
205​	TN49AQ3830	23-12-2022 16:44:01​	259389​
206​	TN49BP2919	23-12-2022 16:44:01​	219839​
301​	TN49BF9747	23-12-2022 16:44:01​	286342​
302​	TN49BA9756	23-12-2022 16:44:01​	263347​
401​	TN49BJ2997	23-12-2022 16:44:01​	182270​
501​	TN49BK8606	23-12-2022 16:44:01​	199113​
502​	TN19P4817	23-12-2022 16:44:01​	283338​

Trip with Vehicle Details and Distance Covered:

Trip No​	DOJ​	Vehicle​	KMs​
12​	19-12-2022​	202​	773​
16​	19-12-2022​	107​	121​
17​	19-12-2022​	302​	72​
19​	19-12-2022​	301​	122​
20​	19-12-2022​	106​	107​
22​	20-12-2022​	107​	250​
23​	20-12-2022​	206​	219​
24​	20-12-2022​	104​	56​
25​	20-12-2022​	101​	66​
26​	20-12-2022​	106​	60​
27​	20-12-2022​	202​	131​
28​	21-12-2022​	102​	173​
29​	21-12-2022​	107​	343​
30​	21-12-2022​	104​	120​
31​	21-12-2022​	205​	110​
32​	21-12-2022​	202​	120​
33​	21-12-2022​	106​	55​
34​	21-12-2022​	101​	70​
35​	21-12-2022​	104​	13​
36​	22-12-2022​	104​	135​
37​	22-12-2022​	204​	120​
38​	22-12-2022​	201​	241​
39​	22-12-2022​	202​	148​
40​	22-12-2022​	301​	242​
41​	22-12-2022​	101​	152​
42​	22-12-2022​	106​	278​
43​	22-12-2022​	107​	21​
44​	22-12-2022​	203​	70​
45​	22-12-2022​	206​	175​
46​	22-12-2022​	501​	32​
47​	22-12-2022​	102​	111​
48​	22-12-2022​	104​	12​
49​	22-12-2022​	107​	100​
50​	22-12-2022​	104​	112​

How to Bring and "Opening" and "Closing" KMs for each Trip?
Please Help. Thank You.

With Hope,
Prabhakaran

 

[isladogs]

Use a subquery. See the meter readings example at

Microsoft Access tips: Subquery basics

Using subqueries in Microsoft Access. What subqueries are, and examples of using subqueries.

allenbrowne.com

 

[arnelgp]

you should adjust the "Opening" (maybe a littler later) since you need it for the calculation.
look at your trip date they are "earlier" than your opening date?

doesn't the odometer tells you the "Closing" for each trip?
i think what you should record is the odometer (the km can be computed from the beginning and ending).

 

[prabha_friend]

Main Query is ready:
SELECT Trip.ID, Vehicle.ID, Trip.DOJ, Vehicle.Opening, Trip.Total_Distance
FROM Vehicle INNER JOIN Trip ON Vehicle.ID = Trip.Vehicle_ID
WHERE (((Vehicle.ID)=101) AND (Not (Trip.DOJ) Is Null) AND ((Trip.Total_Distance)>0));

How to write the SubQuery?

 

[prabha_friend]

(SELECT SUM(Total_Distance) FROM TRIP WHERE TRIP.DOJ<Query1.doj)

Not working

 

[mike60smart]

Main Query is ready:
SELECT Trip.ID, Vehicle.ID, Trip.DOJ, Vehicle.Opening, Trip.Total_Distance
FROM Vehicle INNER JOIN Trip ON Vehicle.ID = Trip.Vehicle_ID
WHERE (((Vehicle.ID)=101) AND (Not (Trip.DOJ) Is Null) AND ((Trip.Total_Distance)>0));

How to write the SubQuery?

Hi
The attached is one way to do this.

See the Report for final Kms

 

[prabha_friend]

SELECT Trip.ID, Trip.Vehicle_ID, Trip.DOJ, Vehicle.Opening, Trip.Total_Distance, (SELECT Sum(Sub.Total_Distance) AS SumOfTotal_Distance
FROM Trip AS Sub
WHERE (((Sub.Vehicle_ID)= query1.[Vehicle_ID] AND Sub.DOJ< [Query1].[DOJ]))) AS PKM
FROM Vehicle INNER JOIN Trip ON Vehicle.ID = Trip.Vehicle_ID
WHERE (((Trip.Vehicle_ID)=101) AND (Not (Trip.DOJ) Is Null) AND ((Trip.Total_Distance)>0));

Also Not working

 

[mike60smart]

SELECT Trip.ID, Trip.Vehicle_ID, Trip.DOJ, Vehicle.Opening, Trip.Total_Distance, (SELECT Sum(Sub.Total_Distance) AS SumOfTotal_Distance
FROM Trip AS Sub
WHERE (((Sub.Vehicle_ID)= query1.[Vehicle_ID] AND Sub.DOJ< [Query1].[DOJ]))) AS PKM
FROM Vehicle INNER JOIN Trip ON Vehicle.ID = Trip.Vehicle_ID
WHERE (((Trip.Vehicle_ID)=101) AND (Not (Trip.DOJ) Is Null) AND ((Trip.Total_Distance)>0));

Also Not working

In the attached look at "qryVeh" This gives the Total Kms for Each Vehicle

 

[prabha_friend]

Successfully Done


SELECT Trip.ID, Trip.Vehicle_ID, Trip.DOJ, Vehicle.Opening, (SELECT Sum(Sub.Total_Distance) AS SumOfTotal_Distance
FROM Trip AS Sub
WHERE (((Sub.Vehicle_ID)= Trip.[Vehicle_ID] AND Sub.DOJ< trip.[DOJ]))) AS PKM, Trip.Total_Distance, [Opening]+Nz([PKM],0) AS OpeningKM, [OpeningKM]+[Total_Distance] AS ClosingKM
FROM Vehicle INNER JOIN Trip ON Vehicle.ID = Trip.Vehicle_ID
WHERE ((Not (Trip.DOJ) Is Null) AND ((Trip.Total_Distance)>0));