Selecting Minimum Records

[JezBullen]

Hi

I have a table which lists parts and then the part prices for each supplier

I need a query that will select the cheapest one, and also display the name (field heading), so we can work out the cheapest supplier quickly. I thought this would be relatively easy, but not getting anywhere

Example of table....

Part SupA SupB SupC
PartA £12 £13.50 £13.45

Hope someone can help
Many thanks

 

[mndrlion]

You will need a query like this:

SELECT tblPartSuppliers.Part, IIf([SupA]<[SupB],IIf([SupA]<[SupC],[SupA],[SupC]),IIf([SupB]<[SupC],[SupB],[SupC])) AS Price, IIf([SupA]<[SupB],IIf([SupA]<[SupC],"Supplier A","Supplier C"),IIf([SupB]<[SupC],"Supplier B","Supplier C")) AS Supplier
FROM tblPartSuppliers;

 

[Golom]

The big problem is that your table is not normalized. I would first write a query that builds a normalized view of the table and then run the "lowest price" query from that.
[blue][tt]
(Select Part, "SupA" As Supplier, SupA As Price From tbl
Where SupA IS NOT NULL)
UNION ALL
(Select Part, "SupB" , SupB From tbl
Where SupB IS NOT NULL)
UNION ALL
(Select Part, "SupC" , SupC From tbl
Where SupC IS NOT NULL)
[/tt][/blue]
Save that as "qrySP", then
[blue][tt]
Select Part, Supplier, Price
From qrySP P
Where P.Price = (Select MIN(Price) From qrySP
Where qrySP.Part = P.Part)
[/tt][/blue]

 

[JezBullen]

Many thanks for the replies, both methods worked fine

With the Normalisation idea, it works well when I specify a part, but when I try to run the Query for all parts Access takes an age before hanging. Is there a quicker method, as I need to run a query to show all parts

Thanks in advance

 

[lespaul]

Unfortunately since your database isn't normalized, there really is no quicker way. You may want to add an index to the table, that may make the query run a little faster. The reason it's taking so long to run is when you run the first query and it searches every record to get all A Suppliers information, then it searches every record to get all the B Suppliers information, then again search every record to get all the C Suppliers information. Now that your data is normalized, the last query really doesn't take that long.

Two suggestions:
1. Restructure your database so it's normalized (check out TTer JeremyNYC's website

http://www.alphabetcitydataworks.com

in the developer's section there's a paper 'Fundamentals of Relational Database Design' that will help)

2. If it's not possible to completely normalize your database, then take the 1st query above and use this as the basis of a make table query. It will still take a while, but once it's done you'll have a normalized table to work from. Depending on how often you get new items you could update this table occasionally.

HTH

Leslie

 

[JezBullen]

Hi

I've normalised the table, but can't get a query that works from this for some reason

The table looks like

Part Supplier Price
E SuppA £24.99
E SuppB £29.76

Many thanks again,
JB

 

[lespaul]

Golom's second query should work, you just need to change the TableName:

Select Part, Supplier, Price
From TableName T
Where T.Price = (Select MIN(Price) From TableName
Where TableName.Part = P.Part)

Leslie