Ask Your Question
0

need help with Query SQL WHERE syntax

asked 2019-01-16 03:24:15 +0200

Mycle gravatar image

updated 2019-01-17 12:09:57 +0200

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.

C:\fakepath\BaseVanTripsVer7.5.odb

Modified DB:

C:\fakepath\BaseVanTripsVer7.5.odb Cheers

Mike

image description

Pic of the form "Trips" With depart details from the previous Trip (TripLog0)

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-01-16 03:59:00 +0200

Ratslinger gravatar image

updated 2019-01-16 12:32:42 +0200

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.

edit flag offensive delete link more

Comments

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.

Mycle gravatar imageMycle ( 2019-01-16 05:25:10 +0200 )edit

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.

Ratslinger gravatar imageRatslinger ( 2019-01-16 05:49:02 +0200 )edit

Sorry, looking at wrong form. Still would like details of where the problem is.

Ratslinger gravatar imageRatslinger ( 2019-01-16 06:24:17 +0200 )edit

Nope - still see no problem.

Ratslinger gravatar imageRatslinger ( 2019-01-16 06:30:55 +0200 )edit

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

Mycle gravatar imageMycle ( 2019-01-16 08:52:46 +0200 )edit

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 gravatar imageMycle ( 2019-01-17 09:52:19 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2019-01-17 19:09:45 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-01-16 03:24:15 +0200

Seen: 49 times

Last updated: Jan 17