Make a union qry

[sonikudi]

Hey,

Okay, i have qry A and qry B..and i want qry C that would do qry A union qry B. The problem is that.. qry A selects 4 cols from tables and qry B select only 3 cols from tables. Both qry A and qry B are aggregate queries. The union of these two queries would have worked great if qry B had 4 cols as well..but it doesn't. When i do a union of qry A and qry B it says they don't have equal number of cols. Is there any way to solve this problem? how can i combine results of qry A and qry B in one table and the rows in qry B not having the 4th col; i would just like those rows to be blank for qry B results. Hope this makes sense. below is the code

[B] QRY A [/B]
StrSQL = "SELECT t1.[Error Code],t2.Description,COUNT(t1.[PCB SS #]) AS TtlNumberFailed " & _
" FROM [PCB Incoming_Production Results] AS t1, [PCB Error Codes] as t2 " & _
" WHERE t1.[Error Code]<> 'Pass' AND t1.[Error Code]<> 'n/a' AND t1.[Error Code]= t2.[Code] AND Manufacturer = """ & Me.txtMfg & """ And [Date] Between #" & _
   Me.txtMfgStart & "# AND #" & Me.txtMfgEnd & "# GROUP BY t1.[Error Code],t2.Description; "

[B] QRY B [/B]
StrSQL = "SELECT (t1.[Error Code]), count (t1.[Error Code])As TtlNumberFailed " & _
"FROM [PCB Incoming_Production Results] AS t1 LEFT JOIN [PCB Error Codes] ON t1.[Error Code] = [PCB Error Codes].CODE " & _
"WHERE ((([PCB Error Codes].Code) Is Null)) GROUP BY (t1.[Error Code]); "

[B]QRY C[/B]  
StrSQL = "SELECT * FROM qryNoFailedPCBByET UNION SELECT * FROM qryNotHaveED; "

Any help will be just great.

THANK YOU!

 

[jrbarnett]

Add a null column to query A as a fourth.

Bear in mind if you add an order by to the end it will operate as part of the whole query, not an individual part.

John

 

[sonikudi]

Thanks John.

what is the sql code to add a column? also would i have to output the information into a table or something??

 

[jrbarnett]

To add a column you just add it to the end of the select list, for example:

StrSQL = "SELECT t1.[Error Code],t2.Description,COUNT(t1.[PCB SS #]) AS TtlNumberFailed, [b]Null As Column4[/b] " & _
" FROM [PCB Incoming_Production Results] AS t1, [PCB Error Codes] as t2 " & _
" WHERE t1.[Error Code]<> 'Pass' AND t1.[Error Code]<> 'n/a' AND t1.[Error Code]= t2.[Code] AND Manufacturer = """ & Me.txtMfg & """ And [Date] Between #" & _
   Me.txtMfgStart & "# AND #" & Me.txtMfgEnd & "# GROUP BY t1.[Error Code],t2.Description; "

Do you have to output the information to a table? No, unless you require the information in a table for your application, but for Access, it can use a query as the source for a report, form etc so there is no need.

John

 

[sonikudi]

Hey,

actually a column3 needs to be added to QRY B, i think i mislabeled the number of columns in my description the first time. But anywho i did that and when i execute the qry it output the parameter box and asks me for the value of col3.
this is what i entered in the queries under Db window to test it

SELECT (t1.[Error Code]), count (t1.[Error Code]) AS TtlNumberFailed, NULL as col4
FROM [PCB Incoming_Production Results] AS t1 LEFT JOIN [PCB Error Codes] ON t1.[Error Code] = [PCB Error Codes].CODE
WHERE ((([PCB Error Codes].Code) Is Null)) 
GROUP BY (t1.[Error Code]), col3;

 

[sonikudi]

okay my bad. i found my mistake. Entered different names for the null column in the qry

 

[sonikudi]

hey john,

Thanks so much for all the help. This part of the program works.

I still do get the "enter parameter value" dialog box for col3 that access automatically pops up everytime the query is run. Any idea how i disable this pop-up dialog box in my VB code??

thanks!!

 

[jrbarnett]

Change col3 to col4 in the Group By line.

John

 

[sonikudi]

thxs..this problem is fixed.

 

[GingerR]

How about a star for John?

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[PHV]

In fact, the problem was fixed here:
thread701-1431655