Append / Make Table questions!

**ADezii** · Feb 10 '10, 02:49 PM

Wouldn't a Primary Key based on the Client ID solve this problem?

**hedges98** · Feb 10 '10, 02:56 PM

It already is a Primary Key! The problem is when a client re-engages, you can't overwrite the existing details (such as when they were referred, GP details, where they were referred from, comments etc) as obviously the previous information will be lost. Of course, the name, gender, DOB, address (usually) are all the same.

I don't know if there's a simpler solution for what I need but I'm willing to listen to suggestions from people clearly a lot more knowledgable than I.

**ADezii** · Feb 10 '10, 04:02 PM

We will need to see more details such as Table/Field Names & Data Types, Relationships, sample data, etc.

**hedges98** · Feb 11 '10, 09:48 AM

Just a forewarning - I didn't create this database and so some of the field names are a little weird!

Table = tbl_Person

Code:

Field; Type; IndexInfo 
Per_ID; Autonumber; PK 
Per_Title_ID; Number 
Per_Forename1; Text
Per_Forename2; Text
Per_Surname; Text
Per_Gender_ID; Text
Per_PAON; Text
Per_Street; Text
Per_Locality; Text
Per_Postal_Town; Text
Per_County;Text
Per_Postcode; Text
Per_Primary_No; Text
Per_Secondary_No; Text
Per_DOB; Date/Time
Per_Ethnic_Origin_ID; Number
Per_GP_Surgery_ID; Number
Per_GP_Name; Text
Per_Probation_Team_ID; Number
Per_Referred_From_Service_ID; Number
Per_Referred_From_Other; Text
Per_Referred_From_Name; Text
Per_Referral_Date; Date/Time
Per_Practitioner_ID; Number
Per_Resolution_Date; Date/Time

There's more fields in this table but these are the most relevant.

As well as this table there are 6 tables (tbl_1stAppoint ment, tbl_2ndAppointm ent etc) that all look as follows:
Table = tbl_Appointment

Code:

Field; Type; IndexInfo 
App_ID; Autonumber; PK 
App_Per_ID; Number
App_Date; Date/Time
App_Outcome_ID; Number
App_Comments; Text

The appointment tables field App_Per_ID is linked to Per_ID in tbl_Person.
These appointment tables are displayed as continuous subforms on the main form as each person can have, for example, several 2nd appointments depending on the outcome of each appointment.

I hope this all makes sense. If not, I'm happy to clarify anything.

**ADezii** · Feb 11 '10, 01:48 PM

I know that you inherited the Database, but I would hardly have structured it in this manner. One method of getting around your dilemma may be to eliminate the Single Field primary Key in the Client Table, and create a 2-Field Composite Primary Key/Index. You can create this Primary Key on possibly The Person's ID and Surgery ID. This means that a Person can be listed more than once in the Person Table, but not for the same Surgery (The combination of IDs must be 'Unique'). This would violate the Rules of Database Normalization since basic data in the Person Table would be duplicated, but then again in my opinion, the Database is not normalized anyway.

**hedges98** · Feb 11 '10, 02:15 PM

You and me both! It is a mess and I've been playing around with it trying to make it function a little better but for the most part it's been a bit of a nightmare - this is what happens when you decide to create a database without having a clear specification from the off and then drafting in a semi-clueless oik (hello!) to try and sort it out. There are so many fields just tacked on that are unnecessary it's a bit ridiculous.

Your idea of creating a composite primary key is interesting - however, I am trying to think of ways to stop duplicates from occuring in the Person table which is why I was looking into append queries and suchlike - is this worthwhile or more hassle than it's worth?

(As an aside, creating a composite on the Person's ID and Surgery ID wouldn't work - if a client re-engaged with the service and it came to entering their details, the database would prevent the same surgery from being selected, wouldn't it? Or am I not following what you have said correctly?!)

Thanks for your suggestions so far, I am going to look into the composite key idea further.

**ADezii** · Feb 11 '10, 02:33 PM

(As an aside, creating a composite on the Person's ID and Surgery ID wouldn't work - if a client re-engaged with the service and it came to entering their details, the database would prevent the same surgery from being selected, wouldn't it? Or am I not following what you have said correctly?!)

You are correct in that the DB would not accept the entry. Short of a total redesign, which is basically what you need, you can now try a 3-Field Composite Key, namely: Person ID, Surgery ID, and Date since I'm sure that the same Person cannot engage the same Surgery on the same Date, but with this DB who knows? (LOL)

**hedges98** · Feb 11 '10, 02:58 PM

Argh, this is where my head starts to hurt!
Not all clients necessarily have their surgery details stored on the database - it depends on how they are referred. It'd probably make more sense if I explained what the database is actually for... which would take a couple of paragraphs (fancy a read? haha)! The database is a mess and the structure is probably just confusing you more with what I'm saying as it's probably doing the opposite.

I don't think a composite key will solve it, annoyingly, but I'm at a loss as to where to go from here.

**ADezii** · Feb 11 '10, 10:27 PM

I'm not sure that this is possible given the nature of the DB, but can you E-Mail it to my Private E-Mail Address, then I can look at it?

**hedges98** · Feb 12 '10, 09:09 AM

Consider yourself PMed good sir!