New to Access... is this practical

C

Cranky

Hello

I'm looking to set up a database of callers to my office, but am unsure
about the practical steps needed, and am hoping for some advice.

I run a public counter where visitor will need to see any one or all of
6 different departments. What our bosses want to do is have a record
for each caller listing their personal details, dates visited, sections
and member of staff seen, and reason for visit.

What is the best way? I thought of a table for all the personal details
- name and address, and latest visit details, another table for name
and visit history, and another listing the different departments and
members of staff.

I can set up the tables and relationships okay, but don't want to do
anything needlessly, or make it more awkward than it has to be.

Any practical advice would be greatly appreciate.

S:)
 
M

Matt Kisasonak

I think your headed in the right direction but there will
be more questions to answer before setting up a DB like;

Does each visitor visit the same departments each time
he/she visits? This can be set up when their account is
created.
Does your staff work in their own departments or do they
change departments? Is it one department, one staff
memeber or many staff memebers per department...

You will probably have these tables; Visitors, Visits,
Departments, StaffMembers

One visitor will have many visits.
One visit will have many departments.
Each department will have a staff member???

Matt
 
C

Cranky

You will probably have these tables; Visitors, Visits,
Departments, StaffMembers
Click to expand...

This is complicated by the fact that each department has their own
reference number, so someone could have up to 6 different numbers
attributed to their name. I think I can tie name and 1st line of
address or postal code to a list of reference numbers.

They would visit any one of the six departments and so could in theory
be a different one each time.

Ideally, I would want to set up a list of every one of a person's
visits and the reason for visit regardless of department seen.

S:)
 
S

SirPoonga

You also need to answer if a staff member can be in more than one
department.

If not then the Staff table will have a department field.

If so there's a many to many relationship between department and
staffmembers so an inbetween table is needed.

As for just doing the visitors and visist part your Visidtors table
will contain all data on that visitor like phone number, addres, etc...
and you will probably want to have an ID field to ID that visitor.
Then in the Visits table each visit will have it's own ID to identify
the record. The other fields would be VisitorID, ReasonForVisit.
Depending on how Staff is related to Departments:
Individual Staff can only be in one department you will add
DepartmentID and StaffID to the Visits table.
If a Staff member can be in more than one department you will need to
put the ID of the inbetween table the identifies the Staff and
Department the Visitor talked to into a field in the Visits table.
 
L

Larry Daugherty

Hi Cranky,

Some thoughts:

Some of the things suggested may seem very confusing now but it's worth
working your way through them. Post back as you have questions. Some other
useful newsgroups for starting are microsoft.public.access.gettingstarted
and microsoft.public.access.tablesdesign Also, read starter books on Access
and work your way through the exercises.


You might want tables for Person, Address, Department, and Visit.

All people type entities go into one table with Staff and Visitor being
values that go into a single field in a Person record.

Address is in a one-to-many relationship with Person. A person might have
many addresses for different purposes.

Department is a list of all departments and will be a value placed in the
Visit record.

Visit is one-to-many to Person - they might just come back again.
frmVisit is the workhorse form. You'll probably want to have your own
reference number, if so, you'll want it to be automatically entered for you
(stay away from the Autonumber datatype for this purpose). You'll find a
Person's name in tblPerson from a combobox and enter it if it doesn't
already exist. You'll find the staffer's name and click it to enter it.
You'll find the department name and click it to enter it. I don't know how
you get the reference number from the department but you'll need a field and
control for it. You'll enter the reason for visit (it there is a limited
repertoire of reasons, they could be in a table and you could then just pick
them). The date of the visit should enter automatically and still allow you
to change it if necessary. Tracking people this closely, you probably also
want entry and exit times.

You'll probably also need to create forms for data entry to the tables and
maintenance of your data. You may, in time need reports.


HTH
 
C

Cranky

Thanls for the info. I'll have a look through and then get back with
specific questions.

Thank you all for replying.

Steve
 
Top