Select query issue

[Tekime]

Been hunting for the answer and I thought I had the solution at one point but I could not get any output. Any help would be highly appreciated.

I have three tables:

tbl_Users
tbl_Rights
tbl_Users_Rights

I need to select rightId and right_name from tbl_Rights where there are no entries in Users_Rights with a specified userId.

As in, userId 123 has a record in Users_Rights for rightId 1 and 2, but not three. I need the query to return rightId 3 and its associated right_name when I specify userId 123.

I can post more details if needed, thanks a bunch.

 

[Dugantrain]

-Outer Join tbl_Rights to tbl_Users_Rights (This is the most important part, make sure that it's Outer Joined rather than Inner Joined).
-Pull in the two fields from tbl_Rights that you'd like to see
-Pull in the join field from tbl_Users_Rights
-In the criteria of the join field from tbl_Users_Rights, write "Is Null". Unclick "Show" so that you don't actually see this field (they'll be all Null)
-Run it and you should get a Recordset of all Records from tbl_Rights where there is no matching Record in tbl_Users_Rights.

 

[Tekime]

Hi DuganTrain, thanks for the reply.

This only returns records from tbl_Rights where there is no matching record in tbl_Users_Rights though. I need all records from tbl_Rights where there is no matching record in tbl_Users_Rights with a specified userId.

The Users_Rights table stores pairs of userId's and rightId's to keep track of the rights each user has been granted.

The query I'm trying to build should return all rights which the user has not yet been granted.

I'm not sure if this is even possible with an Access query.

If I misunderstood your solution please LMK, thanks.

 

[Dugantrain]

Oh, I see. OK, I have done this sort of thing before. Someone else may have a more efficient way, but this is how I do it:

Build a query off of the tbl_Users_Rights table. Add a field and call it something like UR_UID. Define it as:

UR_UID:  [User_ID] & [Rights_ID]

This will concatenate every record's User_ID and Rights_ID

Now, build a query off of the two other tables. Don't join the tables together; just pull in the User_ID and Rights_ID and a final field just like the previous query ([User_id] & [Rights_ID]). The Result Set will return a Cross Product of all possible combinations of User_ID's and Rights_ID's. Now, build a query off of these two new queries and Outer Join the Cross-Product query to the first query on the UR_UID. Join the original Users and Rights tables on their respective ID's to the Cross-Product table so that you can pull in the fields from the Source table that will describe these ID's. At this point, you should have 2 tables and 2 queries in your Query Design. From the other query, pull in UR_UID and, in its criteria, write Is Null. This will give you all Users and all Rights that are not currently assigned to them.

Like I said, I don't know if it's the most efficient way of doing things, but it will get you what you're looking for.

 

[Tekime]

I found a way to get the results I needed by performing a simpler query and then filtering the results. Since I need to assign the results to a listbox I also had to use ADODB and the GetString method to extrapolate the proper string format to apply to a rowsource.

It's probably not the most efficient method either, and I might give yours a go to compare them to one another.

Here is what I coded which is working so far (Me!cboUserId is the currently selected userId):


Dim cnn As ADODB.Connection
Dim rs As New ADODB.Recordset

Dim sqlRights As String

sqlRights = "SELECT tbl_Rights.rightId, tbl_Rights.right_name, tbl_Users_Rights.userId " & _
"FROM tbl_Rights LEFT JOIN tbl_Users_Rights ON tbl_Rights.rightId = tbl_Users_Rights.rightId;"

Set cnn = CurrentProject.Connection
rs.Open sqlRights, cnn, adOpenDynamic

If (Not rs.EOF) Then

Dim rsFilter As String
Dim strRights As String

rsFilter = "userId <> " & Me!cboUserId & " Or userId = Null"
rs.Filter = rsFilter
strRights = rs.GetString(adClipString, 10, ",", ";")

End If

Me![lstRights].RowSourceType = "Value list"
Me![lstRights].RowSource = strRights


I'm a little confused by what you posted as far as defining the UR_UID field in a query and whatnot... but I haven't tackled it yet either!

Thanks again for your input.