Update Query

[Pam]

I have an update query that is not giving results as I would want.
Two tables:

tPriceList
PartID
Manufacturer
ManufacturerPartNumber
Description
Price

t2007PriceUpdate
PartID
Manufacturer
ManufacturerPartNumber
Description
Price

UPDATE tPriceList INNER JOIN t2007PriceUpdate ON
tPriceList.ManufacturerPartNumber = t2007PriceUpdate.ManufacturerPartNumber
SET tPriceList.ListPrice = t2007PriceUpdate.Price
WHERE (((tPriceList.Manufacturer)="NewVendor"))
WITH OWNERACCESS OPTION;

Here's the problem:
tPriceList has part number 2605. t2007PriceUpdate has part number 2605
listed three times as below

2605 wear plate $75.00
2605 for brz see 2608 $0.00
2605 for stl see 2609 $0.00

when I update, part number2605 returns with 0.00 amount. Is there some
criteria I can put in to make it pick up the part number with the dollar
amount? I know I'm missing something here.

If anyone can help, it would be appreciated.
Thanks in advance,
Pam

Here's the problem:

tPartsList has entry for part 2605. t

 

[John Spencer]

You could try filtering out the zeroes.

UPDATE tPriceList INNER JOIN t2007PriceUpdate
ON tPriceList.ManufacturerPartNumber =
t2007PriceUpdate.ManufacturerPartNumber
SET tPriceList.ListPrice = t2007PriceUpdate.Price
WHERE (((tPriceList.Manufacturer)="NewVendor"))
AND t2007PriceUpdate > 0
WITH OWNERACCESS OPTION;

Also, does PartID in tPriceList match up to PartID in t2007PriceList. IF
so, you might want to consider joining on that field.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Pam]

Thanks, John, for the reply. The PartID does not match - it is only an
auto-increment record number. The PriceUpdate table changes each year and
new parts are added from vendor and I just give each part a new number in
the yearly update table. I guess I could filter the zeroes. I was thinking
if there were single entry part numbers that had a new updated part number
and the old number was zeroed out, it wouldn't pick up that change. I was
hoping there was something I was missing to make this work.

Thanks again,
Pam