parts inventory management

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to be able to keep up with parts on my husbands truck..(or he does).
I have already taken care of entering parts numbers, parts discription, &
notes. I need to have a column for parts on hand, that will update
automatically when numbers are entered in the columns for parts in and parts
out. I have the information entered into both Access and Excel. I've
contacted people I use to work with who did training on both Access and Excel
and they can't help me. I know there has to be a way to do this without each
part having it's own ledger sheet. Please HELP
 
It sounds like you're more familiar with Excel than Access. If you need to
keep a history of parts in and parts out, you probably want to use Access;
if all you need to do is keep track of what's on hand, you could probably
use Excel. So the question is, how much do you need this to do?

If you're going to use Access, there are a lot of issues to consider - the
underlying tables you need, how they're related, and the forms you'll need
to manipulate the data. You say you've already entered the information into
Access, but you don't say just how. Did you create a data table? If so, what
is its structure? Do you have just the one table or are there more? Did you
create a form based on the table, or did you enter the information directly
into the table?

Carl Rapson
 
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblJob
JobID
etc

TblJobDetail
JobDetailID
JobID
PartID
PartOutQty

TblSupplier
SupplierID
SupplierName
etc

TblPurchaseOrder
PurchaseOrderID
PurchaseOrderNum
PurchaseOrderDate
etc

TblPurchaseOrderDetail
PurchaseOrderDetailID
PurchaseOrderID
PartID
PartInQty

You need a form/subform for entering purchases of parts. The main form needs
to be based on
TblPurchaseOrder and the subform needs to be based on
TblPurchaseOrderDetail.
You need a form/subform for entering the use of parts on jobs. The main form
needs to be based on
TblJob and the subform needs to be based on TblJobDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblPurchaseOrderDetail and TblJobDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)
 
You might also want to look over the MS template for inventory management.
It might save you a lot of work depending on the complexity of your needs.
--
Hope this helps,

Daniel P





Steve said:
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblJob
JobID
etc

TblJobDetail
JobDetailID
JobID
PartID
PartOutQty

TblSupplier
SupplierID
SupplierName
etc

TblPurchaseOrder
PurchaseOrderID
PurchaseOrderNum
PurchaseOrderDate
etc

TblPurchaseOrderDetail
PurchaseOrderDetailID
PurchaseOrderID
PartID
PartInQty

You need a form/subform for entering purchases of parts. The main form needs
to be based on
TblPurchaseOrder and the subform needs to be based on
TblPurchaseOrderDetail.
You need a form/subform for entering the use of parts on jobs. The main form
needs to be based on
TblJob and the subform needs to be based on TblJobDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblPurchaseOrderDetail and TblJobDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Nita M. said:
I need to be able to keep up with parts on my husbands truck..(or he does).
I have already taken care of entering parts numbers, parts discription, &
notes. I need to have a column for parts on hand, that will update
automatically when numbers are entered in the columns for parts in and
parts
out. I have the information entered into both Access and Excel. I've
contacted people I use to work with who did training on both Access and
Excel
and they can't help me. I know there has to be a way to do this without
each
part having it's own ledger sheet. Please HELP
Click to expand...
Click to expand...
 
First, thank you for your response. I am a little more comfortable with
Excel since I have worked with it a lot longer. I'll try to answer all of
your questions.

How much do I need this to do?
I need to know how many of each part is on his truck

Did I create a data table..
I first entered the information into an Excel table with the columns....part
number,
part description, notes, number on hand, beginning inventory, inventory out,
inventory in. Of course the simple formula p=x-y+z worked for the first
addition, subtraction, but that was the only one. I imported the information
into a data table in Access

I just want to be able to keep track of the parts on hand for him, whether
it's in Excel or in Access. He doesn't have to buy the parts, or keep track
of PO's, vendors info, or job info that information is kept by the company he
works for. He just has to be able to give them what they refer to as "a
truck inventory" of how many of each part is on his truck.

Again, thank your for your response. It sounds like I'm going to need for
you to keep it simple for me............it's clear to me that I'm no where
near as knowledgeable as you are.
 
