Update query with another query

[hmessing]

Thank you to lameid and PHV who helped me solve my problem that I posted yesterday. It helped me get further with what I am trying to do, but now I'm stuck again.

Briefly, my database is used to create buying programs for our customers. Their discounts are based on the dollar commitment level for the year. We have three different levels A, B, and C (levels are determined by a shipment schedule). The list price of our products are then discounted to give the customer their price. Some customers have special net pricing on certain items.

So what I've done is created a query on the regular price list to calculate the customers's costs and another query to show any customers that have special nets. Then I created a union query so I can show them all on one list. My problem is that those part numbers that have a special net are duplicated, it shows their regular price and their special net on a separate line. What I'm trying to do is "over-ride" the regular price with the special net price. I've tried doing an update query but it won't let me.

Here is an example of a couple of part numbers in the list with pricing:

Part# A B C Base
N-100S $8.88 $9.82 $10.23 $10.44
N-100S $7.80 (this is a special net)
12024E-10 $9.59 $10.60 $11.05 $11.28
12024E-10 $8.45 $8.97 $9.50 (these are special nets)

So how can I tell it over-ride the regular price with the special net price?

Sorry for the long explanation, hopefully it makes sense.

Here's my code for the union query:
SELECT [Distributor Price List calculation].Distributor, [Distributor Price List calculation].Model, [Distributor Price List calculation].[2007ListPrice], [Distributor Price List calculation].[A$] AS A, [Distributor Price List calculation].[B$] AS B, [Distributor Price List calculation].[C$] AS C, [Distributor Price List calculation].Base, [Distributor Price List calculation].Order1, [Distributor Price List calculation].Order2, [Distributor Price List calculation].Order3, [Distributor Price List calculation].[R/C], [Distributor Price List calculation].Description, [Distributor Price List calculation].ProductCategory, [Distributor Price List calculation].SubCategory1, [Distributor Price List calculation].SubCategory2, [Distributor Price List calculation].ComingSoon, "" AS Net
FROM [Distributor Price List calculation];
UNION SELECT [2007 Smart Buy].Distributor, [Product Descriptions].Model, [Product Descriptions].[2007ListPrice], [Proposed Nets].A, [Proposed Nets].B, [Proposed Nets].C, "" AS Base, [Product Descriptions].Order1, [Product Descriptions].Order2, [Product Descriptions].Order3, [Product Descriptions].[R/C], [Product Descriptions].Description, [Product Descriptions].ProductCategory, [Product Descriptions].SubCategory1, [Product Descriptions].SubCategory2, [Product Descriptions].ComingSoon, [Proposed Nets].Net
FROM ([Product Descriptions] INNER JOIN ([Proposed Nets] INNER JOIN PROD_PART ON [Proposed Nets].[Part#] = PROD_PART.USER_PART_NO) ON [Product Descriptions].Model = [Proposed Nets].[Part#]) INNER JOIN [2007 Smart Buy] ON [Proposed Nets].USER_CUST_NO = [2007 Smart Buy].Distributor
GROUP BY [2007 Smart Buy].Distributor, [Product Descriptions].Model, [Product Descriptions].[2007ListPrice], [Proposed Nets].A, [Proposed Nets].B, [Proposed Nets].C, "", [Product Descriptions].Order1, [Product Descriptions].Order2, [Product Descriptions].Order3, [Product Descriptions].[R/C], [Product Descriptions].Description, [Product Descriptions].ProductCategory, [Product Descriptions].SubCategory1, [Product Descriptions].SubCategory2, [Product Descriptions].ComingSoon, [Proposed Nets].Net;


Thanks for your help.