Table/Query Design

[Kat]

I have run into a problem and hopefully someone will make the connection my
poor brain just doesn't want to make.

I have some data that will be imported into the database that looks
something like this (not actual field names):

Supplier | Part Number | Quantity Required | (bunch of unrelated stuff) |
Quantity Available | Lead Time | Price

Now I run this through a few tests to determine which Supplier has made the
best bid for each Part Number. I don't have a problem picking the first
right bid. Ulitmately, this is to produce a report of all the winning bids
for each supplier and the Quantity the planner should order from that
supplier.

The problem I am having is when the Quantity Available for the Supplier with
the best bid is lower than Quantity Required.

I have thought about a seperate table (tblWonBid) with Part Number (pk) in
it with a field for Supplier (of the winning bid) and the Quanity to order
from that supplier, however I am not sure how to work insufficient Required
Quantities into that design. I have also thought about a Won field and a
QtyWon field in the table I import the original data into that is then
updated when the line is selected as the best bid. The problem with this
second idea is how to look at the part again to make sure that all the
required quantity has been fufilled and look for the next "best bid" to make
sure we order the entire quanity required.

All of this is still at the "planning" stage, I don't have
tables/queries/code set up to do anything really yet. I need to get over
this problem (since it will determine my data structure and therefore
everything else) before I really work on that stuff.

I hope I have given enough information and thank anyone for their assistance
in advance.

Kat

 

[Noëlla Gabriël]

Hi Kat,

I think you can go a long way with a combination of calculations in queries
and working with temporary tables to solve this problem. If you have the
quantity required and the quantity available, you can easily calculate the
shortage in a query.
Where no shortage exists, you add the line of the first bidder to the temp
table. If there's a shortage you go search (with a bit of code) the second
bidder, calc the desired quantity to complete the set and add it also to the
temp table. The only trouble you can have: what if the winning prices are
quantity related: for instance if you buy 50 pieces you get 10% discount....