Many-to-Many - populate listboxes

[Karja]

Hello there,

I am building an Access application to store information of Investments Funds and their Restrictions. There is more than one Fund and more than one Restriction. Each Fund can have multiple restrictions and each restriction can be attached to more than one Fund. So I created the following tables:

G_Funds (Meta information about the Funds)
M_Restrictions (Meta information about the Restrictions)
R_Restrictions_Funds (cross-refrence table)

In R_Restriction_Funds I store all the combinations of Funds and their Restrictions. On my Form the user can maintain de attached restrictions by moving the restriction between the two listboxes:
.listRestrictionsAttached
.listRestrictionsNotAttached
To populate the first one, I successfully use the following Query:

SQLa = "SELECT R_Restrictions_Funds.ID, M_Restrictions.ID As [Id#], M_Restrictions.Code, " & _
       "M_Restrictions.Description, M_Restrictions.NumberOfVariables As Variabelen " & _
       "FROM R_Restrictions_Funds INNER JOIN M_Restrictions ON R_Restrictions_Funds.ID_Restriction = M_Restrictions.ID " & _
       "WHERE R_Restrictions_Funds.Id_fund = " & IdFonds
      .listRestrictionsAttached.RowSource = SQLa

Not I want to populate the other listbox with alle the restrictions which are not in de result of the above Query. Thanks to this forum I found the function "NOT IN", but I am not sure how to implement this. Now, I've got the following Query but it simply does not work! This the other Query.

SQLb = "SELECT R_Restrictions_Funds.ID, M_Restrictions.ID As [Id#], M_Restrictions.Code, " & _
       "M_Restrictions.Description, M_Restrictions.NumberOfVariables As Variabelen " & _
       "FROM R_Restrictions_Funds INNER JOIN M_Restrictions ON R_Restrictions_Funds.ID_Restriction = M_Restrictions.ID " & _
       "WHERE R_Restrictions_Fund.Id_fund NOT IN (" & SQLa & ")"
       .listRestrictionsNotAttached.RowSource = SQLb

Question: What is wrong the the above. I simply miss it. I've got a solution with a do while loop, but of course, that is not a nice way of filling a listbox. Any help appreciated. An example of how to use the NOT IN statement in this case would be very benificial too. Thanks in advance.

Kind regards,

 

[HarleyQuinn]

To use the NOT IN your subquery (SQLa) would have to only return the R_Restrictions_Fund.Id_fund (as that's the field you're checking against), rather than the 5 columns it does now.

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Karja]

Thanks for your answer HarleyQuinn. You centainly got a point there. Now the query works (which it did not before) but it returns exactly the same values as SQLa (which I did not alter). I've not got a clue why this is happening? Do you?

'Niet-gekoppelde restricties
subSQL = "SELECT R_Restrictions_Funds.ID " & _
       "FROM R_Restrictions_Funds INNER JOIN M_Restrictions ON R_Restrictions_Funds.ID_Restriction = M_Restrictions.ID " & _
       "WHERE R_Restrictions_Funds.Id_Fund = " & IdFonds

SQLb = "SELECT R_Restrictions_Funds.ID, M_Restrictions.ID As [Id#], M_Restrictions.Code, " & _
       "M_Restrictions.Description, M_Restrictions.NumberOfVariables As Variabelen " & _
       "FROM R_Restrictions_Funds INNER JOIN M_Restrictions ON R_Restrictions_Funds.ID_Restriction = M_Restrictions.ID " & _
       "WHERE R_Restrictions_Funds.Id_Fund NOT IN (" & subSQL & ")"
        .listRestrictionsNotAttached.RowSource = SQLb

Regards, Karja

 

[HarleyQuinn]

I'm not sure why you're getting the same results off the top of my head but one idea I did have, rather than using NOT IN could you simply not use <>?

SQLb = "SELECT R_Restrictions_Funds.ID, M_Restrictions.ID As [Id#], M_Restrictions.Code, " & _
       "M_Restrictions.Description, M_Restrictions.NumberOfVariables As Variabelen " & _
       "FROM R_Restrictions_Funds INNER JOIN M_Restrictions ON R_Restrictions_Funds.ID_Restriction = M_Restrictions.ID " & _
       "WHERE R_Restrictions_Funds.Id_fund [red]<> " & IdFonds[/red]
      .listRestrictionsNotAttached.RowSource = SQLb

Is that any closer for you?

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[Karja]

Thanks again. Not a bad idea but I should add that as it is a many-to-many relationship. I do not want to let the listbox populated with doubles or triples etc. Therefore I must include a DISTINCT G_Restrictions.Id. I will try Wednesday to re-write the queries as I think I must start with G_Restrictions.Id. I will keep you posted.

 

[Karja]

Hello. I finally managed to finish the job. Below the working code, just to properly close this thread. Thanks for the help!

'Gekoppelde restricties
SQLa = "SELECT M_Restrictions.ID, M_Restrictions.Code, M_Restrictions.Description, M_Restrictions.NumberOfVariables " & _
       "FROM M_Restrictions INNER JOIN R_Restrictions_Funds ON M_Restrictions.ID = R_Restrictions_Funds.ID_Restriction " & _
       "WHERE R_Restrictions_Funds.Id_Fund = " & IdFonds & ";"
.listRestrictionsAttached.RowSource = SQLa

'Niet-gekoppelde restricties (Zelfde Query maar dan de resultaten van SQLa precies andersom)
SQLb = "SELECT M_Restrictions.ID, M_Restrictions.Code, M_Restrictions.Description, M_Restrictions.NumberOfVariables " & _
       "FROM M_Restrictions " & _
       "WHERE M_Restrictions.ID NOT IN (SELECT M_Restrictions.ID " & _
       "FROM M_Restrictions INNER JOIN R_Restrictions_Funds ON M_Restrictions.ID = R_Restrictions_Funds.ID_Restriction);"
.listRestrictionsNotAttached.RowSource = SQLb

For those who are wondering: the language of the comments is Dutch.