Need a query to return all records that aren't related.

[CafeenMan]

Sorry, but I don't even know how to ask the question so my web searches have
been unproductive.

I have three tables:

Facilities
Insurances
FacilityInsurance

The FacilityInsurance table contains the IDs of Facilities and the
Insurances each Facility accepts.

So I have two ListBoxes. One contains the Insurances accepted by the
selected Facility.

The other ListBox contains all Insurances that are left over.

My problem is how to fill the second list box. If I use the same query as I
used to select the accepted Insurances except using the "not equal to"
operator all I get back is insurances in the that other facilities accept
that aren't accepted by the selected facility. In other words, it's listing
insurances based on the FacilityInsurance table instead of the Insurance
table.

So this doesn't work:

"...WHERE FacilityID<>" & SelectedFacilityID

I'm sure there's a simple way to do this but I'm not well versed in SQL. I
usually use design view and then when it's working I switch to SQL view,
copy, paste and modify into my VB code.

Thanks.

 

[CafeenMan]

Here's the code (not all function calls are included but it's pretty obvious
what they do I think):

Private Function LoadFacilityInsurances() As Long
Dim RST As Recordset
Dim nFacilityID As Long

On Error GoTo errHandler
' Returns Error Code

lstAcceptedInsurances.Clear
With cmbFacilities
nFacilityID = .ItemData(.ListIndex)
End With

Set RST = DB.OpenRecordset(SQLFacilityInsurances(nFacilityID), dbOpenDynaset)
If RecordsetEmpty(RST) Then GoTo CloseRST

With RST
.MoveFirst
Do Until .EOF
lstAcceptedInsurances.AddItem !Insurance
lstAcceptedInsurances.ItemData(lstAcceptedInsurances.NewIndex) =
!InsuranceID
.MoveNext
Loop
End With

' Return 0

CloseRST:
RecordsetClose RST

Exit Function

errHandler:
Dim nErrReturn As Long
Dim nErr As Long

nErr = Err
nErrReturn = ErrorHandler(Error, nErr, vbNullString, Me.Name &
".LoadFacilityInsurances()")

If nErrReturn = vbRetry Then Resume

LoadFacilityInsurances = nErr

End Function

Private Property Get SQLFacilityInsurances(FacilityID As Long) As String

SQLFacilityInsurances = "SELECT InsuranceFacility.*, Insurances.Insurance
FROM " _
& "Insurances INNER JOIN InsuranceFacility ON " _
& "Insurances.InsuranceID = InsuranceFacility.InsuranceID WHERE " _
& " (((InsuranceFacility.InsuranceID)=" & FacilityID & "))"

End Property

 

[Ken Snell MVP]

Looks like you're building the row source query in code so let me show you a
single query that will do that.

First, we'll use a subquery to return the distinct Insurances from
FacilityInsurance that are not accepted by the Facilities ID value; I'm
using SelectedFacilityID as a generic ID value to show you how to build the
query, but you'd obviously use this in your code as a variable whose value
is concatenated into the :

SELECT DISTINCT InsuranceID
FROM FacilityInsurance
WHERE FacilityID <> SelectedFacilityID;

Then we'll use this as the data source for an IN clause in the primary query
to give you the desired data:

SELECT * FROM Insurances
WHERE InsuranceID IN
(SELECT DISTINCT InsuranceID
FROM FacilityInsurance
WHERE FacilityID <> SelectedFacilityID);


So in code:

strSQL = "SELECT * FROM Insurances " & _
"WHERE InsuranceID IN " & _
"(SELECT DISTINCT InsuranceID " & _
"FROM FacilityInsurance WHERE FacilityID <> " & _
SelectedFacilityID & ")"

 

[John Spencer]

You need a query that identifies all possible insurances for each facility

A nested query approach would be to create a query like this one.
SELECT Facilities.FacilityID, Insurances.InsuranceID
FROM Facilities, Insurances

And then use that query in a second query.
SELECT qAllPossible.*
FROM qAllPossible LEFT JOIN FacilityInsurance
ON qAllPossible.FacilityID = FacilityInsurance.FacilityID
AND qAllPossible.InsuranceID = FacilityInsurance.InsuranceID
WHERE FacilityInsurance.FacilityID is Null

If you need further help in building these queries, post back.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[CafeenMan]

Ken - Thanks for the reply. I haven't tried your approach yet but something
seems wrong.

The FacilityInsurance table lists only insurances accepted by various
facilities. There are hundreds of insurances and many of them may be in the
Insurance table but not yet used.

So your first query looks like it selects all insurances that are used by
any other facilities but not by the selected facility. That will leave a lot
of insurances out that haven't been used by any facility. Is that correct?

Ok, I just tried it and it does what I suspected. It only returns
insurances from the FacilityInsurance table but leaves out all insurances
that no facility uses.

The hard way is to make a list of all insurances and then loop through and
remove all the insurances associated with the facility. I either have to put
the insurances in an array or run a lot of queries over a slow server.

There's got to be a one or two-step approach using SQL.

 

[CafeenMan]

John,

Your query returned 18 rows. There are only three insurances in the table
so far. Only one is used by a facility. So I should be getting back two
rows.

 

[CafeenMan]

OK, if I'm following what you're telling me then this is query1 (The table is
actually named InsuranceFacility so I spelled it backwards in my earlier
posts):

SELECT Insurances.InsuranceID, Facilities.FacilityID FROM Facilities,
Insurances;

This is the final query:

