By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,160 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

NOT IN(SELECT

P: n/a
This one droves me completely mad. I did not succeed to exploit the
track given to me by Bob.

I have :
three tables : Clubs, Persons and ClubsPersons that join the two first
in a many to many relationship.
Club has two (2) fields : ClubId (Autonumber) and ClubName (Text).
Peoples has two(2) fields : (PersonId (Autonumber) and PersonName
(Text)
ClubsPersons has two (2) fields : ClubId (LongInteger) and PersonId
(LongInteger).

A form (frmForm) with one control. An associated subform
(frmClubsMembers) for displaying the persons members of the above
club. The control on the subform is a ComboBox (continuous form).

Queries : qryClubs which feed the frmClub
qryClubsPersons for the subform.
a query in the rowsorce of the combobox (My problem is with this
one...)

What I want to see :
When I click on the ComboBox, I want to see the names of the persons
who are not yet members of the displayed Club. I tried various queries
in the rowsource of the combo, without success.

The closer I came is with this query in the rowsource.:
SELECT qryPersonnes.PersonId, qryPersonnes.PersonNom FROM qryPersonnes
WHERE (((qryPersonnes.PersonId) Not In (SELECT
[tClubsPersonnes].[PersonId] FROM tClubsPersonnes WHERE
[tClubsPersonnes].[ClubId] = [Forms]![FrmClub].[ClubId]))) ORDER BY
qryPersonnes.PersonNom;

But it does not display the members. Other queries display the members
but do not permit to update the tClubspersons. Others display members
but update the wrong table.

I have worked days on that. Please help. Many thanks in advance.
Nov 12 '05 #1
Share this Question
Share on Google+
14 Replies


P: n/a
DFS
Jos,

Here's something to get you started:

1) Create a query Q_ALL_MEMBERSHIPS_ALL

SELECT C.ClubID, P.PersonID
FROM T_CLUBS C, T_PERSONS P;
2) Create another query Q_NO_MEMBERSHIPS

SELECT Q.*
FROM Q_ALL_MEMBERSHIPS Q LEFT JOIN T_CLUBS_PERSONS CP
ON (Q.PersonID = CP.PersonID) AND (Q.ClubID = CP.ClubID)
WHERE CP.ClubID IS NULL;

Then check your combobox against Q_NO_MEMBERSHIPS

"Jos?" <f5**@tele2.fr> wrote in message
news:73**************************@posting.google.c om...
This one droves me completely mad. I did not succeed to exploit the
track given to me by Bob.

