Query Dependant on a Query

[rdgskier8]

Hello, everyone.
I've been having a little difficulty lately trying to get my two-list/two-button setup (with the arrows left and right) to work. Here's my problem:

I have a many-to-many relationship between stations and the type of failures at that station (each station can have different failures, each failure can have different stations). On the form I'm working on (frmModify), I have a drop down box to choose the station. I have successfully displayed the list that the station currently HAS but I'm having trouble displaying the type of failures that the station DOESN'T have.

I've been trying to set up a query (qryModify) that is dependant upon the query that works showing what the station has (qryFailure). I was hoping there is some way that I can get qryModify to display the values in table "Failure" that don't appear in qryFailure, but I haven't been able to figure it out. I've also tried the approach where I don't rely on qryFailure at all - but all that has given me is the values in my intermediate table that aren't for the current station (ie. it displays some failure entries multiple times).

Any help would be much appreciated - I know this has been driving me crazy, so hopefully it provides you all with a challenge. =)

Thanks in advance,
Ryan

 

[PHV]

Take a look at LEFT JOIN.
To better understand what I suggest you analyze the SQL code of a query generated by the unmatched wizard.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

Thanks, PHV... LEFT JOIN is starting to work well but I still can't get it. Here's my code:

(This is all in the _Change sub for boxStation)

strSELECT = "SELECT Failure.IDfailure, Failure.FailureName "
strFROM = "FROM Failure LEFT JOIN qryFailure ON Failure.IDfailure = qryFailure.IDfailure "
strWHERE = "WHERE qryFailure.IDstation<>" & boxStation & " "
strORDERBY = "ORDER BY Failure.FailureName ASC;"

strSQL = strSELECT & strFROM & strWHERE & strORDERBY

BuildSQLString = True

CurrentDb.QueryDefs("qryModify").SQL = strSQL

Form_frmModify.Refresh


With that code, the list comes up as blank - when I take the WHERE statement out, the list displays ALL the failures.

Any suggestions? Thanks again for your help.

 

[lespaul]

what is the field type of IDstation? If it's a string you'll need quotes around the boxStation variable:

strWHERE = "WHERE qryFailure.IDstation<>'" & boxStation & "' "

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[PHV]

Playing with qryFailure.IDstation in the WHERE clause defeats the purpose of the LEFT JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

Ahh yeah, I have boxStation as a number.

So..if WHERE defeats the purpose of LEFT JOIN, then what is it used for? Seems like displaying the Failure table would show the same thing. Isn't there a way I can omit the failures of the current selected station?

Thanks again,
Ryan

 

[PHV]

And this ?
strWHERE = "WHERE Nz(qryFailure.IDstation)<>" & boxStation & " "

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[rdgskier8]

Perfect!! Thank you so very much, PHV.

I didn't even know that Nz function existed. =)