Design issues

Hi All,

I wanted some help and advise on my existing database table structure and
would appreciate some inputs to acheive normalization.

currently i have 2 tables with data structure as listed below:

product table:

itemno - pk
itemdesc - description
uom- unit of measure
projectno-project no
catid-catalog id
stockno
po_no - purchase order no.


stocktransaction

id-pk
stockitemno-fk
dor- date of receipt of item
UnitsReceived
location- stock location
UnitsUsed
doi- date of issue
mpi
issuedto


I am calculating the items received and issued and keeping track of the qty
on hand for each item. Each item was received by a single pruchase order
which was unique but now the scenario is the purchae order can vary for the
same item and I have to track by po_no the items that were received. Right
now i am using a single form to input the po_no qty received and another form
for qty issued and tracking them.

If i have to track the po_no do i create another table and use another form.
I am quite new to access and require some guidance.

thanks

Vandy

(see comments in-line below)

"vandy" wrote in message
...
Hi All,

I wanted some help and advise on my existing database table structure and
would appreciate some inputs to acheive normalization.

currently i have 2 tables with data structure as listed below:

product table:

itemno - pk
itemdesc - description
uom- unit of measure
projectno-project no
catid-catalog id
stockno
po_no - purchase order no.

I can see how a description and unit of measure are related to an item or
product, and probably a CatalogID and StockNo. But if you are trying to
normalize your data structure, I'd question how a ProjectNo or a PONo
describes any attribute of a product.

By the way, if those are the actual names of the fields, Access will get
confused by the spaces in the names. If you want to use multiple words as
fieldnames (and that's a perfectly acceptable naming practice), consider
leaving out all spaces. You could use underscores ("_") in their places, or
you could use some variation on CamelCase (run words together, capitalizing
each word).



stocktransaction

id-pk
stockitemno-fk
dor- date of receipt of item
UnitsReceived
location- stock location
UnitsUsed
doi- date of issue
mpi
issuedto


I'm not sure what a [stockitemno] is.

Since you are storing [location], can I assume that an item/product can be
moved? Can be stored in more than one location simultaneously (i.e., the
batch is split and stored in two places)?

I don't know what [UnitsUsed] represents. And "when" were they "used"? And
what happened to them?

[Date of issue] ... what does "issue" mean?

[mpi] ... ??

[IssuedTo] ... is this a person, a department, a customer, ...?

I am calculating the items received and issued and keeping track of the
qty
on hand for each item. Each item was received by a single pruchase order
which was unique but now the scenario is the purchae order can vary for
the
same item and I have to track by po_no the items that were received. Right
now i am using a single form to input the po_no qty received and another
form
for qty issued and tracking them.

If i have to track the po_no do i create another table and use another
form.
I am quite new to access and require some guidance.

thanks

I'd suggest shutting off the computer and using paper/pencil to map out the
things about which you wish to keep information. The "things" are your
entities. The information you keep is the entity's attributes. Then
determine how the entities are related to each other.

Have you searched on-line for Inventory systems?

Have you checked at the Microsoft site for templates?

Have you looked at the Northwind database that comes as a sample with MS
Access?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi Jeff,
Thanks for your suggestions on naming the fields. I will follow that. I am
looking into some templates database designs and hope to come up with a
better table structure. Thanks for pointing me in the right direction.


"Jeff Boyce" wrote:

Vandy

(see comments in-line below)

"vandy" wrote in message
...
Hi All,

I wanted some help and advise on my existing database table structure and
would appreciate some inputs to acheive normalization.

currently i have 2 tables with data structure as listed below:

product table:

itemno - pk
itemdesc - description
uom- unit of measure
projectno-project no
catid-catalog id
stockno
po_no - purchase order no.

I can see how a description and unit of measure are related to an item or
product, and probably a CatalogID and StockNo. But if you are trying to
normalize your data structure, I'd question how a ProjectNo or a PONo
describes any attribute of a product.

By the way, if those are the actual names of the fields, Access will get
confused by the spaces in the names. If you want to use multiple words as
fieldnames (and that's a perfectly acceptable naming practice), consider
leaving out all spaces. You could use underscores ("_") in their places, or
you could use some variation on CamelCase (run words together, capitalizing
each word).



stocktransaction

id-pk
stockitemno-fk
dor- date of receipt of item
UnitsReceived
location- stock location
UnitsUsed
doi- date of issue
mpi
issuedto


I'm not sure what a [stockitemno] is.

Since you are storing [location], can I assume that an item/product can be
moved? Can be stored in more than one location simultaneously (i.e., the
batch is split and stored in two places)?

I don't know what [UnitsUsed] represents. And "when" were they "used"? And
what happened to them?

[Date of issue] ... what does "issue" mean?

[mpi] ... ??

[IssuedTo] ... is this a person, a department, a customer, ...?

I am calculating the items received and issued and keeping track of the
qty
on hand for each item. Each item was received by a single pruchase order
which was unique but now the scenario is the purchae order can vary for
the
same item and I have to track by po_no the items that were received. Right
now i am using a single form to input the po_no qty received and another
form
for qty issued and tracking them.

If i have to track the po_no do i create another table and use another
form.
I am quite new to access and require some guidance.

thanks

I'd suggest shutting off the computer and using paper/pencil to map out the
things about which you wish to keep information. The "things" are your
entities. The information you keep is the entity's attributes. Then
determine how the entities are related to each other.

Have you searched on-line for Inventory systems?

Have you checked at the Microsoft site for templates?

Have you looked at the Northwind database that comes as a sample with MS
Access?

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP