sql statement difficulties

[antonyx]

i have 3 tables..

tblJob
tblPerson
tblPlace

ids are linked in the following way

http://www.londonheathrowcars.com/3tablelayout.jpg

once the booker id and passenger id fields are populated on the job entry form, i want my 'JobFrom' and 'JobTo' drop downs to be populated with places previously visited by either the booker or customer.

i will set the rowsource of the drop down OnEnter but i cant quite get my head around the sql..

i imagine it will resemble something like this..

SELECT [tblPlace].[PlaceID], [tblPlace].[Place] FROM [tblPlace]

where that place is used in a job record....

WHERE [tblJob].[fkBookerID] =" & Me.txtbooker & " OR [tblJob].[fkPassengerID] =" & Me.txtpassenger & "

any ideas?

 

[Moniker]

If Booker and Passenger are text, change your WHERE statement to this:

WHERE [tblJob].[fkBookerID] ='" & Me.txtbooker & "' OR [tblJob].[fkPassengerID] ='" & Me.txtpassenger & "'"

If they are numeric values, change your WHERE statement to this:

WHERE [tblJob].[fkBookerID] =" & Me.txtbooker & " OR [tblJob].[fkPassengerID] =" & Me.txtpassenger

In the numeric one, all I did was remove the ending & " from the statement.

 

[antonyx]

yes.. they are all numeric by the way..

what i need help with is the middle part of the above code i posted..

it has to

select tblplace.placeid from tblplace where
(tblplace.place id = tbljob.fkjobfrom or tbljob.fkjobto
and tbljob.fkbookerid = txtbooker or tbljob.fkpassengerid = txtpassenger)

does this make it clearer?

 

[Moniker]

There's an error in your SQL with the AND statement. Namely, this won't work:

... or tbljob.fkjobto and tbljob.fkbookerid = txtbooker ...

You have to set fkjobto to txtbooker as well as fkbookerid. Change it to something like what is below.

SELECT 
    tblplace.placeid 
FROM
    tblplace 
WHERE
    (tblplace.place id = tbljob.fkjobfrom)
    OR (tbljob.fkjobto = txtbooker 
       AND tbljob.fkbookerid = txtbooker)
    OR (tbljob.fkpassengerid = txtpassenger)
;

 

[antonyx]

i could just copy and paste that but i dont really understand it..

here are some records for example

persontable
1,antony
2,jimmy
3,james
4,lisa
5,jenny

placetable
1,heathrow
2,sheraton
3,hilton
4,buckingham palace
5,docklands

and here are previous job records

jobtable
j1,1,1,1,4 (ant booked for ant.. from heathrow to buckingham palace)
j2,2,3,1,5 (jimmy booked for james.. from heathrow to docklands)
j3,2,4,1,2 (jimmy booked for lisa.. from heathrow to sheraton)

now if i am on my job entry form.. and i choose jimmy as the booker and ant as the passenger..
when i enter the 'jobfrom' dropdown it should display

heathrow, buckingham palace, docklands and sheraton.

if i enter ant as the booker and lisa as the passenger it should display

heathrow, buckingham palace, sheraton.

basically any pickup or destination associated in the past with the booker or the passenger within a job.

what i dont understand is the bold line below

SELECT 
    tblplace.placeid 
FROM
    tblplace 
WHERE
    (tblplace.place id = tbljob.fkjobfrom)
    OR [b](tbljob.fkjobto = txtbooker[/b] 
       AND tbljob.fkbookerid = txtbooker)
    OR (tbljob.fkpassengerid = txtpassenger)
;

to me.. that seems as if you are comparing the jobto field, and looking for an equal value in the txtbooker textbox on the form..

this does not make sense to me..


are you saying that using this = in between does not neccesserilly mean both values must be equal?