[Conner.McLaughlin]

Good day all,

So here is my situation. I have 3 tables already built in access; that have one to many relationships already set up that look like:

PatientInfo Table:
PatientID (Primary Key)
DOB
Address
etc...

IllnessEvent Table:
IllEvtID (Primary Key)
PatientID (Foreign Key)
Facility
etc

MedicalCode Table:
CodeEvntID (Primary Key)
IllEvtID (Foreign Key)
approx 150 Medical Codes, all yes/no


I have approximately 2500 records in an Excel spreadsheet (expect to add another 7,000+ in the near future). I would like to import this into Access and split the data up into the appropriate tables.

For the majority of cases there is a 1:1:1 ratio across the tables;however, there are cases where one patient has multiple illness events, and there are illness events that have multiple coding events.

The medical codes in Excel are stored in text format...in the spreadsheet the header's are just Code 1, Code 2, Code 3, etc... and beneath are the numeric (text format) codes.

In the Access tables there is a yes/no option for every code that I'm interested in (e.g. 781.1->yes/no; 756.3->yes/no, etc...), as well as fields for "other codes" to be typed in that were recieved but aren't directly related to what is being studied. All other fields have header's equivalent to the variable name's in the access tables, and are in the form that they will be stored in.

So, my questions are:
1) What is the best way to bring the data in and sort it into the different tables? (from the bit of reading I've already done in the forums looks like VBA is going to be the best bet, though I have no experience with this...but am eager to learn)

2) is there a way to get access to look at the Medical Codes, recognize "781.1" and then for that entry make the Yes/No option for 781.1 a "Yes" and make it a "No" for all the other, or for someone that has 781.1, and 915.4, yes for both of those and no for everything else, etc...

I realize a full response to some of this might be beyond the scope of these forums, but even something that says "yes, what you pursue is within the realm of the possible" or "no you'll just have to go through a form and update the Yes/No code fields by hand..." or whatever...

Thanks much for any feedback!

-Conner