Would love to learn to do it from scratch, just have not used Access before.
Can you help?
First step is to get a good feel for how Access works, conceptually. At the
basis of the whole concept is "Normalization" - if you don't have properly
normalized tables, your whole project is on a shaky foundation!
Check some of the references in the websites below, particularly the "Database
Design 101" links on Jeff's site:
Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html
The Access Web resources page:
http://www.mvps.org/access/resources/index.html
In a thumbnail sketch, you need to identify what kinds of "Entities" -
real-life things, persons, events - are of importance. Each kind of Entity
gets its own table; all of the information about that entity goes in that
table (and noplace else!!).
For a sample-checkout application your Entities will be the samples. So you'll
need a Samples table. You'll need some unique identifier for each sample (a
"primary key" in Access terms); my guess would be that you already have some
such identifier, and you should probably carry it over into your database, and
resist the persuasion to assign an Autonumber (if your identifier is unique,
stable, and reasonably short it's probably a better choice since your users
are familiar with it already).
Another entity would be the person checking out the sample. Again you need a
unique PersonID - you may have an employee badge number, or employee number,
or some such; if so, use it. If not you can assign an autonumber PersonID.
This table would have fields for FirstName, LastName , contact information,
office or location, etc.; information about the person (nothing about samples
or about what they've checked out).
Another entity would be an event - the event of someone checking out a sample.
This table would have a link - a "Foreign Key" - to each of these other
tables, identifying WHAT was checked out and to WHOM. You'll need other fields
such as the date and time checked out, the date and time checked back in (this
field will be NULL while the sample is out, which you can use later).
Once you have the tables you can start designing Forms to enter the
information: probably a Form for samples, and a Form for people with a Subform
for checkouts. Or maybe you want to focus on the samples, in which case you
could put the Checkouts subform on the Samples form. You could even do both!
To report what's checked out, who's checked things out when, and so on you
will create Reports based on Queries, probably queries that will join all
three tables.
Post back as you proceed, we'll try to help!
John W. Vinson [MVP]
