Merging files, layout for data input

Hi!

I've previously only used excel/access very little, but now I need to use it in a patient research project. There are some issues that I have not grasped, and I hope some of you can point me in the right direction.

I am in the beginning of the project, and I'm planning on using Excel (or Access) as the basis for my data. SPSS will be used for statistical analysis.

Now, there will be about 200 subject (rows) and several data variables (columns) involved. Some of the variables will be numbered, and some will be multiple choice.

As the data input will be done by different people, I want to create several excel-sheets for the different people. These sheets will include a patient ID (1,2,3...etc), which I'm planning to use as a common identifier. They will include different variables, though.

What I would like to achieve is creating a master sheet based on all those excel-sheets. This master sheet should then include all variables and all patients. I then plan to single out groups of different variables for analysis in SPSS.

Now, how should I start from here? I've tried to google the merge process, but it's a bit over my head at the moment. Also, am I better off creating a database in access for this kind of project (I must say that I have never worked with access). Hopfully some of you out there will be able to get me started.

Hi,

Can you clarify and confirm please. When you say you want to create several excel sheets, do you really mean that you want several different workbooks, one for each of several people to fill in, or do you literally mean several sheets in the same workbook?

If the latter I'd question why you need several sheets since realistically and unless you're using workbook sharing, only one person can be using the workbook at the same time for data entry - (although of course there can be separate read only copies).

If you can clear up this point we can probably advise.

Regards

OK

If the ultimate requirement is a statistical analysis then I think you'll be better using Excel as the analysis tool rather than Access. From a purely professional advice point of view it would seem the best bet would be an Access database which would then be imported into Excel for subsequent analysis.

However from a pragmatic point of view and unless you really want to learn Access, a database in Excel is perfectly OK.

I see three initial key steps.

1 Decide exactly what data you want to collect and on a dedicated sheet create labels for each field of data on row 1. Don't forget to include fields like the record date, and the user name of the person who entered the data, and there may be other 'admin' type fields which you need. The important thing is to try and think of every conceivable bit of information you may want. Far better to get a complete list now than to have to worry about adding fields later on. Some fields may be calculated fields based on information in other fields. Include these now.

2. On another sheet design a record input sheet which should include a cell for every field of information, apart from calculated fields which can be worked out in the background.

3. Decide which of the input cells are simple free format fields and which should be restricted to permitted values only. You'll then need to create validation tables for all these allowable choices and create Data Validation options for these cells.

Once you've got all the basics designed then you can move on to the next stage, and I suggest you revisit the forum when you've got the basics set up.

The next stages will be to create macros which will respond to a data entry and add new records to the database, and the corollary - deleting records from the database.

At that stage the basic template should be available for releasing to the users.

the final stage will be to create a master statistical analysis workbook which will contain macros for loading all the data sheets from the individual data entry workbooks. You'll need to give consideration as to whether once a data set has been uploaded to the master workbook, it can be deleted from the originating workbook so that this starts out with no records ready for the next updating process, otherwise the system will need to test for duplicates before uploading to the master. However first things first.

HTH