How to display name field from second table in a query

[Mycle]

Edited

Hope I can explain sufficiently.

Three tables:

tblTripLog, tblTrips, tblAccom - Description

tblTripLog - fields TripLogID, Description

tblTrips - fields TripID, TripLogID, PrevAccomID, ArriveAccomID and more fields date, odometer, time, cost etc

tblAccom - fields AccomID, AccomName, plus fields for Address etc

On a form frmTripDetails (data content tblTripLog) with a subform (data content qryTrips) to display trips associated with TripLog records - linkedfields Master = TripLogID Subform = TriplogID

The PrevAccomID and ArriveAccomID form controls display the AccomID number.

What I would like is to be able to use form controls to show tblAccom.PrevAccom Name and tblAccom.ArriveAccomName to appear on the subform.

I have based the subform on the Query qryTrips. I use the query for calculations such as the cost of the accommodation “ArriveAccomRate” * DATEDIFF( ‘dd’, “ArriveAccomDate”, “DepartDate” ) etc.

How can I use a query field to display both Name fields (Arrive and Depart) from tblAccom. qryTrips is based on tblTrips, by adding tblAccom with just one join tblTrips.ArriveAccomID to tblAccomID and adding AccomName from tblAccom to the query under the alias Arrive, it works.

How the heck can I produce records with both Arrrive and Depart AccomName details not just the ID

Longwinded description sorry, but I wanted to be sure I had explained the situation correctly.

Cheers,

Mike

[Ratslinger]

@Mycle Trying to figure out what you are talking about makes my head spin. You list tables, then populating something then all works fine.

All the sudden there is a BUT. Now another explanation.

Please simplify. Make a point. Provide a sample. In this question do not know where to look first. Not really sure what is on what form and what is supposed to be the problem.

Edit: A closer look show many problems with the question like suddenly you are discussing subform (data content qryTrips) and would like is for tblAccom.Name and tblAccom.Name to appear on the subform same field twice!

Very hard to decipher.

[Ratslinger]

Hello,

OK, after another 30 minutes and tossing out 90% of the question, I believe you are simply looking to attach in you query the ‘Depart’ and ‘Arrive’ name of the accom.name table.

If this is the case, then it is a left join and just give the name an alias. You need two joins here - one for each ID:

Based this mostly on previously answered questions.

If this is NOT what you are looking for, then I am really lost. Then need more help.

If this is what you ARE looking for, then please realize your question is way overstated and confusing.

Edit 2018-12-26:

OK maybe this is a simpler solution for you using the graphical query interface. Add a second tblAccom to the query. Drag the ID to the prevAccomID of tblTrips. Now select that name & give alias:

[Ratslinger]

@Mycle The situation presented is no different than the solution given in my answer. You can take your existing SQL and just add a left join for the ‘PrevAccomID’. If you are unsure of this, please post the SQL statement you have. To view, right click the query name and select Edit in SQL view...’ and this presents similar screen to mine above.

[Mycle]

Well you have come through again Ratslinger, thank you Two Accom tables and two links, works great . I did not know two occurences of the same table could be used in this way. I will also work of my forum etiquette, sorry.

Cheers, and thanks again,

Mike

[Ratslinger]

@Mycle Just to be clear, these duplicates are really aliases. You can have more than two also. The SQL in my original answer also used an alias for each - B & C.

[Mycle]

Thanks for the heads up

Mike

[Mycle]

Ok the question was a bit of a mess, and yes the tables are not the same as previous @Ratslinger. Maybe a visual will help.

This works Arrive field = the name of the Arrive Accommodation

How do I show both Arrive and Depart Accommodation (PrevAccomID)

Thanks for persevering

Mike

[Ratslinger]

@Mycle Have mentioned this before:
Please do not use an answer for a response - only to answer original question. Instead use add a comment or edit original question noting additional information…