need help setting up inventory database

[LeanE]

I need to create an inventory database that will track customer owned inventory as it is received stored serviced and sent back to my customer. I am in the oil and gas industry and store large amounts of customer owned pipe. many joints will have the same part number but each joint is one of a kind in that it has a joint,heat, lot, and batch number. here is an example,

Job Name: Shell ex
Part Number: 129001
Quantity: 1000

Here is an example of joint info I need to record

Joint Number:193
Heat Number: f21283
Lot: 202
Batch:12j22
Reference: byy2112
Tally:33.2

Each one of the thousand joints will have a combination of this information making it one of a kind.

My system needs to allow my users to receive jobs, record where we store the pipe in a rack location, pull the specific joints out of inventory as the customer calls for them. We will receive 1000 at a time but the customer calls for portions of that job over long periods of time.. Any help would be greatly appreciated.

 

[CJ_London]

Your descriptions are not clear so it is difficult to advise:

the terms are very specific to your industry, but they mean nothing to anyone else

In the data you need to record I see no mention of rack location or part number but they are mentioned in your text.

It is also not clear whether the data you describe relates to 1 joint or 1000, or whether that particular description might relate to 100 joints and there are other variations for the remaining 900.

You say

each joint is one of a kind in that it has a joint,heat, lot, and batch number

So how is that relevant? You might as well say I build cars that are one of a kind in that they have wheels, doors and an engine

Hopefully you can see how we are struggling to understand

I suggest you map out some simple examples so that we can get a feel for the data - perhaps upload a screenshot from excel

 

[spikepl]

For inventories check this: http://allenbrowne.com/appinventory.html

Like in your bank statements, you store transactions(movements) in and out.

You need to clarify more about the workflow - how is a quantity ordered, what happens then, what exactly is "customer call" relative to your inventory, how do you return them.

As to the db itself, you may want to work through this http://www.rogersaccesslibrary.com/Tutorials/Entity_Relationship.zip

 

[RainLover]

How clear is your idea of what is what.

e.g. Where does Quantity come from.

You need to gather and assemble all the field names and the tables to which they belong as your first task.

Also read up on Normalisation.

 

[LeanE]

Thank you all for your responses. Below is a sample of information from 2 jobs on an excel type spread sheet. each line represents 1 joint.

So for this example, I have got 2 jobs Shell ex, and Exxon W1
2 part numbers 129001, and 10221.
10 total joints of pipe.
The combination of joint heat lot batch ref. Creates a 1 of a kind item.
It is extremely relevant that each joint is one of a kind. I have to be able to distinguish which joint is which where it is on my yard at all times, when I received it when I loaded it out.
These joints are for deep water projects which is required full traceability for all parts. This means that in the event a part fails they can trace it back to the manufacturers, to the machine that built the part, to the day and hour, to the person operating that machine, all the way to when the metal was pulled from the earth.

Job ______PartNum___Joint___Heat___Lot___Batch____Ref._____Tally__Rack_received
Shell ex____129001___193____F213___202___12j22____Byy2112__33.2__A001_11/5/2014
Shell ex____129001___193____F412___202___13j10____Byy2112__33.2__A001_11/5/2014
Shell ex____129001___111____F213___202___12j22____Byy2112__33.2__A001_10/5/2014
Shell ex____129001___203____F213___201___13j21____Byy2112__33.2__B001_5/05/2014
Shell ex____129001___111____F686___102___16j11____Byy2112__33.2__B001_1/05/2014
Exxon W1__100221___108_________________1D21____1H33H12__38.5__C001_1/18/2014
Exxon W1__100221___252_________________1D21____1H33H12__38.5__C003_5/15/2014
Exxon W1__100221___001_________________2X23____1W24X12__38.5__C003_5/15/2014
Exxon W1__100221___001_________________2X24____1Q34W12__38.5__C003_5/15/2014
Exxon W1__100221___099_________________1D21____1H33H12__38.5__C001_1/09/2014


I Know this is not the way to set up tables for Access. But this is how I have it in excel.

If this isn't clear enough please let me know I will try something else.

Thanks again.

 

[LeanE]

Like in your bank statements, you store transactions(movements) in and out.

You need to clarify more about the workflow - how is a quantity ordered, what happens then, what exactly is "customer call" relative to your inventory, how do you return them.

Customer calls us to sort through pipe, itemize, inspect, tally, and store the equipment.

We unload the pipe lay it out and record the information in my spreadsheet. we inspect each joint and record the tally, once completed we move the joints to a rack location for storage. I then send a Tally Report. which is an itemized summary of the job to the customer.

Months later the customer will call and say I need 20 Joints of Shell Ex pipe. or they may say I need 20 joints of Part number 129001.(Same Question)
we pull the 20 joints, record exactly which joints were pulled, create a delivery ticket and ship the pipe.

