I am having trouble with the lingo in Access. They show examples in the book
where you open in the 1st form (Customer Info) and at the bottom you can
select a button to another form (license information) and another buttom to
another form (activities). When I click through all forms and enter the
information I want to be able to run a report of who has or has not paid for
their 2005 license. Make sense? What are these steps called? Right now I
have the "CustomerNumberID" field on each form and they are the priomary key
for each, this is also what links them in "Relationships" Am I heading in
the right direction?
I think your first misconception (and it's an easy one go fall into!)
is that data is stored in Forms, and that you can link the data in one
Form to another Form. Forms don't "update to each other" - different
Forms may be windows onto the same table, viewing it in a different
manner, but this is a different matter than "updating".
Data is stored in Tables and *ONLY* in Tables. A Form is just a tool,
a movable window which lets you edit the data in a Table (usually via
a Query). The examples in the book (which book, might I ask??) appear
to be starting at about step 6 of the database design process!
Rough outline:
Turn off the computer. Get a #2 pencil with a good eraser and a pad of
paper, and a cup of tea, coffee, or your preferred mild stimulant
beverage of choice.
- Identify the Entities, real-life things, persons or events, of
importance. In this case, I'd imagine Customers, Licenses, Activities,
probably Payments, maybe more.
- Identify each Entity's attributes: a Person has attributes such as
lastname, firstname, other identifying information. A Person may have
a single address (at least only one address that you care about, maybe
you don't need to know where their fishing cabin is), or might have
more than one; if one, then the person would have attributes Address1,
Address2, City, State, PostCode. Or these might be attributes of an
Address entity related to the Person entity.
- Identify how the Entities are related. A Customer will have zero,
one, or more(?) Licenses; each License will pertain to one and only
one Customer, for example.
- Given these, for each type of Entity you would create a Table. Each
Table should have ITS OWN Primary Key - it's *VERY* rare that two
tables would both have the same primary key! The Primary Key of the
Customer table might be your CustomerNumber (or CustomerID, it's
probably not a NumberID) - a field which uniquely and clearly
identifies each customer. The Primary Key of the Licenses table would
NOT be the CustomerNumber unless each customer has one, and *only*
one, license, ever over the course of your application's lifetime. It
might be the LicenseNo (I don't know whether these are drivers'
licences, auto license plates, dog licenses or what <g>). The Licenses
table *would* contain the CustomerNumber, but this would be a separate
field - called a "foreign key" - used to link to the Customers table.
Each attribute would correspond to one field in the table. If you find
yourself putting in repeated values - such as License2003,
License2004, License2005 or Payment1, Payment2, Payment3 - STOP;
you've discovered another entity in a one-to-many relationship with
this table.
- ONLY NOW would you start thinking about how to design Forms to enter
this data. Access' forms are quite versatile; you will probably make a
lot more use of a Form with Subforms, than you will need buttons
opening other forms (though such buttons are handy and often used,
they require VBA code and aren't necessary for most purposes).
John W. Vinson[MVP]
