Populate controls with previous record using a query

[Mycle]

Here I am back again with this issue. @Ratslinger has helped previously and I have studied SQL and using joins but still am not able to achieve what I want, maybe it’s not possible, I would like to know.

The DB is a simple record of trips and accommodation. When I arrive I record the details and again on departure. So the next record entry form should be able to present me with departure details from the last record, date, time and odometer. The form then should be able to show the trip time and distance traveled in calculated fields.

Surely it should not be necessary to enter these details again in a new record only the details of the accommodation, arrive - date, time and odometer, and depart - date, time and odometer. The trip details - time and odometer should be able to be calculated and presented on the form. I have struggled with designing a query in SQL which will retrieve records from the previous record, could not figure how to use previous, top etc.

DB Attached and pic of the form and what I want to achieve.

Here’s hoping

Mike

BaseVanTripsVer2.0.odb

Edit 16:28 02/01/2018

Reply from Ratslinger ( 1 hour ago )

First, by what was presented in the
sample I should have simply waited for
you to post something with somewhat
correct information. Etc

Sample ------ FromMycle2.odb

I attempted to calculate the trip time but my understanding of SQL is limited. Am I getting close with the code below. I get a syntax error, after checking the Datediff Function I’m unsure if it should be

Datediff(‘hh’ “B”.“DepartTimePrior”,“ArriveTime”) “TripTime”
Or
Datediff(‘hour’ “B”.“DepartTimePrior”,“ArriveTime”) “TripTime”

Thanks

Mike

SELECT “TripSectorID”, “PrevAccom”,Datediff(‘hour’ “B”.“DepartTimePrior”,“ArriveTime”) “TripTime” , “ArriveOdom” - “B”.“DepartOdomPrior” “SegMileage”, “B”.* FROM “TripSectors” “A” LEFT JOIN ( SELECT “TripSectorID” “TripSectorIDPrior”, “AccommodationID” “AccommodationIDPrior”, “ArriveDate” “ArriveDatePrior”, “ArriveTime” “ArriveTimePrior”, “ArriveOdom” “ArriveOdomPrior”, “DepartDate” “DepartDatePrior”, “DepartTime” “DepartTimePrior”, “DepartOdom” “DepartOdomPrior”, “PrevAccom” “PrevAccomPrior” FROM “TripSectors” ) “B” ON “A”.“PrevAccom” = “B”.“AccommodationIDPrior”

Edited as requested @Ratslinger

Edit 17:06 02/01/2018

@Ratslinger 8 min ago

As for the SQL, you need to use the
syntax for the database in use. For
the HSQLDB v1.8 embedded it is:

Datediff(‘hour’,
“B”.“DepartTimePrior”,“ArriveTime”)
“TripTime” Note the comma after
‘hour’.

[Ratslinger]

Hello,

This concept would be the same as previously presented. However with the provided sample this is no longer possible. You have changed the table contents and now it appears there is no longer any connection between the current record and any previous record.

[Ratslinger]

@Mycle Please don’t re-post every item when a simple comment is appropriate. Please remove unnecessary info from question and post your further info as comments. The current method in which you are posting can end up being pages long!

[Ratslinger]

@Mycle Again that last addition to the question, that really belongs in a comment under the appropriate answer. You should add to the question only information to clarify the question itself.

Maybe look at some other questions and answers as to how they are posted.

And to be sure you understand, your questions are always welcome.

[Ratslinger]

@Mycle PLEASE stop adding what are “comments” to your original question. This send large messages/emails (the entire text) to those answering. PLEASE use add a comment under the answer/comment you are responding to.

[Ratslinger]

Hello,

First, by what was presented in the sample I should have simply waited for you to post something with somewhat correct information. Beside no connection in records (mentioned in the comment), there were multiple table and form fields with incorrect types such as odometer reading as a date field. There were others. Had to modify table & form. Please be more considerate in the future. The contents of this sample was simply careless construction. Something slapped together in a hurry. You couldn’t even add a new record in that form.

Final design is yours as I just pieced it together for a working model.

Added a previous accommodation field to the TripSectors table so at least there was something to connect them together. Because of the construction of this form, split the data into main and sub form. Have added the calculation for mileage but did nothing for time - that left for you.

All this is once again based upon using a left join to be able to get the proper data. There may still be a problem or two in the form as it was a mess to begin with but it appears most is OK. Have even added a new record for test.

Based upon the last few questions, it appears you are trying to create a system as you go along. This happens to many starting with Base. The proper way is to lay out all that is to be accomplished in a design and implement from there. Piecing things together will only result in a LOT of backtracking. You have already presented this problem using different forms and tables yet the answer is generally the same.

Sample ------ FromMycle2.odb

[Ratslinger]

It was nothing with the formulating - look at the table in the sample you posted. Edit it and look at the field types you assigned. Just like picking at random. That is the upsetting part. The form also had wrong field types. And as for Access, the removal of the connection would have caused a problem there as well.

As for the SQL, you need to use the syntax for the database in use. For the HSQLDB v1.8 embedded it is:

Datediff('hour', "B"."DepartTimePrior","ArriveTime") "TripTime"

Note the comma after 'hour'.

As another note, this is partially what I meant about planning. With a time calculation involved I would have gone with a timestamp field vs the separate date/time fields. This would make for more accurate calculations especially if crossing dates.

[Mycle]

Have searched and cannot find how I should format the output of Datediff(‘minute’, “B”.“DepartTimePrior”,“ArriveTime”) “TripTime” in “HH:MM” format??

Mile

[Ratslinger]

DATEDIFF( ‘mi’, “B”.“DepartTimePrior”, “ArriveTime” ) / 60 || ‘:’ || mod(DATEDIFF( ‘mi’, “B”.“DepartTimePrior”, “ArriveTime” ),60)