User view maintenance - users may have multiple cases

[Paul]

Since each 'Contact Name' (or client) might have more than one case, it makes it tricky to maintain which records (cases) are viewed and 'Printed' by each user. I have that figured out by copying and editing lines from the Browse SQL below. But going back and forth between tables and columns to find the information is a pain.
To help maintain which users see which records, I have made 'Full Name' (from the Users Login record) the same value as 'Contact Name' on each record. They are one and the same 'entity'.
I did a join query to display the user_id on the EDIT form which corresponds with 'Contact Name' and 'Full Name' for each record (case). In other words, the same Contact Name may appear on more than one record (case) so I need that user_id to help modify my Browse form SQL. But that resulting user_id needs to be displayed on the Browse form as well to allow for easier maintenance of the main Browse SQL query. The browse form already shows the case number and user name/contact name, I just need to add the user ID that I got from the join query I did. How do I accomplish that?
Did I describe the situation clearly enough?

BROWSE form SQL:

Code: Select all

SELECT * FROM cases 
WHERE ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
    OR ('#USER_ID#' = '68d883538d9b830' AND pk_inv_case_no = '13')
    OR ('#USER_ID#' = '68d885c9ebe0dee' AND pk_inv_case_no = '14')
    OR ('#USER_ID#' NOT IN ('68d858650166554','68d883538d9b830','68d885c9ebe0dee'));

EDIT form SQL:

Code: Select all

SELECT
  zzzzsys_user.zzzzsys_user_id
FROM
  cases
  INNER JOIN zzzzsys_user ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE
  cases.pk_inv_case_no = '#RECORD_ID#';

[kev1n]

Have you tried something like this?

Code: Select all

SELECT
  *
FROM
  cases
  INNER JOIN zzzzsys_user 
    ON cases.pk_inv_contact = zzzzsys_user.sus_name
WHERE
  ('#USER_ID#' = '68d858650166554' AND pk_inv_case_no = '12') 
   OR ('#USER_ID#' = '68d883538d9b830' AND pk_inv_case_no = '13')
   OR ('#USER_ID#' = '68d885c9ebe0dee' AND pk_inv_case_no = '14')
   OR ('#USER_ID#' NOT IN ('68d858650166554','68d883538d9b830','68d885c9ebe0dee'));

[Paul]

When globeadmin is logged in with my original SQL in Browse form I get this:

sql1.PNG

When globeadmin is logged in with your SQL in Browse form I get this:

sql2.PNG

So globeadmin is not seeing all of the records.

Not sure if you are clear on what I am looking for.