SELECT query1.* FROM query1 LEFT JOIN InsuranceFacility ON query1.FacilityID
= InsuranceFacility.FacilityID AND query1.InsuranceID =
InsuranceFacility.InsuranceID WHERE InsuranceFacility.FacilityID is Null

 

[CafeenMan]

Another way would be to loop through the results of a query against the
InsuranceFacility and then build an SQL statement as follows:

dim s As String

s = "SELECT Insurance FROM Insurances WHERE "
RST.MoveFirst
DO Until RST.EOF
s = s & " (InsuranceID <>" & RST!InsuranceID & ")"
If Not RST.EOF Then
s = s & " AND "
RST.MoveNext
Loop

or something like that. Not elegant but it will build the query. I doubt I
will ever hit any limits that way but in 20 years maybe when there are 100
gazillion insurances entered in the db.

 

[CafeenMan]

This works. It's ugly and could potentially build a query miles long and
could eventually choke. Hopefully before that happens I find the real answer.

======================================

Private Function LoadFacilityInsurances() As Long
Dim RST As Recordset
Dim nFacilityID As Long
Dim s As String

On Error GoTo errHandler
' Returns Error Code

lstAcceptedInsurances.Clear
With cmbFacilities
nFacilityID = .ItemData(.ListIndex)
End With

Set RST = DB.OpenRecordset(SQLFacilityInsurances(nFacilityID), dbOpenDynaset)
If RecordsetEmpty(RST) Then GoTo CloseRST

s = "SELECT InsuranceID, Insurance From Insurances WHERE "

With RST
.MoveFirst
Do Until .EOF
s = s & "(InsuranceID<>" & !InsuranceID & ")"
lstAcceptedInsurances.AddItem !Insurance
lstAcceptedInsurances.ItemData(lstAcceptedInsurances.NewIndex) =
!InsuranceID
.MoveNext
If Not .EOF Then
s = s & " AND "
End If
Loop
End With

Set RST = DB.OpenRecordset(s, dbOpenDynaset)
If RecordsetEmpty(RST) Then GoTo CloseRST
With RST
.MoveFirst
Do Until .EOF
lstAllInsurances.AddItem !Insurance
lstAllInsurances.ItemData(lstAllInsurances.NewIndex) = !InsuranceID
.MoveNext
Loop
End With

' Return 0

CloseRST:
RecordsetClose RST

Exit Function

errHandler:
Dim nErrReturn As Long
Dim nErr As Long

nErr = Err
nErrReturn = ErrorHandler(Error, nErr, vbNullString, Me.Name &
".LoadFacilityInsurances()")

If nErrReturn = vbRetry Then Resume

LoadFacilityInsurances = nErr

GoTo CloseRST

End Function

 

[CafeenMan]

OK, That didn't work right. This is better:

============================

Private Function LoadFacilityInsurances() As Long
Dim RST As Recordset
Dim nFacilityID As Long
Dim s As String

On Error GoTo errHandler
' Returns Error Code

lstAcceptedInsurances.Clear
lstAllInsurances.Clear

With cmbFacilities
nFacilityID = .ItemData(.ListIndex)
End With

Set RST = DB.OpenRecordset(SQLFacilityInsurances(nFacilityID), dbOpenDynaset)

If Not RecordsetEmpty(RST) Then

s = "SELECT InsuranceID, Insurance From Insurances WHERE "

With RST
.MoveFirst
Do Until .EOF
s = s & "(InsuranceID<>" & !InsuranceID & ")"
lstAcceptedInsurances.AddItem !Insurance
lstAcceptedInsurances.ItemData(lstAcceptedInsurances.NewIndex) =
!InsuranceID
.MoveNext
If Not .EOF Then
s = s & " AND "
End If
Loop
End With
Else
s = "SELECT InsuranceID, Insurance FROM Insurances "
End If

s = s & "ORDER BY Insurance"

Set RST = DB.OpenRecordset(s, dbOpenDynaset)
If RecordsetEmpty(RST) Then GoTo CloseRST
With RST
.MoveFirst
Do Until .EOF
lstAllInsurances.AddItem !Insurance
lstAllInsurances.ItemData(lstAllInsurances.NewIndex) = !InsuranceID
.MoveNext
Loop
End With

' Return 0

CloseRST:
RecordsetClose RST

Exit Function

errHandler:
Dim nErrReturn As Long
Dim nErr As Long

nErr = Err
nErrReturn = ErrorHandler(Error, nErr, vbNullString, Me.Name &
".LoadFacilityInsurances()")

If nErrReturn = vbRetry Then Resume

LoadFacilityInsurances = nErr

GoTo CloseRST

End Function

 

[John Spencer]

So you just want a list of the insurance products that are not used by any
facility. I misunderstood your question - I thought you wanted to list each
facility and the insurances that each facility did not have.

Try this:

SELECT Insurances.*
FROM Insurances LEFT JOIN InsuranceFacility
ON Insurances.InsuranceID = InsuranceFacility.InsuranceID
WHERE InsuranceFacility.InsuranceID is Null

You could have the Unmatched query wizard build the above query.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Ken Snell MVP]

Ok, let's reverse the logic:


SELECT DISTINCT InsuranceID
FROM FacilityInsurance
WHERE FacilityID = SelectedFacilityID;

Then we'll use this as the data source for an IN clause in the primary query
to give you the desired data:

SELECT * FROM Insurances
WHERE NOT InsuranceID IN
(SELECT DISTINCT InsuranceID
FROM FacilityInsurance
WHERE FacilityID = SelectedFacilityID);