I have :
three tables : Clubs, Persons and ClubsPersons that join the two first
in a many to many relationship.
Club has two (2) fields : ClubId (Autonumber) and ClubName (Text).
Peoples has two(2) fields : (PersonId (Autonumber) and PersonName
(Text)
ClubsPersons has two (2) fields : ClubId (LongInteger) and PersonId
(LongInteger).

A form (frmForm) with one control. An associated subform
(frmClubsMembers) for displaying the persons members of the above
club. The control on the subform is a ComboBox (continuous form).

Queries : qryClubs which feed the frmClub
qryClubsPersons for the subform.
a query in the rowsorce of the combobox (My problem is with this
one...)

What I want to see :
When I click on the ComboBox, I want to see the names of the persons
who are not yet members of the displayed Club. I tried various queries
in the rowsource of the combo, without success.

The closer I came is with this query in the rowsource.:
SELECT qryPersonnes.PersonId, qryPersonnes.PersonNom FROM qryPersonnes
WHERE (((qryPersonnes.PersonId) Not In (SELECT
[tClubsPersonnes].[PersonId] FROM tClubsPersonnes WHERE
[tClubsPersonnes].[ClubId] = [Forms]![FrmClub].[ClubId]))) ORDER BY
qryPersonnes.PersonNom;

But it does not display the members. Other queries display the members
but do not permit to update the tClubspersons. Others display members
but update the wrong table.

I have worked days on that. Please help. Many thanks in advance.

Nov 12 '05 #2

P: n/a


Hi DFS

I think the first query must use tClubsPersons. Anyway the second query
does not produce what I expect.

My test includes only 7 persons and 4 clubs. Am I so hopeless ?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
Jos? wrote:
This one droves me completely mad. I did not succeed to exploit the
track given to me by Bob.


The excellent response, provided by DFS, can be created by clicking on
Queries/New and selectiong the FindUnmatched Query wizard.

Nov 12 '05 #4

P: n/a
DFS

"F5JD" <JD@devdex> wrote in message
news:3f*********************@news.frii.net...
Hi DFS

I think the first query must use tClubsPersons.
No. It's a Cartesian query, which produces all combinations of people and
clubs. It will assign each person to each club, ie, a list of 7 people and
4 clubs will produce 28 records.

Anyway the second query does not produce what I expect.
You said "I want to see the names of the persons who are not yet members of
the displayed Club"

If you say "SELECT * FROM Q_NO_MEMBERSHIPS WHERE ClubID = 5, you will get a
list of persons who are not yet a member of Club 5.

It should be working for you. It works perfectly for me.

My test includes only 7 persons and 4 clubs. Am I so hopeless ?
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #5

P: n/a

Many thanks to DFS and Salad for their answers and comments. I cannot
make your suggestions work. I completed them with a Requery in the
subform AfterUpdate, tried to change the order of data. No success.

Now I know that I am hopeless. If, by any chance, you could send me your
example.

Sorry, I Give up.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #6

P: n/a
DFS
"F5JD" <JD@devdex> wrote in message
news:3f*********************@news.frii.net...

Many thanks to DFS and Salad for their answers and comments. I cannot
make your suggestions work. I completed them with a Requery in the
subform AfterUpdate, tried to change the order of data. No success.

Now I know that I am hopeless. If, by any chance, you could send me your
example.

Sorry, I Give up.


Don't ever give up. Post your email and I'll send you my sample that
works - but recognize it's not a form-based example, just three tables and
two queries.
Nov 12 '05 #7

P: n/a
Jos? wrote:
This one droves me completely mad. I did not succeed to exploit the
track given to me by Bob.

I have :
three tables : Clubs, Persons and ClubsPersons that join the two first
in a many to many relationship.
Club has two (2) fields : ClubId (Autonumber) and ClubName (Text).
Peoples has two(2) fields : (PersonId (Autonumber) and PersonName
(Text)
ClubsPersons has two (2) fields : ClubId (LongInteger) and PersonId
(LongInteger).

A form (frmForm) with one control. An associated subform
(frmClubsMembers) for displaying the persons members of the above
club. The control on the subform is a ComboBox (continuous form).

Queries : qryClubs which feed the frmClub
qryClubsPersons for the subform.
a query in the rowsorce of the combobox (My problem is with this
one...)

What I want to see :
When I click on the ComboBox, I want to see the names of the persons
who are not yet members of the displayed Club. I tried various queries
in the rowsource of the combo, without success.

The closer I came is with this query in the rowsource.:
SELECT qryPersonnes.PersonId, qryPersonnes.PersonNom FROM qryPersonnes
WHERE (((qryPersonnes.PersonId) Not In (SELECT
[tClubsPersonnes].[PersonId] FROM tClubsPersonnes WHERE
[tClubsPersonnes].[ClubId] = [Forms]![FrmClub].[ClubId]))) ORDER BY
qryPersonnes.PersonNom;

But it does not display the members. Other queries display the members
but do not permit to update the tClubspersons. Others display members
but update the wrong table.

I have worked days on that. Please help. Many thanks in advance.


If I understand you correctly, you have a form (Main) and 2 subforms (1 to
display people in a club, the other to display those not in the club).
This is based on the value of the club selected by combo box. This means
the main form is not bound to a table. The subforms are.bound to your
queries (tables)

One thing. I like to have a reference to a row. In ClubsPersons, you
have two (2) fields : ClubId (LongInteger) and PersonId (LongInteger) I
would add a thrid column, an auto number (primary key) for the table.
It's not necessary. Its a preference of mine. But later on I may add
more tables and having such a key to insert makes things easy..

Onward. I changed a couple of field names...ex PersonName instead of
PersonNom. But this is what my query builder code is.

The SQL for the rowsource of the subform for those that are IN the club is

SELECT Person.PersonID, Person.PersonName
FROM Person INNER JOIN ClubsPersons ON Person.PersonID =
ClubsPersons.PersonID
WHERE (((ClubsPersons.ClubID)=[Forms]![MainForm]![ComboClubs]))
ORDER BY Person.PersonName;

The SQL for the rowsource of the subform for those that are NOT IN the
club is
SELECT Person.PersonID, Person.PersonName
FROM Person
WHERE Person.PersonID Not In (Select PersonID From ClubsPersons Where
ClubID = [Forms]![MainForm]![ComboClubs])
ORDER BY Person.PersonName;

Let's say your subforms are calle Sub1 and Sub2. The combo's form is
called MainForm. In the Combo's AfterUpdate event enter something like
Forms!MainForm!Sub1.Form.Requery
Forms!MainForm!Sub2.Form.Requery
so that the values refresh after you change the club value in the main
form.

This works for me. Change the field, form, and table names where
appropriate. I kept the table names the same.

Do you see how the row source for the sub forms get the value of the Combo
in the main form for filtering?

Nov 12 '05 #8

P: n/a

Salad,
Many, many thanks for coming back. I said that I surrender, but that is
not true. I continue to fetch a solution. I really want to make this
form work.

Your proposal now seems closer to what Bob Quintal proposed before.
Sincerely, I do not understand how the algorithm of DFS coud work.

I must clarify a point. I have one form and only one subform. It could
have more. I say that for simplification. The subform (datasheet) has
only one control. It could have more. This control is a combo box. If
the club displayed has 3 members, we have 3 lines with name plus the
line with the asterisk.

What I want to see : when you click on the combobox to add a member or
replace, I want the drop down list show only names of persons who are
not members of that club.

Suppose we have a total of 7 persons. John, Laureen, Luc, Marc, Peter,
Sonia, Vanda.

4 clubs : Chess, Foot-Ball, Ski, Parachuting

Luc, Marc and Vanda are members of the foot-ball club.

When the form displays foot-ball, the subform displays (vertically) Luc,
Marc, Vanda, *.

If I click one of the combobox, I want to the dropdown list show only
John, Laureen, Peter, Sonia. Now, clicking one of these must update
ClubsMembers, the table joining Clubs and perons.

Maybe it cannot be done with Access. I have many books here, never found
an example, even close to that.

Sorry for this long post. English is not my first language.

I'll watch your answer. My email is f5**@tele2.fr

Regards

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #9

P: n/a
DFS

"F5JD" <JD@devdex> wrote in message
news:3f*********************@news.frii.net...

Salad,
Many, many thanks for coming back. I said that I surrender, but that is
not true. I continue to fetch a solution. I really want to make this
form work.

Your proposal now seems closer to what Bob Quintal proposed before.
Sincerely, I do not understand how the algorithm of DFS coud work.

I must clarify a point. I have one form and only one subform. It could
have more. I say that for simplification. The subform (datasheet) has
only one control. It could have more. This control is a combo box. If
the club displayed has 3 members, we have 3 lines with name plus the
line with the asterisk.

What I want to see : when you click on the combobox to add a member or
replace, I want the drop down list show only names of persons who are
not members of that club.

So the subform shows a list of assigned members, and the combobox in the
subform shows non-members?

That's more clear than what you were saying before, but it won't work.
You're asking the combobox to be bound to a member's name, but have the
combobox' rowsource set to show only non-members. The result will show a
set of 3 blanks where the member name would normally be.

To get around this, you'll need to create, inside the subform, a 2nd
combobox used to choose the non-members.

I think you're making it too hard on yourself. I recommend you just show a
list of all Members in the drop-down.

Suppose we have a total of 7 persons. John, Laureen, Luc, Marc, Peter,
Sonia, Vanda.

4 clubs : Chess, Foot-Ball, Ski, Parachuting

Luc, Marc and Vanda are members of the foot-ball club.

When the form displays foot-ball, the subform displays (vertically) Luc,
Marc, Vanda, *.

If I click one of the combobox, I want to the dropdown list show only
John, Laureen, Peter, Sonia. Now, clicking one of these must update
ClubsMembers, the table joining Clubs and perons.

Maybe it cannot be done with Access. I have many books here, never found
an example, even close to that.

Sorry for this long post. English is not my first language.

I'll watch your answer. My email is f5**@tele2.fr

Regards

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Nov 12 '05 #10

P: n/a


DFS, Salad

Many thanks for the time spent with me. Now I give up as the experts
tell me it cannot be done.

Regards

Josť

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #11

P: n/a
F5JD <JD@devdex> wrote in
news:3f*********************@news.frii.net:


DFS, Salad

Many thanks for the time spent with me. Now I give up as the
experts tell me it cannot be done.

Regards

Josť


Well the expert is wrong.

The form uses recordsource clubs. Has textbox club, bound to
clubs.club.

The subform uses the recordsource persons, with field person.

The field club is used as child field and parent field
The combobox is unbound, it has no recordsource
The combobox rowsource is:
SELECT persons.person FROM persons
WHERE (((persons.person) Not In
(select person from clubmembers
where club = forms!form1.club)));

Now you need to create some combobox events.

Private Sub Combo3_AfterUpdate()
'makes a new entry in the clubmembers table.
DoCmd.RunSQL "insert into clubmembers(club,person) values ('" _
& Me.club.Value & "','" & Me.Combo3.Value & "');"
Me.Child1.Form.Requery
End Sub

Private Sub Combo3_Enter()
'gets the current data
Me.Combo3.Requery
End Sub

I can email an example, do you prefer access2000 or '97.
Bob Q

Nov 12 '05 #12

P: n/a
F5JD wrote:
I must clarify a point. I have one form and only one subform. It could
have more. I say that for simplification. The subform (datasheet) has
only one control. It could have more. This control is a combo box. If
the club displayed has 3 members, we have 3 lines with name plus the
line with the asterisk.

What I want to see : when you click on the combobox to add a member or
replace, I want the drop down list show only names of persons who are
not members of that club.

Suppose we have a total of 7 persons. John, Laureen, Luc, Marc, Peter,
Sonia, Vanda.

4 clubs : Chess, Foot-Ball, Ski, Parachuting

Luc, Marc and Vanda are members of the foot-ball club.

When the form displays foot-ball, the subform displays (vertically) Luc,
Marc, Vanda, *.

If I click one of the combobox, I want to the dropdown list show only
John, Laureen, Peter, Sonia. Now, clicking one of these must update
ClubsMembers, the table joining Clubs and perons.

Maybe it cannot be done with Access. I have many books here, never found
an example, even close to that.

Sorry for this long post. English is not my first language.

I'll watch your answer. My email is f5**@tele2.fr

Regards

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


I WOULD NOT do your way. Why? Because your combo box rowsource would cause
problems.

Let's say your subform has a combo box. The combo box displays names that
ARE NOT IN THE CLUB. Now how would your names that are IN the club be
displayed in existing record?

Next, you are making this more difficult on yourself then necessary. There
are many ways to approach this problem. Here is one concept.
In your main form have 2 combos and 1 subform.

The first combo would select a club. The second combo (actualy a listbox
would be better) would contain a list of all people NOT IN the club. The
subform would contain a list of records for that club.

When a club is selected, it would requery the subform and combo (or listbox)
for persons not in that club. The subform WOULD NOT contain a combo. It
would display the person's name instead.

This is the recordsource for your subform
SELECT Person.PersonID, Person.PersonName
FROM Person
WHERE Person.PersonID Not In (Select PersonID From ClubsPersons Where
ClubID = [Forms]![MainForm]![ComboClubs])
ORDER BY Person.PersonName;

This is the rowsource for your combo (or listbox)
SELECT Person.PersonID, Person.PersonName
FROM Person
WHERE Person.PersonID Not In (Select PersonID From ClubsPersons Where
ClubID = [Forms]![MainForm]![ComboClubs])
ORDER BY Person.PersonName;

If I had a listbox, I would probably have an event on the OnDblClick to add
it. (I us DAO) Ex:
Sub ListBox_DblClick
If msgbox("Do you want to add this person to the list?",vbYesNo +
vbQuestion,"Confirm Add") = vbYes then
Dim rst as recordset
set rst = currentdb.openrecordset("ClubsPersons",dbopendynas et)
rst.AddNew
rst!ClubID = Me.ComboClubs
rst!PersonID = Me.ListBoxNotInClub
rst.Update
rst.close
set rst = Nothing
Forms!MainForm!SubForm.Form.Requery 'refresh your list
Me.ListBox.requery
endif
End Sub

You want to requery the combo/listbox when you have added to remove the name
from the list. I'm not sure if you can requery while it has the focus. You
may have to set focus to the club combo, do the requery, then set focus
back.

The way you have it set up right now, it sounds like the names are displayed
in a combo for those not in the club so those that are in the club wouldn't
display the name.

Good luck.

Nov 12 '05 #13

P: n/a
DFS

"Bob Quintal" <bq******@generation.net> wrote in message
news:b9******************************@news.teranew s.com...
F5JD <JD@devdex> wrote in
news:3f*********************@news.frii.net:


DFS, Salad

Many thanks for the time spent with me. Now I give up as the
experts tell me it cannot be done.

Regards

Josť

Well the expert is wrong.

I looked at it again, and maybe it can be done, but only if the combobox in
the subform doesn't use the PersonID as part of it's rowsource. It's more
trouble than it's worth.

However, your solution won't do it either. You say the subform recordsource
is Persons; this is wrong because it will show all Persons. It must be
sourced from tClubsPersons or a query based on tClubsPersons, so it will
show only members belonging to the club.



The form uses recordsource clubs. Has textbox club, bound to
clubs.club.

The subform uses the recordsource persons, with field person.

The field club is used as child field and parent field
The combobox is unbound, it has no recordsource
The combobox rowsource is:
SELECT persons.person FROM persons
WHERE (((persons.person) Not In
(select person from clubmembers
where club = forms!form1.club)));

Now you need to create some combobox events.

Private Sub Combo3_AfterUpdate()
'makes a new entry in the clubmembers table.
DoCmd.RunSQL "insert into clubmembers(club,person) values ('" _
& Me.club.Value & "','" & Me.Combo3.Value & "');"
Me.Child1.Form.Requery
End Sub

Private Sub Combo3_Enter()
'gets the current data
Me.Combo3.Requery
End Sub

I can email an example, do you prefer access2000 or '97.
Bob Q

Nov 12 '05 #14

P: n/a
"DFS" <no****@nospam.com> wrote in
news:vv************@corp.supernews.com:

Well the expert is wrong.

I looked at it again, and maybe it can be done, but only if
the combobox in the subform doesn't use the PersonID as part
of it's rowsource. It's more trouble than it's worth.


It surely can be done. the query for the rowsource of the unbound
combobox and the code snippets come from the form I built to
check and test it.

The combobox should be on the main form not the subform but will
work in either position. And not bound. In my opinion a listbox
would be a better control than the combobox.

However, your solution won't do it either. You say the
subform recordsource is Persons; this is wrong because it will
show all Persons. It must be sourced from tClubsPersons or a
query based on tClubsPersons, so it will show only members
belonging to the club.
My error in transcribing from the .mdb to email.
The subform must be bound to clubspersons. There is no need to
query as the link mechanism between the main form and the subform
automatically handles the filtering.

Bob Q.


The form uses recordsource clubs. Has textbox club, bound to
clubs.club.

The subform uses the recordsource persons, with field person.

The field club is used as child field and parent field
The combobox is unbound, it has no recordsource
The combobox rowsource is:
SELECT persons.person FROM persons
WHERE (((persons.person) Not In
(select person from clubmembers
where club = forms!form1.club)));

Now you need to create some combobox events.

Private Sub Combo3_AfterUpdate()
'makes a new entry in the clubmembers table.
DoCmd.RunSQL "insert into clubmembers(club,person) values ('"
_
& Me.club.Value & "','" & Me.Combo3.Value & "');"
Me.Child1.Form.Requery
End Sub

Private Sub Combo3_Enter()
'gets the current data
Me.Combo3.Requery
End Sub

I can email an example, do you prefer access2000 or '97.
Bob Q



Nov 12 '05 #15

This discussion thread is closed

Replies have been disabled for this discussion.