I have a database which works satisfactorily. It is made up of 2 main tables , Table A with the data in and Table B with the User details.
Table A fields include a User_ID and Dept_ID field. Table B has User_ID and Dept_ID and there are multiple users in each Deptment
I can build the database, with 2 additonal Views - View A (individual records) and View B (Team Records) to show all records associated with a User and all records associated with their Team.
View B is my issue. It works fine for me, but if I decide to give a user access to 2 or more Deptartments records, I can't get the other departments records to show.
So lets say User A can see their Dept A's records, and also Dept B's records, in View B only department A's records show. I have have tried a few WHERE statements but none see to work.
I'm trying to use a statement such as:
where dept_id in (select dept_id from TableB where user_ID = (SELECT user_id FROM tableB WHERE (login = '" & Session ("userID") & "'))))
It's trying to associate the logged in user with the data in this Team view that I am having an issue with. I just don't understand how to code it, and where to place the code.
Your assictance appreciated
Jeremy