Database design for simple inventory

[toptech]

Hello all! I am brand new to Access and to this forum (this is my first post). I am trying to use Access to setup a simple data base to enter my inventory into (I own my own repair shop). The data will be simple. I need to create tables for the following: item number, item description, supplier, manufacturer, item category (software, hardware, network appliance, custom program, etc.), item price, and number of items in stock. I would also like to eventually add information for phone numbers, addresses and url's of the suppliers and manufacturers and maybe a memo field for the items to allow for a more lengthy description or installation notes if needed. I bought a book (Access Bible) thinking it would help but I am totally lost. I understand the need for good planning and with that in mind I figured it would be best to get some help early on. I think I'm going to need at least three tables to start: one for the items, one for the Suppliers, and one for the Manufacturers. but I need help deciding which data to include in each table and how I should setup the relationships among the tables themselves. Can anyone offer some guidance? I know this is probably easy stuff for the initiated, but again, I am a total newbie to Access and would appreciate any help I can get.

[RuralGuy]

The best way to get started is to take an existing template and start modifying it to better suit your needs. Have you looked at all of the templates that ship with Access and that MS has on the Office web site?

[Ryan]

Top Tech, I need to learn. So I will try to give you a hand. Might take a few days, but I'll put something nice together for you. Thanks to everyone here because I know I will be asking questions ;p

[toptech]

@Ruralguy. Yes I could only find the home inventory template and was bot really sure it fit the bill nor how to modify it so it would.
@Ryan. Thanks man, I appreciate any help I can get. I am reading the book I have to try to get up to speed but, as I said, this is all very new to me. Thanks again.

[Ryan]

This is the data base so far. The querys work fine, outside the form. It ask for parameters, and I get the result. But seeing, is a little easier. Here is the DB so far.

Its as a attachment in .zip format.

Thanks!

PS. It all runs off the form called, StartMenu

Rural, you suggested posting the DB. To refresh, you showed me how to put a Subform in a datasheet. It worked on another database. But here the two combo boxes, drop down, pick. But get blank results.

[Ryan]

A more updated DB. _ The problem I am having is making the combo box critieria work, to display on the SubForm. The combobox's are on the main form.

[RuralGuy]

Which form are you trying to get working?

[Ryan]

There all based on the same princible. Using a Criteria in the query, to give a result on the Subform.

If you can make any of them work, I can use that to make the others work.

Im stumped, as I got this to work in another database. Not sure what im doing wrong, but Any of them. Can you post it back, if you get any of them to work?

Thanks Rural.

Note: (any of the Search Forms) ( The customer search would be awesome)

[RuralGuy]

OK, try the form that loads automatically.

[Ryan]

Wow thats nice. Thanks, Rural. Hopefully I can talk to toptech soon and get more of a idea of what he wants. I'll work with this for now. Thats really nice how its auto finding. Im assuming its in the properties of the Form, the query looks to be the same.

Thanks Again!

Soon as I get the thing fully working, I post it up again for toptech or anyone else who may have use of it.

If anyone has ideas of things to add, do tell. Its for toptech as a PC Repair Business, Though im still in that sort of Beta phase ;p.
Please Suggest if you got ideas!

*Rural, ya the bomb!*

[RuralGuy]

I changed the bound column of both ComboBoxes.

[Ryan]

Well here you got TopTech. I also imported the Account Ledger Template, so you can keep track of bills aswell.

Thanks Rural, couldnt have done that without you. I suppose I could go further with it, which I probably will. Just to learn a bit more. But for now, here ya go.

For you pros out there, Be gentle. I only been working with Access 2003 for about two weeks. :P But! I would love opinions, it would definately let me know how/what I should do to change things.

TopTech, if you want me to add / remove / change, send me a Email, I will give it a go!

[jleslie48]

Well here you got TopTech. I also imported the Account Ledger Template, so you can keep track of bills aswell.

Thanks Rural, couldnt have done that without you. I suppose I could go further with it, which I probably will. Just to learn a bit more. But for now, here ya go.

For you pros out there, Be gentle. I only been working with Access 2003 for about two weeks. :P But! I would love opinions, it would definately let me know how/what I should do to change things.

TopTech, if you want me to add / remove / change, send me a Email, I will give it a go!

Wow! Hi all Jon here, brand new to this forum and I've never used access (but very skilled with Excel, C programming) and I've been tasked with making an inventory system. I'm very glad to see some from the ground floor examples are started here in this forum and specifically this thread. I'll be looking at the attached zips shortly.

My issues with an inventory system will be a little different. While I have purchased products A,B,C, ... I will also be producing products AA, BB, CC, ... where for example AA will consume say, 4 of A, and 22 of B, and 18 of C, ... In other words, I'm making finished goods from raw inventory parts. When I make a finished good, I must subtract inventory from A, B, C, ... based on a list of components of the item (a BOM, a bill of material) multiplied by the number of units I've added of the finished good. So some facility to make a BOM must be there as well as the addition, subtraction of inventory levels.

I think Access should be the right platform for such a system as I can't get it to flow easily in Excel.

Also there should be a running log file of what was adjusted in the inventory, aka

YYYYMMDD product A reduced by xxx units
YYYYMMDD product B reduced by xx2 units
YYYYMMDD product C reduced by xx3 units
YYYYMMDD product D reduced by xx4 units

YYYYMMDD product AA increased by xx5 units

That's the biggies of the system, and there are a few finer points that I haven't mentioned (product lookup, re-work of a product, location of product) but the above described procedure seems to be missing from off the shelf inventory systems.

I again apologize for my lack of Access knowledge, I'm really a C programmer in an electronics shop who must create an inventory system but not use any man hours to do it, - thanks a lot boss.

I would make this entirely in C if I had the time, but that would take me at least a month probably two full time. The boss won't go for that, besides I have my real job to do. He expected me to search a few hours on the web and have one up and running. LOL.

Anyway, I'm hoping for a base inventory system in Access, something I can reverse engineer a bit to get the mindset of the Access environment, and then add my custom tweaks to the system.

TIA,

Jon