The customer will also call and ask for things such as
How many joints of shell ex pipe do we have left at your yard?
Can you provide copies of the delivery tickets for all shell ex deliveries?
When did you ship shell ex joint number 1 heat number h123 lot number 232.....?
Can you pull shell ex joint numbers 1, 21, 23, 454, we are sending someone to pick them up?

 

[spikepl]

Customer calls us to sort through pipe, itemize, inspect, tally, and store the equipment.

Really? He really rattles off by phone each required part number? I dunno so I ask...

In your narrative we also discover a "delivery ticket". What other items of this sort do you have?

I honestly think you need to map this entire thing before thinking about coding anything. You have a number of objects, a number of workflows that generate data at some steps, slips, lists etc,. A data flow diagram could be nice in fact to get an overview. If that makes no sense to hyou do not worry, but try to work through the link. What we need to identify are the items you have and what happens to them, the data about them, and what /how any change is triggered.

Your business does not sound like a shoestring op, so you may want to pay someone to either sort out the basic requirements or even write the thing for you. This under the assumption that you did a search for commercial products managing pipe-bits and rejected the results.

Out of curiosity - is data enetred manually or do you have any barcodes/QR/RFID on your equipment

 

[jdraw]

LeanE,

Based on your latest post it appears you have these things, and readers are not necessarily familiar with any of them. It would be helpful to readers, and you, if we had a plain English description for each.

Customer  
pipe, (itemize, inspect, tally, and store the equipment.)
 joint 
 rack location 
 storage
 job 
 Shell Ex pipe 
 joints of Part number 129001 
  delivery ticket 
  ship the pipe.
yard
delivery
joint number 
 heat number h123 
 lot number 232.

Part of this is jargon which we don't understand, so plain English is key.

Those were the things involved, but only you know how these things fit together, and that is where relationships fit.

How many "joints" can there be on 1 piece of Shell ex pipe?
What exactly does x number of Joints of Part number ZZZZ really mean?
How many different Parts do/could you have?
How many pipes can be on a Rack? How many Locations?
How does a delivery occur - who does what to what?
Where does a delivery ticket fit and what info does a delivery ticket contain?
How do heat numbers and lot numbers get assigned?
What is involved in a Lot?

These are just some of the basic questions that jump out for me based on your post.

We're here to help/advise, but you can see we need more info.

Good luck.

 

[spikepl]

I see "inspect" - does that result in a recording/signature somewhere?
"stored" - so we also have designated storage locations like in any warehouse?
The incoming/outgoing tally is presumably also recorded and signed for?

Ideally you'd describe the entire process as is, then recognizing that some bits of it are dictated by physical paper, one would break it down into the necessary ingredientsand the n reconstruct the thing as made for an application....recognising that revolutionalising or changing the process too much is probably not doable.

 

[LeanE]

Really? He really rattles off by phone each required part number? I dunno so I ask...

In your narrative we also discover a "delivery ticket". What other items of this sort do you have?

I honestly think you need to map this entire thing before thinking about coding anything. You have a number of objects, a number of workflows that generate data at some steps, slips, lists etc,. A data flow diagram could be nice in fact to get an overview. If that makes no sense to hyou do not worry, but try to work through the link. What we need to identify are the items you have and what happens to them, the data about them, and what /how any change is triggered.

Your business does not sound like a shoestring op, so you may want to pay someone to either sort out the basic requirements or even write the thing for you. This under the assumption that you did a search for commercial products managing pipe-bits and rejected the results.

Out of curiosity - is data enetred manually or do you have any barcodes/QR/RFID on your equipment

No he doesn't rattle off part numbers in that way... Think of the part number as the same as job name. They are umbrellas for the whole group of line items. the line item is each joint of pipe.

So when the customer calls he will say "I am sending you 1,000 joints, part number 10002121" I will say great, what is the job name" he will say "Cheveron well2"

So now I have this information
Job name: Chevron well2
Part number: 10002121
Quantity:1,000

Now when the pipe arrives I can begin to itemize the job.


to answer some of your other questions. I have spent the last 2 years going to confrences, calling inventory software companies, pipe inventory software companies. There is nothing out there that tailors to my needs. Which is why im creating my own.

Yes the end game would allow for my field guys to scan barcodes on joints and pull those items onto a delivery ticket. I left information such as this out because I figured I could get by with just the basic database first and add this later.

I will begin writing down a list of all the reports and functions I need it to do and post on here when im done.

 

[spikepl]

I smell an opportunity for a trade here, which with luck might generate some business for me and some support for you. Let's chat briefly - phone or skype. I'm in a GMT+1 time zone (for the time being; San Serriffe is a bit unsteady in this respect), but can basically talk from 10 to 17 or 21-23, GMT +1. If interested PM me.

BTW : I'm an engineer so I know what a PI-diagram is.