Subquery with Date Criteria

prabha_friend

Prabhakaran Karuppaih
Local time
Today, 11:36
Joined
Mar 22, 2009
Messages
1,012
Table1 Table1

IDField1Field2
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"
 
Troubleshooting Tip:
  1. Subselect may return only one record.
  2. Look closely at the Where expression. ... Alias can help
SQL:
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?
 
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
 
Load is a rather little needed aggregate function.
Maybe you need Max(...).
Alternatively you can use Top 1 with Order By.

Example:
SQL:
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?
 
Last edited:
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;
 
How to give one more criteria especially a Datefield using AND
 
Current Result:
Query1 Query1

VehicleIDTripDateREGN_NoTripIDPlace_TravelledTrip_OpeningTrip_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​
 
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.
 
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 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.
 
I just have to report the trips with the fields of "Trip_Starting", "Trip_Distance" and "Trip_Closing"
 
There is no field named "Distance Meter". I can't help. I'm sure someone else will.
 
Code:
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?
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?
 
Code:
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?
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
 

Users who are viewing this thread

Back
Top Bottom