Subquery with Date Criteria

[prabha_friend]

Table1 Table1

ID	Field1	Field2
2​	01-01-2022​	500​
3​	02-01-2022​	1100​
4​	03-01-2022​	1700​

SELECT Table1.ID, Table1.Field1, Table1.Field2, (Select [Table1].[Field2] from [Table1] where [Table1].[Field1]<[Table1].[Field2]) AS Expr1
FROM Table1;

Expecting "Opening Balance"

 

[Josef P.]

Troubleshooting Tip:

1. Subselect may return only one record.
2. Look closely at the Where expression. ... Alias can help

SELECT
   T.ID, T.Field1, T.Field2,
   (Select ... from [Table1] as X where ...) AS Expr1
FROM Table1 as T

"..." were incorrect expressions.

BTW:
[Table1].[Field1]<[Table1].[Field2]
The comparison between date and number brings what?

 

[prabha_friend]

Sorry that expression was wrong. Please find below the required Expression:
SELECT T.*, (Select Last(Trips.Fuel_Closing) from Trips where (Trips.Dateval < T.TripDate AND (Trips.Fuel_Closing < t.Trip_Closing) )) AS Trip_Opening
FROM Vehicle_Report AS T;

Correct Value not coming

 

[Josef P.]

Load is a rather little needed aggregate function.
Maybe you need Max(...).
Alternatively you can use Top 1 with Order By.

Example:

SELECT
     T.*
     , (
           Select Top 1 Trips.Fuel_Closing
           From Trips
           Where Trips.Dateval < T.TripDate AND Trips.Fuel_Closing < T.Trip_Closing
           Order By ??? [desc/asc]
       ) AS Trip_Opening
FROM Vehicle_Report AS T

[OT]
Do you know how to mark a code in the forum with code tags?

 

[prabha_friend]

How to give one more criteria especially a Datefield using AND

 

[prabha_friend]

Current Result:
Query1 Query1

VehicleID	TripDate	REGN_No	TripID	Place_Travelled	Trip_Opening	Trip_Closing
1​	01-03-2023​	TN 49 CB 1650	92​	Thanjavur	​	46666​
1​	03-03-2023​	TN 49 CB 1650	93​	Thanjavur	46666​	46694​
1​	03-03-2023​	TN 49 CB 1650	94​	Thanjavur	46666​	46703​
1​	06-03-2023​	TN 49 CB 1650	95​	Thanjavur	46703​	46761​
1​	06-03-2023​	TN 49 CB 1650	96​	Thanjavur	46703​	46780​
1​	06-03-2023​	TN 49 CB 1650	97​	Thanjavur	46703​	46796​
1​	07-03-2023​	TN 49 CB 1650	98​	Thanjavur	46796​	46809​
1​	07-03-2023​	TN 49 CB 1650	99​	Thanjavur	46796​	46828​
1​	07-03-2023​	TN 49 CB 1650	100​	Thanjavur	46796​	46851​

 

[prabha_friend]

Need 46694 in Trip_Opening on the 3rd Row...

 

[prabha_friend]

 

[prabha_friend]

I just have to report the trips with the fields of "Trip_Starting", "Trip_Distance" and "Trip_Closing"

 

[prabha_friend]

Fuel_Closing is the reading of "Distance Meter" after the trips

 

[mike60smart]

Fuel_Closing is the reading of "Distance Meter" after the trips

It would help if you uploaded your Database.

 

[prabha_friend]

Vehicles.accdb

drive.google.com

includes garbage (unnecessary Queries) and some extra tables too...

 

[prabha_friend]

Vehicles.accdb

drive.google.com

 

[prabha_friend]

Sub Generate_VehicleReportData()
Dim VehicleRecords As Recordset
Set VehicleRecords = CurrentDb.OpenRecordset("Vehicle_Report_Data", dbOpenDynaset)
With VehicleRecords
    .MoveLast
    .MoveFirst
    While Not .EOF
        .Edit
        If .AbsolutePosition = 0 Then
            .Fields("Trip_Opening").Value = 0
        Else
            Dim PreviousClosing As Long
            .MovePrevious
            PreviousClosing = .Fields("Trip_Closing").Value
            .MoveNext
            .Edit
            .Fields("Trip_Opening").Value = PreviousClosing
        End If
        .Update
        .MoveNext
    Wend
End With
End Sub

I know its not perfect. How to do without the following code?

            Dim PreviousClosing As Long

            .MovePrevious

            PreviousClosing = .Fields("Trip_Closing").Value

            .MoveNext

            .Edit

can't we read values from a recordset like we do in an excel sheet? using rows and columns?

 

[mike60smart]

Sub Generate_VehicleReportData()
Dim VehicleRecords As Recordset
Set VehicleRecords = CurrentDb.OpenRecordset("Vehicle_Report_Data", dbOpenDynaset)
With VehicleRecords
    .MoveLast
    .MoveFirst
    While Not .EOF
        .Edit
        If .AbsolutePosition = 0 Then
            .Fields("Trip_Opening").Value = 0
        Else
            Dim PreviousClosing As Long
            .MovePrevious
            PreviousClosing = .Fields("Trip_Closing").Value
            .MoveNext
            .Edit
            .Fields("Trip_Opening").Value = PreviousClosing
        End If
        .Update
        .MoveNext
    Wend
End With
End Sub

I know its not perfect. How to do without the following code?

            Dim PreviousClosing As Long

            .MovePrevious

            PreviousClosing = .Fields("Trip_Closing").Value

            .MoveNext

            .Edit

can't we read values from a recordset like we do in an excel sheet? using rows and columns?

Hi
I think your table structure is wrong.
A Vehicle makes a Trip (Journey)
Each Journey should have a Mileage Start and a Mileage End Reading

You are trying to put the Mileage End Reading in the Fuels Table

 

[Josef P.]

Presumably, you're looking for something like this:

SELECT
    V.ID AS VehicleID,
    T.DateVal AS TripDate,
    V.REGN_No,
    T.ID AS TripID,
    T.Place_Travelled,
    (  
        Select Top 1 X.Fuel_Closing 
        from Trips as X 
        where X.Vehicle = T.Vehicle and X.Fuel_Closing < T.Fuel_Closing 
        Order by Fuel_Closing desc 
     ) AS Trip_Opening,
    T.Fuel_Closing AS Trip_Closing
FROM
    Vehicles as V
    INNER JOIN
    Trips as T ON T.Vehicle = V.ID
ORDER BY
    V.ID, T.DateVal, T.ID

Note: if you use an active DBMS like SQL Server, there are simpler variants than a subselect.

 

[prabha_friend]

Previous End is the New Beginning