inventory auditing help needed.

[trode]

Hello all.
For the first time, our company might make use of the MS Access portion of
the office package. Problem is, I don't know it well enough to whip out a
desired program.

What we want to do is have two Tables. One table called PRODUCTS,
containing "partnumbers" with "descriptions". The other table called
"AUDITED PRODUCTS" that starts blank with the following fields:
"Tag_number", "Quantity","partnumber", and "description".

The goal is to manually audit our inventory and then enter the results into
a new database. We want to have a form that allows a data entry user to
type in the Tab number, the Quantity, the Partnumber, and the description
will automatically pull from the PRODUCT table in reference to the
partnumber entered. The FORM will then create a record of the entered info
into the AUDITED PRODUCTS table.
So far I've imported the PRODUCTS table and created a blank AUDITED PRODUCTS
table. Along with a simple form that will be used to build on the AUDITED
PRODUCTS table.
How do I make the form automatically pull the description from the PRODUCTS
table so that it is entered into the AUDITED PRODUCTS table?

BTW, sometimes the table might have a partnumber that is new an does not
exist in the PRODUCT table. Therefore the form needs to allow the user to
enter the description if necessary.

I'm betting that this is easy but, I was never given time to learn ACCESS.
Maybe this is the start of a new era for our company. Alright, don't laugh
to hard.

I look forward to any help you can offer.

-trode

[Phil Stanton]

I may be missing something, but why 2 tables?
It looks to me as if you should only have 1 table and either it has got a
Tab /Tag number and quantity or they are blank. Obviously you already have
the part number and description loaded to your table.

Phil

"trode" <as***@for.it> wrote in message
news:MQ*******************@news4.srv.hcvlny.cv.net ...

Hello all.
For the first time, our company might make use of the MS Access portion of
the office package. Problem is, I don't know it well enough to whip out a
desired program.

What we want to do is have two Tables. One table called PRODUCTS,
containing "partnumbers" with "descriptions". The other table called
"AUDITED PRODUCTS" that starts blank with the following fields:
"Tag_number", "Quantity","partnumber", and "description".

The goal is to manually audit our inventory and then enter the results into a new database. We want to have a form that allows a data entry user to
type in the Tab number, the Quantity, the Partnumber, and the description
will automatically pull from the PRODUCT table in reference to the
partnumber entered. The FORM will then create a record of the entered info into the AUDITED PRODUCTS table.
So far I've imported the PRODUCTS table and created a blank AUDITED PRODUCTS table. Along with a simple form that will be used to build on the AUDITED
PRODUCTS table.
How do I make the form automatically pull the description from the PRODUCTS table so that it is entered into the AUDITED PRODUCTS table?

BTW, sometimes the table might have a partnumber that is new an does not
exist in the PRODUCT table. Therefore the form needs to allow the user to
enter the description if necessary.

I'm betting that this is easy but, I was never given time to learn ACCESS. Maybe this is the start of a new era for our company. Alright, don't laugh to hard.

I look forward to any help you can offer.

-trode

[Keith Wilby]

"Phil Stanton" <di********@stantonfamily.co.uk> wrote:

I may be missing something, but why 2 tables?
It looks to me as if you should only have 1 table and either it has got
a Tab /Tag number and quantity or they are blank. Obviously you already
have the part number and description loaded to your table.

Also, to store the same data twice would be at odds with normalisation.
Investigating normalisation theory might be a good starting point.

Regards,
Keith.

[trode]

"Keith Wilby" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...

"Phil Stanton" <di********@stantonfamily.co.uk> wrote:

I may be missing something, but why 2 tables?
It looks to me as if you should only have 1 table and either it has got
a Tab /Tag number and quantity or they are blank. Obviously you already
have the part number and description loaded to your table.

Also, to store the same data twice would be at odds with normalisation.
Investigating normalisation theory might be a good starting point.

Regards,
Keith.

So if I remove the "description" row from the PRODUCTS AUDITED table and
make a relationship using partnumber I will overcome the reduncency of data
but what about the rest of my problem?
Please note, the reason for a seperate database is so that it will be
tracked by the tag numbers. Some Tabs will have the same products.
I am sorry if I am not explaing this well. Maybe this will help: The Tags
are basic manilla tags used to take inventory. Some of our inventory spans
across multiple locations in an aisle. Therefore there will be an instance
in which more than one tag will have the same product counted for. The
seperate database consisting of the tags will allow us to create forms
showing tag totals of a same product.

Is there a book or similar code I can anaylize? The wizard is not close
enough to work from. Maybe there is a book that I can understand quickly?

In any event I am greatfull for your help.

cheers,
trode

[Phil Stanton]

Ok then
You need 1 table Products with
ProductID AutoNumber Key
ProductPartNo Is this text or numeric? Indexed No Duplicates
ProductDescription Text

Second Table TagQuantities
TagID AutoNumber Key (not stricly neccessary, but use it just the
same)
ProductID Number Long Required = Yes
Position ???
Date ????
Quantity

Create a relationship between the 2 tables on ProductID
Then create a query QProducts based on Products with the ProductDescription
Assending
Create a form Products based on QProducts

Create a form SubTagQuantities based on TagQuantities
With the Products form in design view, drag the SubTagQuantities Form into
the Products Form
On the properties of the Sub Form you should see Link Child Fields and Link
master Fields. They should both have ProductID in it.

This should allow you to add or amend product details in the main form and
add the quantities in the Sub form.

Still havn't fathomed out what Tabs are. Got the Tags bit

Phil

"trode" <as***@for.it> wrote in message
news:%_*******************@news4.srv.hcvlny.cv.net ...

"Keith Wilby" <ke*********@AwayWithYerCrap.com> wrote in message
news:Xn************************@10.15.188.42...

"Phil Stanton" <di********@stantonfamily.co.uk> wrote:

I may be missing something, but why 2 tables?
It looks to me as if you should only have 1 table and either it has got a Tab /Tag number and quantity or they are blank. Obviously you already have the part number and description loaded to your table.
Also, to store the same data twice would be at odds with normalisation.
Investigating normalisation theory might be a good starting point.

Regards,
Keith.

So if I remove the "description" row from the PRODUCTS AUDITED table and
make a relationship using partnumber I will overcome the reduncency of

data but what about the rest of my problem?
Please note, the reason for a seperate database is so that it will be
tracked by the tag numbers. Some Tabs will have the same products.
I am sorry if I am not explaing this well. Maybe this will help: The Tags are basic manilla tags used to take inventory. Some of our inventory spans across multiple locations in an aisle. Therefore there will be an instance in which more than one tag will have the same product counted for. The
seperate database consisting of the tags will allow us to create forms
showing tag totals of a same product.

Is there a book or similar code I can anaylize? The wizard is not close
enough to work from. Maybe there is a book that I can understand quickly?

In any event I am greatfull for your help.

cheers,
trode

[Allen Browne]

Phil, not sure if this will be useful for your particular question, but if
you are looking for a general guidance on the issue of products, sales,
acquisitions, stocktakes, and calculating how many are on hand, this article
may be useful:
Inventory Control: Quantity on Hand
at:
http://allenbrowne.com/AppInventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"trode" <as***@for.it> wrote in message
news:%_*******************@news4.srv.hcvlny.cv.net ...

Is there a book or similar code I can anaylize? The wizard is not close
enough to work from. Maybe there is a book that I can understand quickly?