K
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
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