Importing old database to Access how to with query help?

[Technyc2003]

Years ago I created an old database using Symantec Q&A for DOS for my job. Time has come to convert this database data to Access with multiple users being able to use it.

I've already converted the data to an Excel spreadsheet. Now here's the thing. I'm collecting data on patients at our clinic and the primary key is their ID#. Because there's so much data, I created 4 tables and each table is linked with the PatientID as the main field to be linked through the relationships functiom. I'm not sure if this is the way to do it but I don't know any other way of doing it. My problem is that I don't know how to import the data from the Excel spreadsheet into each one of these tables with the exception of the 1st one but want to make sure the other data gets to the other 3 and that the data matches that patient.

I tried to do some crazy query and Access laughed at me with some error message.

Here's the layout of my tables:
-----------
PatientInfo
-----------
PatientID (PK)
Last Name
First Name
DOB
City_State_Zip
Home Address

------------------
MedicationHistory
------------------
PatientID
Medications
Dosage

---------------------
HouseHold Information
---------------------
PatientID
Name
Relationship
Age

----------------------
Insurance
----------------------
PatientID
Type
Number
ExperationDate


So if there's a way I can do it, I'd appreciate if someone here can tell me how I go about doing this. Thank you.

 

[Edski]

Hi there,
You should post the error message as it would help others to identify the problem more easily.

I don't see any problems here. You've got 4 empty tables linked together through PatientID in a one-one join (correct?) and you want to import the data from Excel. All you need to worry about is joining the tables correctly. Have you tried importing the tables one by one manually?

 

[DrSimon]

I'm amazed that you've managed to hang onto DOS software for so long! You're not doing too bad so far, what you've started to do by splitting into different tables is called 'Normalisation' and is "a good thing". In terms of design you can get advice from another forum 'Microsoft: Access Tables and Relationships'.
You haven't said anything about the structure of your spreadsheet or the structure on Symantec Q&A. Access has some really good tools for importing data, though it is often helpful to go through Excel first which is even better. However Excel has a size limit problem (65,536 rows by 256 columns maximum).

You've also got the problem of the actual plan of creating the new database. For example - what are you going to do about new admissions between the time of taking the data from the existing database and updating the new one?

What you want to do is not a trivial task and you may well do better to get a professional IT person to help. It is often much more difficult to transfer from one system to another than to set up from new. I speak as an ex-healthcare scientist now in healthcare IT.
Simon Rouse

 

[BNPMike]

All you have to do is import the 4 spreadsheets using Access import wizards. Access will do everything for you. all you do is press the button when it tells you to. As long as the Patient ids are correct in each dataset then you have a working database with all the records correctly connected. It really couldn't be easier. Where are you getting stuck? If it asks you for keys you can just say 'no thanks' for the time being.

 

[Technyc2003]

I exported the data from my old program into an ASCII format and then opened up the file into Excel. There's only 1 spreadsheet that contains all the data that I want to be able to import into the 4 tables in Access. So I created an append query to import the data from a table that was imported called QAOLDDATA into my [HouseHoldInfo] table in access. I added the two tables in the query screen and joined both tables via the PatientID. I seletected "Only include rows where the joined fields from both tables are equal." When I try to run it, it says that you are about to Append 0 row(s). So I'm wondering if this is the correct way to import the data I imported from Excel to a new table in Access and make sure the data is correct for each client via the PatientID.

If you need more info please let me know.

PS - It's not 4 spreadsheets, just 1.

 

[Technyc2003]

Also just to let you know. The entire database is completed. Reports, Forms, tables, queries. Just time to take the old data and import it. They're still using the old DOS app until this one is ready to go.

 

[DrSimon]

Glad to hear you've done the main body of work. I thought you would only have one spreadsheet. I suggest you copy it 3 times giving them appropriate names and then open and delete the columns you don't want. These can then be imported.

 

[Technyc2003]

I'm going to try and see what happens again, I just want to make sure that when I do the import the records match to the right client.

 

[bosk00]

A thought that may or may not help. In Q&A if you go to the file menu, then the utilities menu, then the export menu. Some of the choices are for dbase files. You can import the tables then directly without going through excel.