reduce inventory based on needs

I dont understand your data structurein the SOH worksheet.

does all that dta relate to the one serial number?

there are 4 rows.  how does those rows match up to the other sheet?

Doa ll the items needed for the single repair match up to a serial number on the inprocess sheet?

if so, do you need a cross freference table which sayd the item you arre to repair, and a seperate row for each part serial number?

Sudonim,

The Part_no on the second page is tied to the Part_req on the first page.  The first page is an extract from the processing table, which identifies parts required to repair each serial number.  The second page is from the inventory table(stock on hand) and quantities currently on hand.  

I need to be able to change the issue to true on the first page where all parts are available and change the process to "M" which is our code for in work as well, then reduce inventory by the qty required

When all parts are not available to do the repair I need to flag the issue as false and change the process code to "G" which is a work stoppage due to lack of parts availability.

Thanks,

Jeff

FWIW, the "Link" between the InProcess sheet and the SOH sheet should be the SN field, not the "Part" Field.
This is because the SN is unique, the "Part" is not.

so you can use an inner join to link the two tables from soh.part_no to in_process.partreq in order to update in_process.process = m and issue = true but only when all of the conditions of the SOH list are met?

in_process denotes the parts required for each serial number?  so for example to repair a
"A146R00251" you need 1 housing and 1 shaft.

Do those items - the specific housing and shaft have their own part numbers which exist tied to the thing they are fixing and in your inventory.

just brainstorming here...depending on your level of access knowledge...

we need a query to identify the parts (and their qty) required to fix a single SN....then we need to cmpare each of those items with the current stock to make sure stock amt >= required....

the difficult bit is then to work out across that recordset wether all of the required stock parts are present..

I'd probably do this using a form, or a function.

I;'d have to see some more of the data and a full set of data for each correct and incorrect set of reqs so that we can work out the logic.

can you give me an example from your provided data set which meets either criteria?

in SOH - are there supposed to be part numbers of housing/shaft?  shouldn;t they match SN's?

I've added them to a DB, but i cannot see how to join the partno in SOH to the  SN or other field in in_process?

Jeff:  Re your post at http:#a35007700 - that's what I thought until I ran into the question below.  Sometimes that serialnumbered item comes back for rework, modification, or repair

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_26845300.html?cid=1131#a34988727.

Ray,

OK.

I'll let you guys take this from here...

;-)

Jeff

Sorry about that I see how I caused this confusion now.  I am reattaching the data.

The serial number is a unique identifier for the end item that we are repairing and ties back to a table that contains other information about the item we are repairing.  

The first page in the spreadsheet if a list of parts required to repair each serial number and the quantity required to do the repair.  There are actually about 600 parts that go into doing this repair so I have limited it to just a few of them

The third page....sorry for the duplicate entry.  The data for the part number is unique and shaft and housing are correct part numbers for this particular piece of equipment.  Some how I screwed up copying it and the housing was listed twice.  It should have been unique.

As we process this the list of serial numbers there are the ones remaining after all others have been moved to an awaiting parts status because they did not have 100% of the parts required for the repair.  

I need to be able to flag the parts to issue and reduce the quantities on hand as they are flagged to issue.  When the quantity is insufficient to repair an item I need to flag as dont issue any parts to that serial number and move on to the next and evaluate it against the new quantity for stock.

I hope that better explains it and sorry for the duplicate entry on the second page.

Semper Fi,

Jeff
example-of-data.xls

I have attached a simple process flow for what I am trying to obtain.  I have managed to get just about everything working.  I have also included how basically my data should look at the end of it as well.
example-of-data-END-RESULT.xls
simple.jpg

so your soh table with its 3 rows:

PART_NO      NOMENCLATURE      SOH
011351539      SPUR GEAR      0
HOUSING      HOUSING      4
SHAFT      SHAFT      6


Is actually representing:
Partno 011351539 / SPUR GEAR requires HOUSING Qty 4 and SHAFT Qty 6?

So the table should have 2 rows:
Item: 011351539 requires: HOUSING Qty 4
Item: 011351539 requires: shaft Qty 6

or should it have one row for each serial number required?  e.g.
Item: 011351539 requires: A14600043 Shaft Qty 1
Item: 011351539 requires: A146000631  Shaft Qty 1
Item: 011351539 requires: A146000631 Housing Qty 1
Item: 011351539 requires: A14600065  Housing Qty 1
Item: 011351539 requires: A146000681  Shaft Qty 1
Item: 011351539 requires: A146000681  Housing Qty 1
Item: 011351539 requires: A146000861  Shaft Qty 1
Item: 011351539 requires: A146000861  Housing Qty 1
Item: 011351539 requires: A14600148  Shaft Qty 1
Item: 011351539 requires: A14600221  Shaft Qty 1

Does each repair have a distinct list of the part numbers required? Are we going to need that to be stored in order to check a repair against available stock, and if all the item ( conditions) are met, then we can do the "simple" code to set the issue and process fields.

No the table that is stock on hand is the inventory we have to repair the serial numbers in question

first page of the spreadsheet is the parts requirements for each serial number

the items that need to be repaired are sn a166xxxx, the parts listed are the parts required i.e.

Serial number a14600043 only needs a shaft

serial number a146000631 requires a shaft and a housing

anything needing a spur gear has already been moved to the G pool because there are no spur gears on hand.

There is a table that contains a list of the serial numbers for the gear box and links to this.  Each serial number in that table is unique and part of a fixed population that we track throughout its life

Serial number a14600043 only needs a shaft

serial number a146000631 requires a shaft and a housing

So does the serial number appear in the soh table... and how does the stock required link to your actual stock table.

i just seem to be getting more and more confused.

Okay let me see if I can explain this better than I have with a bit more background and some clarifications.

There are several steps prior to this point and after.(this should make the spreadsheet referenced ID: 35012121) make more sense

We have a master table of all serial numbers for this component(gearbox)  and a by our model gearboxes pass or fail based on a probability algorithm.

Those that "Fail" have an algorithm run which determines probability of needing certain parts.  This generates the IN_PROCESS table that lists the serial number and its parts requirements.  

All gearboxes that need an item that shows with "0" qty in the SOH table are immediately moved to the G_QUE along with all parts requirements. Tht is why you do not see anything IN_PROCESS that requires a spur gear

I have that piece working no problem....

The next set of evaluations is where I need the help

Once we have moved anything to the G_QUE that needs in this case a spur gear(0 SOH) the remaining gearboxes and parts required are left since we have shafts and housings.  The SOH_TABLE is the inventory available to clear the in_process.  We need more shafts and housings than we have

I need to evaluate gearboxes, flag them to issue parts if all parts required have sufficient quantities   and REDUCE the SOH_TABLE SOH field by the quantity of items it takes to repair gearboxes until SOH is exhausted.  

All gearboxes that were flagged to issue parts to move to the M_QUE.  

I need to move the remainder of the gearboxes(the ones I did not have enough shafts and housings to repair over to the G_QUE as well so they can be reevaluated in the next loop for repair

When I am done I should have an in_process table that is empty and a G_QUE table like the one in the spreadsheet and an M_QUE table like the one in the spreadsheet.


Hopefully this makes things a little more clear.  My big problem is in getting the SOH Qty to reduce and flag for issue only if all parts are available for the repair  and then evaluate the next serial number

Thanks again for your help and patience

Semper Fi,

Jeff

fair enough.

Managed to figure it out