Subquery with Date Criteria (1 Viewer)

[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?

 

[Pat Hartman]

You need to summarize the subquery to combine all the previous records into the opening balance and therefore, you cannot include the date in the select clause.

SELECT Table1.ID, Table1.Field1, Table1.Field2, (Select Sum([Sub].[Field2]) As Exp from [Table1] as Sub where [Table1].[Field2]<[Sub].[Field2]) AS OpeningBal
FROM Table1;

 

[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...

 

[Pat Hartman]

When you don't know how to write SQL, the simplest solution is to use the QBE. Select the columns you want and add the criteria in the WHERE cells. Criteria on the same line is AND'd, Criteria on separate lines is OR'd

The QBE cannot display subqueries visually. Jet and ACE also do not optimize subselects efficiently so I don't use them unless that is the ONLY solution. For your situation, I would use a union query that contains two queries. One that is a totals query and selects the opening balance and the second is the query that selects the current detail.

Your most recent post changed the question.

 

[Pat Hartman]

Now that you have posted new pictures and posted a different question, my previous answers are not your solution. The beginning balance you are looking for is the ending balance of the previous record. To find that, you need criteria that can find the "last record less than this one". That means you are going to use the Max() function in your "right" side table if you use a simple left join or if you use a subselect, the criteria applies to the "sub" table.

If you want exact syntax, it is best to post a database with a sample of the data.

 

[prabha_friend]

 

[Pat Hartman]

@prabha_friend that isn't a database with a table full of data. I also don't know what starting balance you are looking for. There is no Mileage field in the trips table. Are you trying to calculate fuel usage or mileage? What is Fuel_Closing? Is that how much fuel it took to refill the tank.

 

[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

 

[Pat Hartman]

There is no field named "Distance Meter". I can't help. I'm sure someone else will.

 

[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