New at this- HELP!

[BrianCapps]

Hi all! I am new to this forum as well as MS Access. I have a few questions for you involving my new database program for my company.

I need to attach a Bill of Materials to a list of inventory items. In essance, Each item in a list will be linked to several in another list. What is the best way to do this?

My inventory includes quantities. I have quantity on hand as on of the fields in my Inventory form. But, as we ship products, I need to have my program remove the ammount we shipped from the Qty. on hand. How?

Also, I have a list box of all my part numbers, and I want the user to be alble to pick a part and have the part's description be filled in automatically. How should I do this?

That's all for now, but I promise to think of some other questions soon!

Thanks so much!
Brian

 

[shacket]

Brian,

it is somewhat difficult to answer your questions because it is unclear as to what you know and the way you did things. Here are some good questions that you can answer for us:

1. The Bill of Materials - is that a table in your database? A report?

2. Quantities: you said it is a field on a form. Is it a field in the table?

3. When you ship products, how is that entered in the database? (also when you receive them)

4. Part numbers - you have it in a combo box, but is it a value list or is the combo drawing from a table?

Are you familiar with Relationships and how they work? If not, read up on them in the help files.

Again, the difficulty is that it is unclear how you have set things up and unclear what you know and don't know about Access and database structure and design.

Quick answers would be:

To link information in two "lists", they should be separate tables with Primary Keys that are linked in a one-to-many relationship. (Again, if you are unaware of what that means, you need to learn about database structures.)

Quanties (or any field) in a table can be adjusted through code directly if you want to keep it static (i.e. "change 5 widgets to 4 left"), or through a formula if you want it to be dynamic (i.e. "add all we have received and subtract all we have shipped").

The part numbers and descriptions should be listed in a table. When you call the part from the combo box (with info drawn from the table), you can add the description to a text box with the AfterUpdate event.

I hope this helps.

Dave

 

[BrianCapps]

Here are the answers to your questions, Dave:

1: The BOM is stored in a table (or will be, as soon as I figure out how to access (no pun intended) it.

2: Quantities are stored in the 'products' table, along with ProductName, ProductDescription, UM, Category, DrawingNumber, and ReorderLevel.

3: Shipping is entered via a simple shipments form.

4: the combo draws from a table, the 'products' table.

Thanks for the help, I will read up on relationships.

Btw, I am using Access 97.

Brian