AboutBob Heifler Expertise ALL MICROSOFT ACCESS DESIGN AND PROGRAMMING
QUESTIONS
Free Microsoft Access Tutorial Videos are available to you at
my web site. Fifteen years of Microsoft Access experience allows me to
answer most of your questions. Common question example files available
here.
Experience Microsoft Access
programming since 1994. Support for all versions.
Publications My Microsoft Access
articles are published at:DailyAccessTips.com
Education/Credentials UCLA GRAD, Member of Association of Database Developers
Awards and Honors AccessToExcel.com
has won several
shareware awards for assisting analysts in moving data from MS Access
to Excel.
SELECT AA.ProductID, AA.[Supplier ID], AA.Cost
FROM ItemSupp AS AA
WHERE (((AA.Cost)=(SELECT MIN(Cost) AS MinPrice FROM ItemSupp WHERE ProductID = AA.ProductID)));
(Not sure why access put lots of extra brackets in?).
BUT, what I want to do now is to be able to add more into that in the form of "OR WHERE [Supplier ID] = "A5" for example so that I will get a list of the minimum prices that I can compare to prices from a given supplier (so I think I will have to GROUP BY ProductID?).
Thanks in advance for any help given.
Regards
Andrew
Answer Andrew,
Yes a group by on product ID and use the visual query grid. Once you do that you can just use the build in (from the list) aggregate functions of min, max, avg, etc. You could even do additional grouping by SupplierID.
ALL MICROSOFT ACCESS DESIGN AND PROGRAMMING
QUESTIONS