Query by different number or criteria each time

[Djblois]

I have a query attached to a form. In which depending on the user logged in, it will need to query based on a different number of criteria.


As an example:


If user A then view Group A, Group B, Group C
If user B then view Group D, Group E
If user C then view Group A, Group E, Group F, Group G
and so no


I cannot hard code it as the number of Groups can change per person. The way I get the number of groups a user has access to is there is a table with a many to many connection. Each user can have many groups in that table. How can I code this?



** Sorry if you see this as a duplicate. I posted this yesterday but I searched by my username and the title and cannot find it.

 

[arnelgp]

what is the structure of table (many to many connection), including the fieldtype.

 

[Djblois]

Primary Key: OtherReceivingID Autonumber
Foreign Key: SalespersonID Number
The Groups: UserName Short Text

 

[arnelgp]

are there any table you can link to the above to clearly identify the groups the user belongs.

 

[Djblois]

the UserNames is what it will filter by. It is salespeople, so the names within the UserNames field is who the Salesperson has access to see. So their name will be in there and others, if they have access to see other salespeople orders.

 

[arnelgp]

is the query "view" (readonly)?

select * from table1
where table1.[groupField]
in (select userName from the_table t1 where t1.salesPersonID = " & global_useID);