simple parts inventory

[tbogy]

Hello,
I am kinda new at access. I am trying to build a database for my maint.
parts inventory. I have searched the web for samples or templates and there
is nothing out there that will work. I have looked at the templates that
come with microsoft and they do not fit.
What I need is a simple database that tracks by part number, all it needs to
do is track Qty on Hand, Qty In and Qty Out. I have a few items that are on
consignment but that will be an easy fix. Does anyone have a database of
this type that they would like to share or does anyone know where I could
find one.
If not, can anyone help with writting codes to make what I have work??
Please help...

 

[Jason Lepack]

Essentially you need a table of items and a table for transactions (in
and out)

item:
item_id - autonumber
item_name - text

transaction:
item_id - number - FK ref. item.item_id
trans_date - date/time
quantity - number

Whenever inventory comes in, ex. we receive 2000 of Item 1. There
would be a new record in transaction with the values:
(1, 10/22/2007 1:23 PM, 2000)

Now if we issue 100 of item 1 then there would be another record:
(1, 10/22/2007 1:24 PM, -100)

To see the current balance, a query is used:
select
i.item_id, i.item_name, sum(t.quantity) as balance
from
inventory as i
inner join transaction as t
on t.item_id = i.item_id

Cheers,
Jason Lepack

 

[tbogy]

Thanks.
Here is what I have so far. It may be that I need to start over.

Tbl Parts List:
Category
Manufacturer
PartNumber
QuantityOnHand
QuantityUsed
QuantityReceived

Forms QuanityReceived
PartNumber
QuantityReceived

Quantity Issued
PartNumber
QuantityIssued

I have set up the switchboard to open on startup and go to the various forms
or to the table to edit parts information.

Am I headed in the right direction. I have read that VBS code is where I
need to do the calculations.
Thanks

Essentially you need a table of items and a table for transactions (in
and out)

item:
item_id - autonumber
item_name - text

transaction:
item_id - number - FK ref. item.item_id
trans_date - date/time
quantity - number

Whenever inventory comes in, ex. we receive 2000 of Item 1. There
would be a new record in transaction with the values:
(1, 10/22/2007 1:23 PM, 2000)

Now if we issue 100 of item 1 then there would be another record:
(1, 10/22/2007 1:24 PM, -100)

To see the current balance, a query is used:
select
i.item_id, i.item_name, sum(t.quantity) as balance
from
inventory as i
inner join transaction as t
on t.item_id = i.item_id

Cheers,
Jason Lepack

Hello,
I am kinda new at access. I am trying to build a database for my maint.

[quoted text clipped - 8 lines]

If not, can anyone help with writting codes to make what I have work??
Please help...

 

[Jason Lepack]

Tbogy,

I would not design this in the method that you are.

You are relying on forms and VBA to perform the function of tables and
queries.

your method will work, but you are essentially storing the same data
in two different tables. My one table (transaction) performs the
function of your Quantity Received and Quantity issued.

All that is required for the problem that you have shown is two
tables, two forms, one query and possibly a report.

Cheers,
Jason Lepack

Thanks.
Here is what I have so far. It may be that I need to start over.

Tbl Parts List:
Category
Manufacturer
PartNumber
QuantityOnHand
QuantityUsed
QuantityReceived

Forms QuanityReceived
PartNumber
QuantityReceived

Quantity Issued
PartNumber
QuantityIssued

I have set up the switchboard to open on startup and go to the various forms
or to the table to edit parts information.

Am I headed in the right direction. I have read that VBS code is where I
need to do the calculations.
Thanks

Essentially you need a table of items and a table for transactions (in
and out)

item:
item_id - autonumber
item_name - text

transaction:
item_id - number - FK ref. item.item_id
trans_date - date/time
quantity - number

Whenever inventory comes in, ex. we receive 2000 of Item 1. There
would be a new record in transaction with the values:
(1, 10/22/2007 1:23 PM, 2000)

Now if we issue 100 of item 1 then there would be another record:
(1, 10/22/2007 1:24 PM, -100)

To see the current balance, a query is used:
select
i.item_id, i.item_name, sum(t.quantity) as balance
from
inventory as i
inner join transaction as t
on t.item_id = i.item_id

Cheers,
Jason Lepack

Hello,
I am kinda new at access. I am trying to build a database for my maint.

[quoted text clipped - 8 lines]

If not, can anyone help with writting codes to make what I have work??
Please help...- Hide quoted text -

- Show quoted text -

 

[tbogy]

Thanks.
I will start over with your suggestions and let you know how it goes.
Thanks again

Tbogy,

I would not design this in the method that you are.

You are relying on forms and VBA to perform the function of tables and
queries.

your method will work, but you are essentially storing the same data
in two different tables. My one table (transaction) performs the
function of your Quantity Received and Quantity issued.

All that is required for the problem that you have shown is two
tables, two forms, one query and possibly a report.

Cheers,
Jason Lepack

Thanks.
Here is what I have so far. It may be that I need to start over.

[quoted text clipped - 59 lines]

- Show quoted text -