Hi Adam,

My first observation is that your worksheets have a lot of duplicated, hence redundant data. Provided each client has a unique ID, that is the only datum required on all sheets. Aside from anything else, this reduces the risk of inconsistencies between the two sets of data. In this regard, you'd probably do best to have one sheet with the details of each client, then whatever other sheets you need for, say, referring agency details and for tracking clients' history. As it is, though, if you're going to have a separate record for each 'event', you could probably have the whole lot on one worksheet.

Whichever way you go, I don't think there'll be a significant issue exporting the data to Access - provided you eliminate the data duplication. Access can import Excel workbooks and various other files formats, including CSV to which Excel can output data.

As for reading the file, I have both Office 2000 and 2007 installed. In any event, Office 2000 can read Office 2007 files courtesy of a format converter from MS.

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

Thank you. The reason for the duplication is two-fold: 1) it's easier for the end-user to see some of the data from the first sheet when filling out the second sheet, and 2) it's necessary for reporting purposes. I know of no easy way in Excel to create the reports I referenced in my original e-mail without duplicating the data. I do not believe there to be a real risk of inconsistencies, as the duplicated data on sheet 2 are all copied over directly from sheet 1 through vlookups - there's no repeated data entry. Enter the MR #, select the admission date, and the data entered in sheet 1 appears on sheet 2.

That being said, it appears that Access is structured so that there's no reason for this duplication. So, what I'd probably do is import the second worksheet without the duplicated data. The only link between the two sheets would be the first two columns: MR # and admission date. Those two together (but not separately) represent a unique identifier. I could see MR # becoming a unique identifier on its own in Access, and linking each MR # with (potentially) multiple admission dates, and then linking that date with the variable information related to each admission date. I'm not sure how I'd do all of this yet, but I'll get there. At least all of the data will be there!

Because of my inexperience with databases in general, I still haven't come up with a plan for how everything will work in Access. I'm assuming it'll be something like this:
Table 1 - Client Demographics (name, mr #, DOB - things which shouldn't change)
Table 2 - Admissions (mr #, admit date, admitting diagnosis, hospital info, etc)
Table 3 - Outcomes (filled out upon new assessment period or discharge)

Doing it this way would require an extra table (the information on Table 1 and Table 2 are combined in the Excel workbook, but doing it the same way in Access would lead to redundancy), but I'm not certain that this is a problem (or if this is the solution, for that matter!). I'd then be able to create the queries and reports involving data from all three tables. So I'd be able to determine, for example, how many discharges (Table 3) of patients born between 1935 and 1945 (Table 1) originally admitted from Mass General Hospital (Table 2) we had in March 2011 (Table 3).

I don't mean to bore everyone with this stream of consciousness - I want to verify that I'm on the right track.

Adam