ms-access: select from another query

Question

i am connecting access to a mysql db.

i need to put together two statements and make them into one.

for example:

SELECT 
  users.id,
  users.first,
  users.last,
  chavrusas.luser_type AS user_type,
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM users
INNER JOIN chavrusas 
  ON Users.id=chavrusas.luser_id
WHERE     ((chavrusas.ruser_id)='3166' and chavrusas.ended=false) 
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student'); 
UNION
SELECT  
  users.id, 
  users.first, 
  users.last, 
  chavrusas.ruser_type AS user_type, 
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM users
INNER JOIN chavrusas 
  ON Users.id=chavrusas.ruser_id
WHERE     ((chavrusas.luser_id)='3166' and chavrusas.ended=false)
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student')
ORDER BY 4;

Users is a query which is:

SELECT 
  tblusers.*,
  tblusershliach.*,
  tbluserstudent.*,
  tbluserstudentteacher.*,
  tbluserteacher.*
FROM
(
  (
    (tblusers 
     LEFT JOIN tblusershliach 
     ON tblusers.id = tblusershliach.shliach_user_id
    )
  LEFT JOIN tbluserstudent 
  ON tblusers.id = tbluserstudent.student_user_id
  )
LEFT JOIN tbluserstudentteacher
ON tblusers.id = tbluserstudentteacher.student_teacher_user_id
) 
LEFT JOIN tbluserteacher 
ON tblusers.id = tbluserteacher.teacher_user_id;

instead of using "Users" in the first statement, i just want to put them together into one statement

how do i do it?

Answer 1

Your SQL statement includes a semi-colon before the UNION keyword. I'm not sure how Jet/ACE treats it, but I always thought the semi-colon meant "end of statement". Discard it and see if your results are any different. I'm not at all confident that will fix your problem, but let's make sure it's not contributing.

Update: I did some tests, and it looks like Jet/ACE just ignores a semi-colon within a UNION. I was barking up the wrong tree.

Answer 2

If you are looking at combining rows from both the queries (i.e. 3 rows from table1 & 2 rows from table2 = 5 rows using the final query), you could write

SELECT * FROM TABLE1
UNION
SELECT * FROM TABLE2

For UNION to work, the number of fields in both the queries should be same along with the data type.

i.e. if the TABLE1 has fields numeric, numeric, text, date - TABLE2 should also have the same number of fields (4) and in same order of the data type (i.e. numeric, numeric, text, date).

EDIT: I have looked at your modified question. It is OK to have User as separate query than making it a one big totally unreadable query in MS-Access.

Access doesn't retain the SQL formatting when you save it. So, it makes sense to split the query into different re-usable pieces than to push everything into its own query.

In your example, if you wish to have Users as part of the main query - it will have to be repeated for both case (LEFT and RIGHT side of UNION). That doesn't make sense.

EDIT: Here is the big query. Thanks to @Welbog. The formatting will be lost when you save the query in Access.

EDIT2: See if this helps. I have included the "USERS" query into your main sql.
The idea is to match the brackets.

SELECT 
  users.id,
  users.first,
  users.last,
  chavrusas.luser_type AS user_type,
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM 
    (
        (
            (
                (tblusers AS Users
                 LEFT JOIN tblusershliach 
                 ON tblusers.id = tblusershliach.shliach_user_id
                )
                LEFT JOIN tbluserstudent 
                ON tblusers.id = tbluserstudent.student_user_id
            )
                LEFT JOIN tbluserstudentteacher
                ON tblusers.id = tbluserstudentteacher.student_teacher_user_id
        ) 
        LEFT JOIN tbluserteacher 
        ON tblusers.id = tbluserteacher.teacher_user_id;
    )
INNER JOIN chavrusas 
  ON Users.id=chavrusas.luser_id
WHERE     ((chavrusas.ruser_id)='3166' and chavrusas.ended=false) 
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student'); 
UNION
SELECT  
  users.id, 
  users.first, 
  users.last, 
  chavrusas.ruser_type AS user_type, 
  chavrusas.id, 
  users.title, 
  users.city, 
  users.state, 
  users.home_phone, 
  users.email
FROM
    (
        (
            (
                (tblusers AS Users
                 LEFT JOIN tblusershliach 
                 ON tblusers.id = tblusershliach.shliach_user_id
                )
                LEFT JOIN tbluserstudent 
                ON tblusers.id = tbluserstudent.student_user_id
            )
                LEFT JOIN tbluserstudentteacher
                ON tblusers.id = tbluserstudentteacher.student_teacher_user_id
        ) 
        LEFT JOIN tbluserteacher 
        ON tblusers.id = tbluserteacher.teacher_user_id;
    )
INNER JOIN chavrusas 
  ON Users.id=chavrusas.ruser_id
WHERE     ((chavrusas.luser_id)='3166' and chavrusas.ended=false)
  AND     (chavrusas.luser_type) <> (chavrusas.ruser_type)
  AND NOT ((chavrusas.luser_type)='teacher' AND (chavrusas.ruser_type)='student')
  AND NOT ((chavrusas.ruser_type)='teacher' AND (chavrusas.luser_type)='student')
ORDER BY 4;