Thank you Daniel for responding. The info that I need on this inventory
isn't nearly as detailed (hope I said that right). The company that my
husband works for keeps track of PO's and the related information. My
husband doesn't buy the parts, he just needs to be able to provide what his
company refers to as "a truck inventory" which consists of the number on hand
of each part. I checked the MS inventory template, but either I missed the
one I need, or didn't know how to adapt it for this need. After reading the
responses, I think I'm definitely out of my league on here. I originally set
up the table in Excel with the columns parts number, parts description,
notes, beginning inventory, inventory on hand, inventory out, inventory in
and tried to use the formula of parts on hand = beginning inventory -
inventory out + inventory in........of course that worked for the first
calculation and was useless after that. I tried everything I could think of
which did no good.

Again, thank yor for your response.

Daniel said:
You might also want to look over the MS template for inventory management.
It might save you a lot of work depending on the complexity of your needs.
--
Hope this helps,

Daniel P





Steve said:
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblJob
JobID
etc

TblJobDetail
JobDetailID
JobID
PartID
PartOutQty

TblSupplier
SupplierID
SupplierName
etc

TblPurchaseOrder
PurchaseOrderID
PurchaseOrderNum
PurchaseOrderDate
etc

TblPurchaseOrderDetail
PurchaseOrderDetailID
PurchaseOrderID
PartID
PartInQty

You need a form/subform for entering purchases of parts. The main form needs
to be based on
TblPurchaseOrder and the subform needs to be based on
TblPurchaseOrderDetail.
You need a form/subform for entering the use of parts on jobs. The main form
needs to be based on
TblJob and the subform needs to be based on TblJobDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblPurchaseOrderDetail and TblJobDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Nita M. said:
I need to be able to keep up with parts on my husbands truck..(or he does).
I have already taken care of entering parts numbers, parts discription, &
notes. I need to have a column for parts on hand, that will update
automatically when numbers are entered in the columns for parts in and
parts
out. I have the information entered into both Access and Excel. I've
contacted people I use to work with who did training on both Access and
Excel
and they can't help me. I know there has to be a way to do this without
each
part having it's own ledger sheet. Please HELP
Click to expand...
Click to expand...
Click to expand...
 
Steve, thank your for your response. After reading your response, I don't
think what I need is as detailed. My husband doesn't buy any of the parts,
he just has to be able to give his company what they refer to as a "truck
inventory" which is basically the number of parts on hand. I entered the
info into an Excel table with the following columns: parts number, parts
description, notes, inventory on hand, inventory out and inventory in. I
tried a formula that worked great.....for the first time, but was useless
after that.

Steve said:
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblJob
JobID
etc

TblJobDetail
JobDetailID
JobID
PartID
PartOutQty

TblSupplier
SupplierID
SupplierName
etc

TblPurchaseOrder
PurchaseOrderID
PurchaseOrderNum
PurchaseOrderDate
etc

TblPurchaseOrderDetail
PurchaseOrderDetailID
PurchaseOrderID
PartID
PartInQty

You need a form/subform for entering purchases of parts. The main form needs
to be based on
TblPurchaseOrder and the subform needs to be based on
TblPurchaseOrderDetail.
You need a form/subform for entering the use of parts on jobs. The main form
needs to be based on
TblJob and the subform needs to be based on TblJobDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblPurchaseOrderDetail and TblJobDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Nita M. said:
I need to be able to keep up with parts on my husbands truck..(or he does).
I have already taken care of entering parts numbers, parts discription, &
notes. I need to have a column for parts on hand, that will update
automatically when numbers are entered in the columns for parts in and
parts
out. I have the information entered into both Access and Excel. I've
contacted people I use to work with who did training on both Access and
Excel
and they can't help me. I know there has to be a way to do this without
each
part having it's own ledger sheet. Please HELP
Click to expand...
Click to expand...
 
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblPartUse
PartUseID
PartUseDate

TblPartUseDetail
PartUseDetailID
PartUseID
PartID
PartOutQty

TblRestock
RestockID
RestockDate

TblRestockDetail
RestockDetailID
RestockID
PartID
PartInQty

You need a form/subform for entering restocking of parts. The main form
needs
to be based on TblRestock and the subform needs to be based on
TblRestockDetail.
You need a form/subform for entering the use of parts. The main form
needs to be based on TblPartUse and the subform needs to be based on
TblPartUseDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblRestockDetail and TblPartUseDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)

