By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,921 Members | 1,305 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,921 IT Pros & Developers. It's quick & easy.

Append / Make Table questions!

100+
P: 109
Hello

After spending my morning search for a solution to what I need to do, I am a little stuck on which direction to take.

Here is the scenario - I have a database that contains information on clients (tblClient) along with their appointment information (tbl1stAppointment, tbl2ndAppointment etc).
A client can access the service as many times as they like. This is the problem as currently there are duplicate records for some clients. Thinking of a way around this, I stumbled upon append/make table queries. I'm thinking append queries is the right solution though...

My thought is thus - could I move a clients record (or maybe select fields) to a newly created table (called something like tblClosedCases) using an append query (and then an update query to delete the record/fields from their existing place) so that when it comes to entering them into the database again, it will prevent a duplicate and the existing ID number can be used.

I won't post my table structures and stuff just yet as I'm kind of just thinking out loud here and am hoping someone can set me in the right direction.

Many thanks
Gareth
Feb 10 '10 #1
Share this Question
Share on Google+
10 Replies


ADezii
Expert 5K+
P: 8,638
Wouldn't a Primary Key based on the Client ID solve this problem?
Feb 10 '10 #2

100+
P: 109
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.
Feb 10 '10 #3

ADezii
Expert 5K+
P: 8,638
We will need to see more details such as Table/Field Names & Data Types, Relationships, sample data, etc.
Feb 10 '10 #4

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

Table = tbl_Person
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo 
  2. Per_ID; Autonumber; PK 
  3. Per_Title_ID; Number 
  4. Per_Forename1; Text
  5. Per_Forename2; Text
  6. Per_Surname; Text
  7. Per_Gender_ID; Text
  8. Per_PAON; Text
  9. Per_Street; Text
  10. Per_Locality; Text
  11. Per_Postal_Town; Text
  12. Per_County;Text
  13. Per_Postcode; Text
  14. Per_Primary_No; Text
  15. Per_Secondary_No; Text
  16. Per_DOB; Date/Time
  17. Per_Ethnic_Origin_ID; Number
  18. Per_GP_Surgery_ID; Number
  19. Per_GP_Name; Text
  20. Per_Probation_Team_ID; Number
  21. Per_Referred_From_Service_ID; Number
  22. Per_Referred_From_Other; Text
  23. Per_Referred_From_Name; Text
  24. Per_Referral_Date; Date/Time
  25. Per_Practitioner_ID; Number
  26. Per_Resolution_Date; Date/Time
  27.  
There's more fields in this table but these are the most relevant.

As well as this table there are 6 tables (tbl_1stAppointment, tbl_2ndAppointment etc) that all look as follows:
Table = tbl_Appointment
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo 
  2. App_ID; Autonumber; PK 
  3. App_Per_ID; Number
  4. App_Date; Date/Time
  5. App_Outcome_ID; Number
  6. App_Comments; Text  
  7.  
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.
Feb 11 '10 #5

ADezii
Expert 5K+
P: 8,638
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.
Feb 11 '10 #6

100+
P: 109
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.
Feb 11 '10 #7

ADezii
Expert 5K+
P: 8,638
(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)
Feb 11 '10 #8

100+
P: 109
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.
Feb 11 '10 #9

ADezii
Expert 5K+
P: 8,638
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?
Feb 11 '10 #10

100+
P: 109
Consider yourself PMed good sir!
Feb 12 '10 #11

Post your reply

Sign in to post your reply or Sign up for a free account.