In an effort to help a friend get his new business venture up and running, I offered to design a simple (atleast I thought) database to store customer and appointment information. After having completed a database project during my time at college I felt reasonably confident in my ability to do this, even though that was 6 years ago. After spending a good few days on this project it seems I was really, really wrong in this assumption. I am now lost in a sea of information I have read over the past few days both on this forum and from many other sources via searching the Internet.

It seems I may have fallen at the first hurdle. I jumped straight into Access and the design of the tables themselves feeling very pleased with myself for remembering what I had. I eventually came to setting up a relationship between my two shiny new tables and everything came tumbling down in my realisation that I have no real clue what I'm doing.

I'm hoping people don't view this as a 'please do my work for me' because that is not my intention of this thread. Instead I am hoping for some sort of advice on the decisions to be made when designing tables and subsequent relationships, be it through your replies themselves or links to suitable guides/faq's. After having read through many threads here, I apologise for how basic this question may be for the majority of this forums users.

Some information on what is required of the database itself:

1) To store customer details
2) To store appointment details
3) To cut down on the manual input of data by linking the data

I created two seperate tables: Appointments and Surveyors.

Appointments consisted of:

Customer number: autonumber (primary key)
Title
Name
Address
Postcode
Telephone
Job Type
Payer Type
Year Property Built
Time Since Last Insulated
Both Home-owners Available?
Date
Time
Additional Comments
Sumant -
Richard - tick boxes to assign appointments to one of the three surveyors
Harold -

Surveyors consisted of:

All of the above fields
Loft - tick box on completion
Wall - tick box on completion
Sold - tick box
Paid - tick box

The basic idea of the database is to input apppointment information which includes the customer details as well as information on the appointment itself. These appointment records are then assigned to one of three surveyors by clicking the relevant tick box. Queries are designed to create reports for each of the surveyors giving them the details of their appointments for that day. The surveyors themselves can then tick off the jobs as they're completed updating the database to be ready for the next day's appointments.

After reading around the various sites I think I may have a problem with the duplicated information from the Appointments table in the Surveyors table that could be made more efficient via Normalisation of my tables. I think I may need to start at step 1 again and re-design my tables, perhaps splitting them into smaller ones.

Apologies for the extra long post but I wanted to ensure I included as much information as possible. Any help available would be massively appreciated.

Look through the "Design" area of this forum for articles I have written about the approach to building a database. Take the age limits back at least 2 years, maybe more.

Here is your "mental compass." You are building a model of this business because you want to track reality in tables. That can only happen if the tables reflect the reality of the business.

Business models talk about the entities of interest to your business. Two obvious examples: Customers and appointments. One you didn't mention: Surveyors.

We talk about JOIN queries a lot in here. Such queries find cases where one entity has or interacts with another entity. Sometimes it is simple: One customer, possibly many appointments. Sometimes it is nastier. Could the same customer record apply to different properties, with a different surveyor for each? Search this forum for the concept of JUNCTION tables if you don't already know what that is.

The various actions involved in the business will translate into things that must be done to one table or another to reflect those actions. The actions can be implemented via input from a form (piecemeal) or an action query of some sort (wholesale). Schedules for coming visits, customer invoicing, payment history... all of these involve reports based on tables you must keep.

And you keep those tables because of something I call the "Old Programmer's Rule." It is this: Access won't tell you anything you didn't tell it first, or at least tell it how to find out.

What that advice means in practical terms is that often, once you have the gross details laid out for the "obvious" parts of your business, the next step is to look at what you need for day-to-day and longer-term operations to succeed. This will often be in the nature of reports or forms. For everything you think you need, look at the tables to see how you would derive that information. If the data you need is not in the table and not computable from what is in the table, you have identified something to add to your design, a new table or a new field in an existing table.

The mind-set must be that if you can draw a diagram of the business model, because of old programmer's rule #2: You cannot do in Access what you cannot do on paper. Take this one to mean that until you can diagram the data flow, you don't know enough about the problem to program it.

one problem with designing stuff for inexperienced users, is that your app has to be really robust, so they can't go wrong - if its for your own use, this doesnt matter so much, as you can fix little glitches yourself.

so good luck with it, and hopefully the end result will be worth the undoubted effort you will have to put in.

Thanks to both of you for the speedy replies. Doc Man: I spent another few hours re-analysing what I need in the database after reading your post. In doing so I came up with a new table structure involving 4 tables rather than the previous design with 2.

My tables are now as follows:

tblCustomer

Customer ID (PK) Autonumber
Name
Address
Postcode
Telephone
Year Property Built
Time Since Last Insulated
Both People Available?

tblAppointment

Customer ID (PK) Autonumber
Job Type
Payer Type
Date
Time
Additional Comments

tblSurveyor

Customer ID (PK) Autonumber
Sumant
Richard
Harold

tblJobComplete

Customer ID (PK) Autonumber
Loft
Wall
Sold
Paid

So the idea behind this setup is for a receptionist to input customer and appointment details into a form with both tblCustomer and tblAppointment fields. This is then assessed by the area’s supervisor who will assign the appointments to one of the three surveyors in tblSurveyor. Queries that identify appointments assigned to each surveyor are then used to generate a report containing the customer and appointment information which is sent to the relevant surveyor. Once each job is complete, the surveyors then update the database through the tblJobComplete which contains tickboxes for each type of job and to indicate when payment has been received.

This seems to make sense to me although I am having some further issues with the relationships between the 4 tables. At this time I have simply setup a one to one relationship between the primary keys in each table. So from Customer ID in tblcustomer to Customer ID in tblAppointment. From Customer ID in tblAppointment to Customer ID in tblSurveyor and so on. I was under the impression that this setup would allow the database to link the CustomerID numbers on various tables. This isn’t the case however, instead the database is simply creating a new autonumber for each table. If these autonumbers happen to be the same on each table then the data from other tables is shown fine. I guess what I’m trying to say is that the autonumbers aren’t being assigned to customers or appointments but that customer and appointments are being assigned to autonumbers. Isn’t this a fundamental problem in that if I add 10 records to each table then decide to delete one, the autonumbers will be out of sync?

I’m not sure if there is something I have failed to grasp or if it’s a problem with how I have setup my database tables and the subsequent relationships. As always, any help would be massively appreciated.

...tblSurveyor

Customer ID (PK) Autonumber
Sumant
Richard
Harold


Are those field names? What happens when Richard gets headhunted and is replaced by Robert, or Harold has gender reassignment surgery and changes her name to Harriet?

I think you want something like:
CustomerID
SurveyorName
[any other fields]

CustomerID is a PK only in the customer table. Each table needs its own PK. When you store the PK of another table in your record to create a realtionship it is known as a foreign key.

Don't your surveyors need to be joined to the appointment?