Ok, first of all I know very little about ms access so bare with me. Second of all, right now I am only interested in a general answer then I will ask more as I go if needed.

I am interested in using access to make an inventory list and an order page so that when you input things a quantity it automatically updates the inventory list and flags you when you hit a reorder level. Is this possible with access and for that matter easy to figure out with access? I understand I will need to learn how to manipulate the fields, I will figure all that out as I go. I will probably be using Access 2002 if that makes a difference.

Again, I don't need details right now just a quick answer. Thanks.

klmgb

Yes it is possible, you may want to go to the MS Office website, they have a lot of templates and you may find something that fits your needs

jduke44

Thanks for your quick reply. I did find a template, but it was very confusing how it is set so i wanted to try to start from scratch. Sometimes I am a glutton for punishment. I think I am having trouble learning the concept of how access works rather than the actualy creating, if that makes any sense. I wanted to make sure these things were possible before I rack my brain figuring all the details out. I will probably have more questions or break down and buy a book. Thanks again.

NeedKarma

The challenging aspect is the flagging part. I know a little Access but no VB and I'm not sure how that would work.

jduke44

So I would need VB you think to do the flagging part? I don't necessarily need to do that. It would be an added future. I guess I'll save that til last. Thanks Need.

ScottGem

No you wouldn't need VBA to do the flagging part. And I would suggest you start with a template and modify it.

The way to do the Reorder part is with a query/report. But an inventory application requires a special structure. Such an app centers around a transaction table where all movement of stock, both in and out, are recorded. To then calculate stock on hand you would add all incoming transactions and subtract all outgoing transactions.
You do that in a query then run another query comparing the results to your reorder levels.

jduke44

Thanks guys for your responses. As I said before, I think I was having trouble with the concept of MS Access rather than the building of a database. With your responses I am starting to understand this.

Scott, I will take your suggestion of a template and modify it. I think that would be easier. I have a co-worker that did a database to record our jobn failures and equipment failures so I have been disecting that to understand how everything relates. I am slowly getting it.

One thing I don't understand is the relationship of tables and how to use them. If I connect a field from one table to the same field in another table how does that relate. Another words, if I modify a field in one table will it automatically reflect the field in the other table it has a relationship with?

Thanks again for your help.

ScottGem

The concept of relational databases is both simple and complex. In its basic form its simple. The purpose is to reduce or eliminate redundant data. This means that data exists in ONE table only. So when data is updated, its updated in ONE place so the most current data can be reflected throughout the database.

The way this is accomplished is through a process called normalization and that's where it can get complex. I would suggest doing a search on database normalization. You will find several articles that can help you understand this crucial design concept.

I can give you some examples though. In your application you would have a Products table. It might look like this:

tblProducts
ProductID (Primary Key Autonumber)
SKU
Description
Units
UnitPrice

Now you also have your OrdersDetail table that has a field also called ProductID, but here its the Foreign key. A foreign key is just a field that contains the value of the PK of the related record. Now you don't need the SKU, Description or Units in your OrdersDetail because they are already in the Products table and can be pulled via a Join.

However, UnitPrice is an exception to this rule. Because prices can change, you need to capture the price at the time of the transaction. So you would have a UnitPrice field in the OrderDetails and you would populate it from the Products table.

But if you needed to change the description, you would do that in the Products table and it would be automatically reflected wherever you displayed that field.

jduke44

Scott, thanks for the quick and detailed response. I think I am understanding this more and more. Once I start doing this I know it'll make sense. I wil do the search you recommended also. I will post back with more questions as I go. Thanks again.

BTW - thanks to everyone else for their replies. This has helped me greatly.

ScottGem

I've been designing databaes for over 20 years now and working with Access for about 10 years. So if I can be if further assistance, just ask.