Comparing Price Quotes in a Query

[fahel-schafferv]

I am working on a database for bulk item ordering of parts. I have
three relational tables of information that I want to combine in a
query and compare the 3 or 4 bids for each part I am going to buy.
It's late in the day, and I am having trouble thinking clearly to
devise a formula that will answer true if it is the lowest bid of
three bids, and false otherwise. Is there anyone who could help me
out with this?

There is the "Catalog Number for Part" that has a one to many
relationship with "Vendor PN". The "Vendor Price per Unit" goes along
with the Vendor PN. I want to compare the 3 or 4 prices of each
"vendor PN" that correspnds to the "Catalog Number for Part" that I
want to replace.

catalog number for part: 123456
vendor pn: 123 is $2.56
vendor pn: 333 is $6.28
vendor pn: 122 is $1.30
vendor pn: 60 is $0.50

I want the results to equal true for P/N 60 and false for the other
three.

Is this possible?

 

[Allen Browne]

Several possibilities.

If this has to be an updatable form, you may need to use DLookup() to get
the vendor. Unfortunately, DLookup() doesn't allow you to specify sorting,
so use this extended replacment instead:
http://allenbrowne.com/ser-42.html
The expression would be something like this:
=ELookup("[Vendor]", "[Table1]", "[Part] = """ & [Part] & """", "[Price]
DESC")

If it doesn't have to be updatable, a subquery would be faster. See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

If subqueries are a new concept, see:
http://allenbrowne.com/subquery-01.html