Re: If prices equal then choose always choose Vendor A
- From: "Michel Walsh" <vanderghast@VirusAreFunnierThanSpam>
- Date: Mon, 15 Dec 2008 05:45:46 -0500
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
"jsccorps" <jsccorps@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6BD0FB80-C1C0-42FF-ACE3-C25AB5849234@xxxxxxxxxxxxxxxx
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" wrote:
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" <jsccorps@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:69F123AC-B66A-4494-906A-3866CB0455BF@xxxxxxxxxxxxxxxx
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
.
- References:
- If prices equal then choose always choose Vendor A
- From: jsccorps
- Re: If prices equal then choose always choose Vendor A
- From: Michel Walsh
- Re: If prices equal then choose always choose Vendor A
- From: jsccorps
- If prices equal then choose always choose Vendor A
- Prev by Date: Re: Carry out a search using keywords
- Next by Date: Re: Carry out a search using keywords
- Previous by thread: Re: If prices equal then choose always choose Vendor A
- Next by thread: Read an FTP Log File
- Index(es):
Relevant Pages
|
