## 4 replies

Last post Nov 30, 2009 10:52 AM by Qasimali84

• Qasimali84

Member

19 Points

70 Posts

### How I can get overall lowest cost

Dear Experts

I have problem in MS Access/SQL query.

I have three database tables  (1.Material  2. Suppliers  3. OFFERS ) OFFERS table contains different offers of suppliers for different materials like as

OfferID  Quantity UnitPrice  DelTime  Supplier   Material
1             1          99         5            A         Chair
2             3          90         5            A         Chair
3             5          80         5            A         Chair
4             1          95         5            B          Chair
5             1          90         5            C          Chair
6             3          85         5            C          Chair
7            10         80         5            C          Chair

I want to get the supplier with overall lowest price for Chairs of quantity 14
The paper work for overall lowest price is given as
Material: Chairs
order Quanity: 14

Cost for Supplier A:

10 x 80= 800 (As by offer 3)
3 x 90=  270  (As by offer 2)
1 x 99=   99   (By offer 1)
Overall Cost for supplier a is: 1169

Cost for Supplier B:

14 x 95= 1330 (By offer 4)
Overall Cost for supplier a is: 1330

Cost for Supplier c:

10 x 80= 800 (by offer 5)
3 x 85=  255  (by offer 6)
1 x 90=   90   ( offer 1)
Overall Cost for supplier C is: 1145

In this Supplier C offer minimum cost for 14 chairs
How I can access it via query.

• rtpHarry

All-Star

39486 Points

8916 Posts

### Re: How I can get overall lowest cost

Ouch! That looks like a complicated query...

• hans_v

All-Star

25756 Points

7014 Posts

### Re: How I can get overall lowest cost

I don't think you can do this in SQL. I would suggest to do this in VB / C# code...

• rtpHarry

All-Star

39486 Points

8916 Posts

### Re: How I can get overall lowest cost

yeah, you should start off in a math forum to ask what its called when you need to find which combination of numbers from a given set can be used to total a specific number.

I was thinking it was called permutations but thats just a brute force listing of all the possible combinations of a set of numbers. I guess you could then add up all of the permutations individually and see which combinations = 14 (or your required order quantity) and then calculate the total order value for those permutations and take the lowest value.

I would still ask at a math forum though to see if you can find a better way to approach this problem?

• Qasimali84

Member

19 Points

70 Posts