2 Database Tables in to the One??? (1 Viewer)

[illingworth22]

I am an extreme beginner as you will see from this problem I have.
I started to make a database for all the patients who pass through the mortuary in the Hospital where I work. This DB was to be able to give me stats on the RIP's in different wards and so on.
Now I initially (I know this was stupid) I intended on making a separate database for each year. I created 2009 and then 2008 (renamed 2009 to 2008 and deleted all records). As I now know I should have had only the one database I am stuck with this problem.
How do I import the data from the 2008 table into the 2009 table. Here is where my total lack of Access knowledge comes in, initially I created the 2009 database then deleted all records and re named it 2008. If I import the table data from 2008 some if not most of the data will have the same primary key information.

Is there any way round my problem?
Is or do I have a problem?

 

[stopher]

I agree that your data for different years should be in a single table. There are many ways to achieve this but it sound like your problem is really to do with the primary key. Could you explain a bit more about your primary key e.g. is it autonumber? Also, do you have other tables related to this primary key?

Chris

 

[illingworth22]

I agree that your data for different years should be in a single table. There are many ways to achieve this but it sound like your problem is really to do with the primary key. Could you explain a bit more about your primary key e.g. is it autonumber? Also, do you have other tables related to this primary key?

Chris

Yes both the Primary Keys are auto numbers and I think this is where the problem is, some of 2008 and 2009's data have the same numbers! 2009, as this was the first development, starts from 1 and goes to 1100+. I started 2008 after 2009 and the primary key on this starts at 190. I think I need to re number the 2008 or 2009 to say 2001 onwards. But this would take ages me thinks...... Unless there's a way to do this.

 

[stopher]

First take a backup of your database.

You can create a new blank table by copying and paste one of the tables and selecting "Structure Only".

Then you can copy/paste the data from your old tables into the new table. If the data is too big then you'll need to use an append query instead of copy/paste. Don't worry about the Autonumber in the new table as Access will deal with that and assign new numbers (regardless of what numbers you are pasting in).

One problem with the above is that if you have other tables that are linked to your old primary keys then these will be completely lost. So we need to use a slightly more complex method of transferring the data. Let me know if you need to do this.

hth
Chris

 

[illingworth22]

First take a backup of your database.

You can create a new blank table by copying and paste one of the tables and selecting "Structure Only".

Then you can copy/paste the data from your old tables into the new table. If the data is too big then you'll need to use an append query instead of copy/paste. Don't worry about the Autonumber in the new table as Access will deal with that and assign new numbers (regardless of what numbers you are pasting in).

One problem with the above is that if you have other tables that are linked to your old primary keys then these will be completely lost. So we need to use a slightly more complex method of transferring the data. Let me know if you need to do this.

hth
Chris

Chris,

Unfortunatly I have the tables linked (I think). As well as keeping a register of the Deaths I also need to add the Undertaker, the Ward/Other Hospital and the Consultant. This data is all stored in separate tables (i.e. a table for Consultants, one for the Ward ect) this way I can add a new Consultant and he or she will appear in the drop down menu on the main form! I hope this makes sense!

 

[stopher]

Depends how the tables are linked. Suppose you Deaths table has the following fields:

DeathID (PK)
DeathDate
PatientName
CauseOfDeath
ConsultantID
HospitalID
WardID

In the above, changing the value of DeathID will not affect the relationships to the Hospital, Ward and ConsultantID.

However, suppose you are recording the Pathologists attending the Autopsy (because maybe there are more than one attending Pathologists), then you might have another table for Autopsies like this:

AutopsyID (PK)
DeathID (FK)
PathologistID

In other words, if DeathID is the foreign key in other tables then there is more work to do.

Maybe you could post a screen shot of your relationship view.

Chris

 

[illingworth22]

Depends how the tables are linked. Suppose you Deaths table has the following fields:

DeathID (PK)
DeathDate
PatientName
CauseOfDeath
ConsultantID
HospitalID
WardID

In the above, changing the value of DeathID will not affect the relationships to the Hospital, Ward and ConsultantID.

However, suppose you are recording the Pathologists attending the Autopsy (because maybe there are more than one attending Pathologists), then you might have another table for Autopsies like this:

AutopsyID (PK)
DeathID (FK)
PathologistID

In other words, if DeathID is the foreign key in other tables then there is more work to do.

Maybe you could post a screen shot of your relationship view.

Chris

Will have to do that from home, the firewall here wont let me upload images. I can e mail to home tho