Table Structure Help

[hedges98]

I'm not sure how to best explain this but hopefully someone will understand my problem!

Basically, I am creating a new database but am at a bit of a stumbling block as to how to structure one of the tables.

I need to create a table to store Appointment information for each client. However, each client could have 0 appointments or up to 6 appointments. In the existing database, a continuous form was used but this caused problems when querying the appointments for different reports (for example, querying how many clients attended their 2nd/3rd/4th appointments etc).

My question is - is there a way of creating a table that will add appointments in when needed as the way I'm thinking of solving it is to give each client 6 appointment 'slots' but this would be a bit redundant and have a lot of blank entries into the Appointments table.

[MMcCarthy]

It's not clear from your post why you were having problems. Can you post the metadata of the client and appointment tables so we can see the existing structure and then maybe we can figure out where the problem lies.

Here is an example of how to post table MetaData :
Table Name=tblBooking s

[i]Field; Type; IndexInfo[/i]
StudentID; Autonumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

[hedges98]

Well, this is the existing set-up. The one I'm currently creating is only half designed as I am stuck at this point!

I won't post all all the information stored in the Client table as it's not relevant here.

Table Name=tblClient
Field; Type; IndexInfo 
ID; Autonumber; PK 
Forename; String 
Surname; String 
Address; String

There are additional fields containing other information...

Here is the Appointment table:

Table Name=tblAppointment
Field; Type; IndexInfo 
AppID; Autonumber; PK 
App_Per_ID; Number 
AppDate; Date/Time 
AppOutcome; String 
AppComments; String
AppFirstDate; Date/Time
AppFirstComments; String
AppFirstOutcome; String

The tables are linked by the ID from tblClient = App_Per_ID in the Appointment table.
I added AppFirstDate, AppFirstComment s and AppFirstOutcome to this table a while ago so I could query information on the first appointments. But I need to be able to query any of the appointments but as the information is added via a continuous form, I can't work out how to do that.

Is this structure worth keeping or is there an easier way to solve this issue?

[MMcCarthy]

It shouldn't matter how the data is added only how it is stored.

Using the following structure ...

Table Name=tblClient
Field; Type; IndexInfo 
ID; Autonumber; PK 
Forename; String 
Surname; String 
Address; String

There are additional fields containing other information...

Here is the Appointment table:

Table Name=tblAppointment
Field; Type; IndexInfo 
AppID; Autonumber; PK 
App_Per_ID; Number FK
AppDate; Date/Time 
AppOutcome; String 
AppComments; String
Attended; Yes/No

Then the following query will tell me the first appointment for each Client.

SELECT tblClient.ID , tblClient.Forename & " " & tblClient.Surname As Name, DMin(tblAppointment.AppDate)
FROM tblClient INNER JOIN tblAppointment
ON tblClient.ID=tblAppointment.AppID
GROUP BY tblClient.ID , tblClient.Forename & " " & tblClient.Surname

You can then join this query to the appointment table to query the rest of the information for each client.

Note: I have to go out now for a few hours. I will check this out when I get back but in the meantime some of the other experts will probably check in so feel free to post further.

[hedges98]

Is there a way to query different appointments though? Like if I wanted to see how many clients didn't attend the 3rd appointment for example?

A problem I've just realised that I haven't mentioned is that there could potentially by a lot more than six appointments stored for each client as if an appointment is booked and then cancelled, this is stored in the database so, for example, a client could attend 5 appointments but have 10 entries with the 5 other entries being rescheduled/cancelled/not attended etc.

The AppOutcome field contains the information on if the appointment was attended etc (Attended, Cancelled, Rescheduled, DNA etc.)

I'm aware that I'm rambling a little but hopefully it's coherent!

Thanks for your help so far

[MMcCarthy]

You have to decide how you identify each appointment. For instance if you add an AppNum field and give each appointment a number like 1, 2, 3, 4. Then if an appointment is cancelled you could change the AppNum to 0 and give the next appointment that number.

Forget for the moment the database and try to work out logically how you would identify each appointment. The database can only use the information you give it.

[hedges98]

msquared - that is actually a fantastic idea, thank you so so much. Gives me something to think about and toy with! That seems to be the beginnings of a solution and will mean I can simply edit the existing database rather than start anew like I'd planned.

The thing is, regardless of, say, the 4th appointment being attended or cancelled or unattended, it still needs to register as the 4th appointment. Otherwise, if I query the number of 4th appointments attended it will come back with all of them attended, won't it?

My brain is pretty fried from messing around with this all day, so I'll ask my supervisor what is needed tomorrow. No doubt I'll need your help again.

Thanks very much though, certainly on the right tracks I think!

[MMcCarthy]

You're welcome. Always remember to sort out the logic first before attempting to change the database as it saves a lot of headaches later on :D

[hedges98]

Right, I figure the best solution is to add an AppNum field and just number the appointments (cheers msquared!). However, is there a way to do this automatically?

So if a new client's appointment information is added then the AppNum will start at 1 and then for each additional appointment will increment by 1? I have no idea where to even begin with this so any help is massively appreciated

[MMcCarthy]

The problem will lie with how to cater for cancelled appointments in any automation of this routine. Otherwise you could just use the DCount function on the appointment date.

[hedges98]

Cancelled appointments are counted the same way as any other appointment outcome.

I will look at the DCount function. Be prepared for my return with a host of new problems...!

[hedges98]

I had a little look at the DCount function and I'm confused.

What I need is for the new AppNum field to increment by 1 every time a new appointment is added for a client. Is DCount capable of this?!

Obviously I can go back through and update the existing records but for future use (when I won't be here), is there a way to have the numbers add automatically?