qryTripSectors returns some fields from the previous record for use on form “Trips”. Each record in the table “TripSectors” has a TripLogID
I only want to return fields from the previous record WHERE TripLogID’s are the same, I cannot fathom the correct use of the WHERE function in the query SQL.
Modified DB:
BaseVanTripsVer7.5.odb
Cheers
Mike
Pic of the form “Trips” With depart details from the previous Trip (TripLog0)
Hello,
Note: Deleted original answer.
Edit 2019-01-16:
The problem is in the ON section of the join. Only ‘PrevAccom’ are considered. With the multiple use of AccomodationID, the ‘TripLogID’ needs to be used also for uniqueness:
ON ("A"."PrevAccom" = "B"."AccommodationIDPrior") AND ("A"."TripLogID" = "B"."TripLogIDPrior")
and “TripLogIDPrior” needs to be added to the selection for “B”
As a separate note, there is what appears to be an incorrect date in Trip Log 0 - 01/03/17 depart date seems to have an incorrect year causing your calculations to be way off.
Tried this @Ratslinger but if the same depart place is entered for a new/different trip then the record for that trip is returned not the new trip.
Have now looked at the result multiple times and can’t see any problem. Need specific information on what you see as the problem - such as record(s) being looked at & values. Something specific because I see no problem.
Sorry, looking at wrong form. Still would like details of where the problem is.
Nope - still see no problem.
If you open the form “Trips” and using the bottom scrollbar step to TripLog 1 and then using the scrollbar to step through the Trip Sectors of TripLog1 you will see in the Depart Details section of the form that the depart details TripSector0, date, time, mileage etc from the depart from home record for TripLog record 0 Not TripLog Record 1
TripSector record 6 would be the previous record to the TripSector Record 7 shown in the Destination Details section.
Added a pic to the post!
Cheers
Mike
Thanks again @Ratslinger working beautifully, modified DB attached to original question.
I have a question re a list box on the form Trips. Should I start another thread??
Cheers,
Mike
@mycle While it may seem logical to continue here, the way the forum is set up, your question would become buried in another and others would not benefit. Therefore, it is typically best to ask as a new question for others who may also be looking for a similar answer.