If prices equal then choose always choose Vendor A

[jsccorps]

For the same PID when two different VIDs have the same price, select VID A.
For example, for PID=1 both VID A and B have same price of $9.88 so would use
the record with VID=A. Same would apply for PID=8.

Before:
PID VID Price
1 A $9.88
1 B $9.88
2 B $7.87
3 A $2.79
4 A $4.89
5 A $12.87
6 B $14.31
7 B $5.36
8 A $14.73
8 B $14.73

After:
PID VID Price
1 A $9.88
2 B $7.87
3 A $2.79
4 A $4.89
5 A $12.87
6 B $14.31
7 B $5.36
8 A $14.73

 

[Michel Walsh]

SELECT a.pid, a.vid, a.price
FROM myTable As a INNER JOIN myTable AS b
ON a.pid=b.pid AND (a.price > b.price OR (a.price = b.price AND a.vid >=
b.vid))
GROUP BY a.pid, a.VID, a.Price
HAVING COUNT(*) = 1


should do.

Indeed, the ON clause admit one record, from the reference b, only if
a.price is already the minimum, for that pid, and only if its vid is also
the minimum having such price, for such pid.



Vanderghast, Access MVP

 

[jsccorps]

Getting message

"... can't represent join expression a.price > b.price OR (a.price =
b.price AND a.vid >= b.vid) in Design View"
One or more fields may have been deleted or renamed
The name of one or more fields or tables specified in the join may be
misspelled
The join may use an operator that isn't supported in Design view, such as >
or <."

Is the > causing the problem?

 

[Michel Walsh]

You have to use the SQL view, the join cannot be represented graphically.

I assumed your fields names were pid, price and vid. If they differ, use
your real field names.


Can also try adding an extra parenthesis:


ON (a.pid=b.pid AND (a.price > b.price OR (a.price = b.price AND a.vid >=
b.vid)))



Vanderghast, Access MVP