jimd176 said:
I would like to set up a simple database that will allow me to monitor stock
in and out. Also to have wholesale and retail prices.
I would also like to be able to run reports that allow me to check stock
availability, check net profits and stock that sell well and others
that dont.
jimdl76,
Like all database design projects, you will sit down and work out what
it is you need, and how the stock control and pricing portions of your
business works.
Once you have this down, you can design a "model" of these business
functions operate. This will be a list of all the "entities" (shelf
bins/slots, last inventory quantity, pack qty, prices, dates, external
vendorproductIDs, internal skus/ids, etc.) in your system, the real
world and abstract information you wish to track. From this stage,
you can link them all together in *data modeling* relationships. You
can fake a data modeling tool with Paint, Power Point, or pen and
pencil; or you can get yourself a freeware version (most of the
pay-versions are pretty expensive).
Google:
"Data Modeling"
Once you have your data model down. You can work on translating them
into actually database Tables.
Google:
"Relational Database Design"
"1NF, 2NF, 3NF, BCNF, 4NF"
"MS Access Database Design"
Once your Tables are designed, you can start out by working out how
you want your screens to appear. There are two things, here. The
data model is of enormous importance, but you can't get too bogged
down in it, especially if you've never done it before. Once you think
Take blank sheets of paper, and just free-hand draw your text boxes,
grids, labels, combo boxes, check boxes, list boxes, etc. (It doesn't
matter how squiggly they are). You'll make many mistakes, just
discard them and start over (you're not going for neatness, you're
working to eliminate bad ideas before you get around to making them by
mistake in the Forms design stage. If you have employees, show the
designs of the screens *they'll* be using. You hardly want them
working on something that slows them down or doesn't do what it really
needs to.
If you have an idea of what kind of Reports you want, you can draw out
how you want them to appear, as well.
Once you are at this stage, it's Form and Report design time.
Then, you want to work on Security, and Backups/Disaster Recovery.
(You *will* need both, even if only to protect the database against
accidents.)
Make your .mde, and run your finished app and monitor your stock.
Keep all the documentation on the design process. It'll be a learning
experience. This way, when you go on to the next project, you'll be
able to look a what you did the first time.
Note: None of this is "easy". It's all hard work. I hope you have a
blast doing it.
Sincerely,
Chris O.
