Hi there,
I have a combo box that filters records on a subform by user name. I'd like to add an "all" option to the combo box list so that all records regardless of user name may be displayed at once. I have attempted a few approaches to this using a "union query" but am failing to achieve the correct syntax. The query I'm using is this:
This is the error message I'm getting:
Could anyone help point me back in the right direction?
Thank you!
I have a combo box that filters records on a subform by user name. I'd like to add an "all" option to the combo box list so that all records regardless of user name may be displayed at once. I have attempted a few approaches to this using a "union query" but am failing to achieve the correct syntax. The query I'm using is this:
Code:
select top 1 0,"**ALL**" from tblauditstaff
union All
SELECT tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname] AS [Auditor Name], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
FROM tblAuditStaff INNER JOIN tblDiscrepancy ON tblAuditStaff.AuditStaffID = tblDiscrepancy.DiscrepancyAssignedTo
WHERE (((tblAuditStaff.AuditStaffResponsible)=True) AND ((tblDiscrepancy.DiscrepancyAssignedTo) Is Not Null))
GROUP BY tblAuditStaff.AuditStaffID, [tblauditstaff].[auditstafflastname] & ", " & [tblauditstaff].[auditstafffirstname], tblAuditStaff.AuditStafffirstName, tblAuditStaff.AuditStaffLastName, tblAuditStaff.AuditStaffResponsible, tblDiscrepancy.DiscrepancyAssignedTo, tblDiscrepancy.DiscrepancyActualCompDate, tblDiscrepancy.DiscrepancyOngoing, tblDiscrepancy.DiscrepancyNonGMP, tblDiscrepancy.DiscrepancyCompleted, tblDiscrepancy.DiscrepancyDuplicate
HAVING (((tblDiscrepancy.DiscrepancyActualCompDate) Is Null) AND ((tblDiscrepancy.DiscrepancyOngoing)=False) AND ((tblDiscrepancy.DiscrepancyNonGMP)=False) AND ((tblDiscrepancy.DiscrepancyCompleted)=False) AND ((tblDiscrepancy.DiscrepancyDuplicate)=False));
This is the error message I'm getting:
The number of columns in the two selected tables or queries of a union query do not match.
Could anyone help point me back in the right direction?
Thank you!