I created a couple of queries - then use VBA to loop through the queries using this code
Code:
SQ = "SELECT count(*)"
SQ = SQ & " FROM (SELECT DISTINCT g"
SQ = SQ & " FROM " & aR(indx) & ")"
Set RCDset = db.OpenRecordset(SQ)
Where aR is an array that stores all the query names (there are 4), and indx is the index of the array.
It works for the first two rounds but when it hits like the 2 index (which is the next query) in the array -
error message - "The number of columns in the two selected tables or queries of a union query do not match"
Error always occured at Set RCDset = db.OpenRecordset(SQ)
Please advise !!