[Solved] Creating MySQL query from access query

by


Assuming the first query to run is named qryAgreedToServiceOrUnenrolled. Try nesting the first SQL in the second’s FROM clause. Can replace qryAgreedToServiceOrUnenrolled with some other alias everywhere it is referenced.

SELECT qryAgreedToServiceOrUnenrolled.patient_id, dispositions.description, dispositions.member_status
FROM ((

    (SELECT DISTINCT t.patient_id, MAX(t.id) AS MaxOfid
    FROM transactions AS t
    INNER JOIN disposition_transaction_type AS dt
        ON t.disposition_transaction_type_id = dt.id
    INNER JOIN dispositions AS d
        ON dt.disposition_id = d.id 
    WHERE (d.member_status = "Unenrolled" AND (t.created_at BETWEEN '2019-05-01' AND '2019-05-31')) OR ((t.created_at BETWEEN '2019-05-01' AND '2019-05-31') AND (t.disposition_transaction_type_id = 24))
    GROUP BY t.patient_id) AS qryAgreedToServiceOrUnenrolled 

INNER JOIN transactions ON qryAgreedToServiceOrUnenrolled.MaxOfid = transactions.id) 
INNER JOIN disposition_transaction_type ON transactions.disposition_transaction_type_id = disposition_transaction_type.id) 
INNER JOIN dispositions ON disposition_transaction_type.disposition_id = dispositions.id;

I agree that DISTINCT is probably not needed.

0

solved Creating MySQL query from access query