Any MS Access wizards here?

d8m

We have a part number directory for the department where everything gets allocated a number, description and a few details(fixings, castings etc.). Sequential list nothing to complicated but there are a few issues with how its working for us. Main ones being people can accidently allocate the same part number twice if they haven't pushed pulled from SVN and its not the easiest thing to search so sometime we get duplicated parts added.

What I would like to do is move it over to an access database  with predefined fields for certain data, I would like to stick a front end on this for the allocation of new numbers and searching what we already have.

I know exactly what I want it to look like in my head but I haven't touched Access in years so don't know where to start.

Anyone able to point me in the direction of some relevant guides/tutorials?

Cheers

Axe_meister

Part number field should be defined as unique. Add 1 for each new record.

darthed1981

Nice project for a first Access database.

First off, consider seperating the front and back ends, and linking tables.  Makes the database a lot faster and more resilient if you are going to have multiple users.

Create a table in your back end and add your fields, before doing this, consider some database design principles, for example, should this be one table?  It is possible you will store the same information many times when you could store it only once.  For example, if you had a table of "parts" and one of "customers" you would probably store the customer details once, and a customerid as a foreign key against each part, avoiding repeated data.

You need only one form to enter part details, maybe another with a wildcard search for part details like description and part number.

Access is a powerful, frustratingly limited in some ways but nonetheless useful tool.  If you aren't familiar I'd learn some basics about database design before you start, then use this project to teach yourself a bit of Access.

It's power steps up drastically when you learn VBA and add code to your forms to do clever stuff.  You can do a lot with the design tools but learning a bit of SQL will help a lot.

Snags

In posh speak for @darthed1981 's excellent advice, Google normalisation and third normal form when it comes to designing your data structures Mostly because if you screw that up it will never work right.

And learn VBA early, cos the stuff generated by the wizards is shocking, unless it has improved massively. Oh, and yes the table linking stuff to help enforce integrity, but don't cascade deletes unless you're very confident.