Nita M. said:
Steve, thank your for your response. After reading your response, I don't
think what I need is as detailed. My husband doesn't buy any of the
parts,
he just has to be able to give his company what they refer to as a "truck
inventory" which is basically the number of parts on hand. I entered the
info into an Excel table with the following columns: parts number, parts
description, notes, inventory on hand, inventory out and inventory in. I
tried a formula that worked great.....for the first time, but was useless
after that.

Steve said:
You need the following tables in your database:
TblPart
PartID
PartNum
PartDesc
PartNote

TblJob
JobID
etc

TblJobDetail
JobDetailID
JobID
PartID
PartOutQty

TblSupplier
SupplierID
SupplierName
etc

TblPurchaseOrder
PurchaseOrderID
PurchaseOrderNum
PurchaseOrderDate
etc

TblPurchaseOrderDetail
PurchaseOrderDetailID
PurchaseOrderID
PartID
PartInQty

You need a form/subform for entering purchases of parts. The main form
needs
to be based on
TblPurchaseOrder and the subform needs to be based on
TblPurchaseOrderDetail.
You need a form/subform for entering the use of parts on jobs. The main
form
needs to be based on
TblJob and the subform needs to be based on TblJobDetail.

You then need a part inventory form. This form would be a continuous form
based on a query that included
TblPart, TblPurchaseOrderDetail and TblJobDetail. The query would include
the fields
PartID, PartNum, PartDesc, PartInQty and PartOutQty. You would make this
query a Totals query by clicking on the Sigma (looks like a capital E)
button in the menu at the top. You then need to add a calculated field to
the query that looks like:
PartInventory = [SumPartInQty] - [SumPartOutQty].
Set the sort for PartNum ascending.

Your Inventory form would include the fields PartNum, PartDesc and
PartInventory.

If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could
begin
using this system in a very short period of time.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)




Nita M. said:
I need to be able to keep up with parts on my husbands truck..(or he
does).
I have already taken care of entering parts numbers, parts discription,
&
notes. I need to have a column for parts on hand, that will update
automatically when numbers are entered in the columns for parts in and
parts
out. I have the information entered into both Access and Excel. I've
contacted people I use to work with who did training on both Access and
Excel
and they can't help me. I know there has to be a way to do this
without
each
part having it's own ledger sheet. Please HELP
Click to expand...
Click to expand...
Click to expand...
 
Steve said:
If you need help setting this up, I can set it up for you for a very
reasonable fee. I could incorporate what you already have so you could begin
using this system in a very short period of time.
Click to expand...

This is the second time in the same thread that you are advertising your services .... Arghhhhh
We asked you before, and before, and before....
We will *not* ask you again and again and again...

To the original poster:
Beware of his guy !!
Most people here have a common belief that the newsgroups are for *free exchange of information*.
But Steve is a notorious job hunter in these groups, always trying to sell his services.

Before you intend to do business with him look at:
http://home.tiscali.nl/arracom/whoissteve.html

Arno R
 
If you don't need a history of parts in and out (with dates, etc) and only
need a current count, you can do it with a fairly simple Access application.
You could also do it with Excel, but I'm not as skilled with Excel as I am
with Access; you could try asking your questions in an Excel newsgroup if
you want to stick with Excel. One benefit to Access would be that if you
wish to expand the database functionality in the future, it will be easier
if you're already in Access. You'll have to decide which is better for you.

If you already have your Parts table set up with the fields you need, then
all you need to do is create a form based on that table. Use the form wizard
to create the form, then rearrange the controls the way you want. For the
parts in/out, you could add to the form another textbox control (which won't
be bound to any field in your table) and a command button (if the Command
Button Wizard pops up, just cancel it). The textbox will be for entering the
number in (positive) or out (negative). The command button will perform the
calculation. In design view select the command button and open its
Properties window. On the Event tab, select [Event Procedure] for the On
Click event, and then click on the button with the three dots next to it.
When the Code window opens, add code something like this:

Me.Parts_On_Hand = Me.Parts_On_Hand + Nz(Me.txtPartsInOut,0)

This assumes Parts_On_Hand is the name of the field in your table that
contains the number of parts on hand, and txtPartsInOut is the name of the
textbox control you added to the form. You can name this textbox (and the
command button too) anything you want.

If you go this route, you'll probably also want to add some error checking,
especially on the textbox control to make sure a valid number value is
entered. You can post again (I'd suggest the formscoding group) if you have
questions about VBA code, error checking, etc.

HTH,

Carl Rapson
 
Back
Top