Solved

reduce inventory based on needs

Posted on 2011-02-28
17
932 Views
Last Modified: 2012-05-11
Good afternoon,

I am hoping I am just being slow today....

What I need to do is evaluate my current stock on hand against the parts needed to repair an item.  If all of the parts exist I need to reduce the inventory and change the process code on the item so that it can go to the repair location.  If there is not enough inventory for the repair I need it to flag it with a different process code and go on to the next serial number.  If someone can give me a starting point to it I will try and take it from there.  

It is in access 2007.  I have attached a spreadsheet with samples of the 2 tables involved.  The inventory table and the table listing what parts each serial number is waiting for.

Thank you all for your time.  I will respond with any clarifications needed.

Semper Fi,

Jeff
example-of-data.xls
0
Comment
Question by:Kenpachi
  • 8
  • 6
  • 2
  • +1
17 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35005606
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?
0
 

Author Comment

by:Kenpachi
ID: 35006047
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
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35007700
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.
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35007899
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?
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35007940
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?
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35008914
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.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 35009529
Ray,

OK.

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

;-)

Jeff
0
 

Author Comment

by:Kenpachi
ID: 35011633
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
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:Kenpachi
ID: 35012121
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
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35015224
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.





0
 

Author Comment

by:Kenpachi
ID: 35016372
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
0
 

Author Comment

by:Kenpachi
ID: 35016398
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
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35017606
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.
0
 

Author Comment

by:Kenpachi
ID: 35020077
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
0
 

Accepted Solution

by:
Kenpachi earned 0 total points
ID: 35035308
Thanks folks I thing I got it I started by writing a loop to load each serial number into a variable a processed out until the count of serial numbers left in the in process que was zero and then I could work them out one at a time and then I could manage the stock on hand down for multiple items at once.  

Thanks for the time on this.

Semper Fi

Jeff
0
 
LVL 15

Expert Comment

by:Simon Ball
ID: 35035363
fair enough.
0
 

Author Closing Comment

by:Kenpachi
ID: 35081135
Managed to figure it out
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now