Displaying the lowest cost only

[cary1234]

number..materials.........................Unit.....Supplier1...Cost1......Supplier2...Cost2......Supplier3...Cost3
...1.......Faucet 1/2"....................pcs........a..............7...........b.............6...........c...............5
...2.......Utility Box 2" x 4".............pcs........a............77...........b.............7...........c................5
...3.......2x36w Fluorescent Light....set........a............5............b.............6...........c.................5
...4.......Ceiling Board 2' x 2'...........pcs.......a............3............b.............5...........c................5
...5.......Aluminum T runner............pcs.......a............1............b.............2...........c................3
...6.......CHB 4"...........................pcs........a............2............b............5...........c................3
...7.......Portland Cement..............bags.......a...........2............b............5...........c................3
...8.......Aircon 5TR......................set.........a...........2............b............1...........c................3
...9.......Window Type ACU 3/4hp....set........a...........1.............b............1...........c................1


sorry for this crap, its because i need 10 post in order to allow posting links. So I have no choice.
That is my table and i what i want to happen in my query is to be look like this.

number..materials.........................Unit.....Supplier Name...Cost
...1.......Faucet 1/2"......................pcs..........c...............5
...2.......Utility Box 2" x 4"...............pcs..........c................5
...3.......2x36w Fluorescent Light....set..........a....................5............c...........5
...4.......Ceiling Board 2' x 2'...........pcs.........a....................3
...5.......Aluminum T runner............pcs.........a....................1
...6.......CHB 4"...........................pcs..........a.....................2
...7.......Portland Cement..............bags.........a.....................2
...8.......Aircon 5TR......................set...........b...................1
...9.......Window Type ACU 3/4hp....set..........a..................1.............b............1...........c................1



The query will determine the lowest cost for each supplier. a,b and c are the sample supplier names and the cost is the price of material depending on the price given by the supplier. If the 2 supplier supplies material at the same lowest cost the query must output those supplier that has the same lowest cost of material.

Is that possible? Im totally newbie when it comes to ms access, I have intermediate knowledge when it comes to vb 2008.

 

[pbaldy]

I'd reconsider the design. Having each supplier/price as fields is very limiting and makes it more difficult to find the lowest. Proper normalized database design would have each product/supplier/cost combination as a separate record (row). That makes it easy to find the lowest cost using a totals query and Min():

http://www.baldyweb.com/LastValue.htm

 

[cary1234]

Hmmm. Do you want me to reconsider the design right? Its okay but what should I do? Please be patient, I'm just a total newbie. Thanks!

 

[pbaldy]

Like I said:

Faucet 1/2".....pcs.....a.....7
Faucet 1/2".....pcs.....b.....5
Faucet 1/2".....pcs.....c.....6

Presuming you had a products table, you'd likely have an ID here instead of Faucet 1/2", and pcs might be in that table instead of this one. Depends on your overall situation.

 

[cary1234]

Ahh... okay, thanks! But I already finish doing that.
I will start my OJT this coming summer and it will be great if I will start creating their system. He ask me if it is okay to avoid repetition of item and making it like the one that I post (post #1 ). Thanks for your help, do you have another better idea?

 

[pbaldy]

Well, your structure in post one violates normalization rules (it's spreadsheet design, not relational database design). Let's say you get it all set up that way, and next month they decide they need 4 suppliers instead of 3. With your original structure, you have to go back and redesign everything. A normalized structure would handle the change without redesign. If you want to stay with that structure, you either need to adapt something like this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209857

Or use a UNION query to "normalize" the data and then use the normal SQL Min() function. I suspect you'll be back here numerous times with questions about how to work around that structure.

 

[cary1234]

Ahh. Okay I think i get the idea now.Yeah you are right about that. I search about union query about how it works, but i dont know what to put in the tables and how to arrange them so that it the query will output the minimum cost.

 

[cary1234]

By the way my boss said that they only need 3 suppliers and if the time comes that there will be a supplier number 4 they will remove one of the suppliers that has a highest cost of material so that the number of suppliers will only be 3

 

[gemma-the-husky]

cary -

as already pointed out

you dont want a table that looks like this

item        price1    supplier1 price2  supplier2  price3  supplier3
faucet        7.00        A        6.00       B         6.50      C

you want one that looks like THIS

item           price    supplier
faucet        7.00      A
faucet        6.50      B
faucet        6.00      C

then you get the lowest price simply by using a simple totals query to find the minimum price.

this way, you can have
a) more or fewer suppliers - not just 3
b) you can compare prices easily
c) you can easily find all prices for a given supplier.

 

[pbaldy]

By the way my boss said that they only need 3 suppliers and if the time comes that there will be a supplier number 4 they will remove one of the suppliers that has a highest cost of material so that the number of suppliers will only be 3

Your boss obviously doesn't know proper relational database design, but is trying to shove a round peg into a square hole. Access is not designed to operate that way. You can do it if you want, but you have two professionals now advising against it. If you want to stay with it, I gave you two potential work-arounds in post 6.

 

[boblarson]

but you have two professionals now advising against it.

Make it THREE (I'll also agree with that assessment).

 

[cary1234]

Oh! Sorry, yeah I realized now that i was wrong. Thanks! yes you're right that my boss doesn't know proper relational database design. Okay I'll tell him that what he want is nearly impossible to do. Thank you boblarson, gemma-the-husky and of course the guy who has a lot of patient for helping a noob like me, he is pbaldy. Thanks! I learned a lot.

 

[pbaldy]

I think you'll be happier in the long run. Good luck!

 

[varunmathur]

I have a similar problem, how does one display the lowest values or price when multiple dealers quote the same lowest price.
In this case the query displays either of the two as the lowest value and is unreliable.
Help will be appreciated.

 

[Brianwarnock]

One simple approach is to get the min price and then use this info to get all of the dealers

Myquery

Select min(price) as minprice
From mytable

Query2
Select dealer,price
From mytable inner join myquery on mytable.price = myquery.minprice

Running query2 should produce what you require.

It can also be done with first query being a subquery but I am not good at writing those on the fly, tend to have too many typos

Brian