Hi all,
I've used a Union query in Access for the first time recently and I'm encountering some strange behaviour. The query is set up correctly, same number of columns, same data types etc and runs fine when I click it in the navigation panel.
However, this query is the record source for a couple of subforms, and is referenced in some VBA modules. I'm finding that sometimes when I open the forms that use it as record source it generates an error ("The number of columns in the two selected tables or queries of a union query do not match"), same goes for using it in a recordset in code, it generates a 3307 error sometimes. Is this a known bug with union queries, if so what can I do to get it to work consistently?
Regards
Query for reference:
I've used a Union query in Access for the first time recently and I'm encountering some strange behaviour. The query is set up correctly, same number of columns, same data types etc and runs fine when I click it in the navigation panel.
However, this query is the record source for a couple of subforms, and is referenced in some VBA modules. I'm finding that sometimes when I open the forms that use it as record source it generates an error ("The number of columns in the two selected tables or queries of a union query do not match"), same goes for using it in a recordset in code, it generates a 3307 error sometimes. Is this a known bug with union queries, if so what can I do to get it to work consistently?
Regards
Query for reference:
Code:
SELECT tbl_customers_triggered.id,
tbl_customers_triggered.DT,
tbl_customers_triggered.CUST_ID,
tbl_customers_triggered.ACCT_NO,
"Flags Report - Score: " & [Ratings] AS monitor_text,
"N/A" AS dismissal_reason_text,
qry_flags_and_interaction_count.interactions,
tbl_customers_triggered.id AS flag_id
FROM
tbl_customers_triggered
INNER JOIN qry_flags_and_interaction_count
ON tbl_customers_triggered.id = qry_flags_and_interaction_count.id
UNION ALL SELECT tbl_monitors_triggered.monitor_PK AS 'id',
tbl_monitors_triggered.dt,
tbl_monitors_triggered.cust_id,
tbl_monitors_triggered.acct_no,
lst_monitors.monitor_text,
lst_dismissal_reason.dismissal_reason_text,
qry_monitors_and_interaction_count.interactions,
0 as flag_id
FROM ((tbl_monitors_triggered
INNER JOIN qry_monitors_and_interaction_count
ON tbl_monitors_triggered.monitor_PK = qry_monitors_and_interaction_count.monitor_PK)
INNER JOIN lst_monitors
ON tbl_monitors_triggered.monitor_fk = lst_monitors.monitor_PK)
LEFT JOIN lst_dismissal_reason
ON tbl_monitors_triggered.dismissed_reason_fk = lst_dismissal_reason.dismissal_reason_pk
ORDER BY dt DESC;
Last edited: