query to return minimal product of two fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

To return a minimal price of a bottle of vodka stored in field "price" I
can call MIN function .

However this price ignores the size of the bottle.
Bottle types and sizes are stored in a different table called "Bottles"

Suppose I wanted to get a product with the minimal price per litre.
Obviously I have to multiply the bottle price by the bottle size.

Can I do it in one query which will fetch the minimal per/liter price?
 
Something like this:

SELECT Min([PriceTable].[Price] / [Bottles].[SizeInLiters])
FROM [PriceTable] INNER JOIN [Bottles]
ON [PriceTable].[BottleID] = [Bottles].[BottleID];
 
AA,

Without more information about the table structure, and how they are
related, it is difficult to give a specific answer. But the general
answer is Yes, this should be a simple calculated field in a query
such as...
Min([Price]/[Bottle Size])

- Steve Schapel, Microsoft Access MVP
 
Back
Top