database design

[cgacker]

i need to create a database that will document visits to people, at different
locations, on different days. the summary report needs to include the days
visited and infrom from each event (what was discussed, supplies used,
details regarding the person visited that day, etc). i suspect i need the
following, but may need subforms(?):

table - personnel (names of people visited, their address, etc)
table - details regarding visit (what was discussed, etc)

i'm not sure, but i suspect i need other tables, also

aagain, i would like to create a summary report at the end of the month for
each person, listing their name, their details, as well as dates of visit(s)
and details of visits


thanks for any help!

 

[Amanda Payton]

CG -
You're right - storing your data in a couple tables is the best move. Keep
in mind though - Forms themselves don't store data, tables do Forms are what
display your data in a way that is meaningful and understandable. Having a
good table structure is like having a good foundation in a house... Without
it - everything will eventually fall apart, and will never be as flexible as
you might wish... you get the picture....

For a project like this, you are just about right on the mark. I would set
up tables in something similar to the following format:

1. Clients
1. Client ID (Primary Key)
2. First Name
3. Last Name
4. Street Address
5..... (other fields relating to how to find/contact/locate a client)

The goal of this table should be to have 1 record for each client. Your
Client ID field is probably not ever going to be seen by your user (unless
you want to make Name be one field, in which case, that can be your ClientID
since it's not likely you'll ever have 2 clients with the same name. If this
is possible, leave the numerical ID in or come up with some way to tell the
identical names apart, and use that for your ID.)

2. Visits
1. Visit ID (Primary Key)
2. Client ID (Foreign Key)
3. Date/Time
4. .... any other fields relating to what happened at that visit.

The trick to making all this work is having the foreign key. This is what
associates a visit with a client, and the reason WHY client ID has to be
unique. You can't be in two places at once...

if you have information that doesn't really fit into either catagory of
Visit Information or Client Information - but is related to one of the two,
use the concept of Foreign Keys again to link that table to whatever else you
need to create. For Example... if you are going to track travel expenses for
each visit - you might make an Expenses table that has Visit ID as a foreign
key (since if you wanted to be really picky, you have 2 trips for each
visit... one each direction)

I hope that helps! You are defenitely on the right track in thinking things
through! If you get stuck building forms and reports to read and edit your
data - check out those two sections of the Discussion Group, and holler if
you have more questions!

Happy Designing!
Amanda

 

[cgacker]

amanda-
thanks very much for the help. i will post further if any problems arise.

cga