Data Type Mismatch help 1

[matthej]

I have two queries that I'm trying to union. One pulls cost of inventory and the other pulls cost of goods sold. Each query also returns manufacturer. I want to union the two queries. Here’s what I have:

SELECT [Prior Month].MFG, [Prior Month].Cost as [Cost of Goods Sold], '' as [PURCH COST]
FROM [Prior Month]
Union
SELECT Inventory.MFG, '', Inventory.[PURCH COST]
FROM Inventory

As you can see, the Prior Month table doesn’t contain the Purch Cost (cost of inventory) field and the Inventory table doesn’t contain the Cost (Cost of Goods sold) field. I want a query that returns those two fields along with Manufacturer so that I can then use a Crosstab query and do a summary by Manufacturer.

When I export each query separately into Excel, the cost fields show up as numbers. However, when I union the two queries and export the result, the numbers are exported as text. Additionally, Access won't SUM on these numbers and tells me that there's a data type mismatch. I assume that's because the union query turns the cost fields into text. In the base tables, those two fields are set up as numbers.

How do I use the union query appropriately to get the cost fields to be returned as numbers instead of text?

I’ve tried to do a search on this topic, but haven’t found anything, so I apologize in advance if it’s a repeat subject.

I appreciate any help you can provide.

Thanks, Matthej

 

[r937]

use 0 instead of the empty text string

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts May 8 2005)

 

[cmmrfrds]

The Union requires the same # of columns of the same data type in both select lists.

SELECT 1 as type, [Prior Month].MFG, [Prior Month].Cost as [Cost of Goods Sold]
FROM [Prior Month]
Union
SELECT 2 as type, Inventory.MFG, Inventory.[PURCH COST]
FROM Inventory