Nice people thread part 3- Nice as pie

tomterm8

Sounds like any modern database program, including Access, will work just fine with it. People who complain about MS Access are usually techies who want to do more difficult things than access is really designed for. But you have a bog standard database.

Databases are significantly more difficult than spreadsheets, though, I agree with PasturesNew on that... for a start you need to be able to get the tables into third normal form.

misskool

time to start a steep learning curve then.

bah. wanted easy solution

PasturesNew

misskool wrote: »

I have a number of studies, with different patients, each patient has numerous samples stored in different locations. I need a magic computer whizzy thing that tells me exactly where everything is for that single patient.

so far, I have 4 excel spreadsheets and it's getting painful....

That's not really enough detail to instantly suggest something, but thinking in database terms, I'd have to make some assumptions.

I assume there are:
- many studies
- many patients
- one patient can be part of many studies
- one patient will have many samples

So essentially it's four tables:
- studies: name of it, date, details
- patients: name, address, DoB, etc
- sampletypes: type of samples
- samplestaken: an actual instance of a sample

The 4th table samplestaken contains the data from when one sample is taken from one patient, for one study. Samples will bring in:
- uniqueID of the study
- uniqueID of the patient
- uniqueID of sampletype
- sampleDate - date it was taken

Once that's built you just need one input form so you're just inputting today's sample data. How that'd work would depend on quite a few things and quite a lot of knowledge if done in Access.

I've no easy solution to suggest I'm afraid. Databases are "my thing", but I've not touched one for years, just got the magical degree-level bits of paper in it and never had a need to use it.

Maybe your spreadsheets just need tidying up/automating a bit.

PasturesNew

tomterm8 wrote: »

. for a start you need to be able to get the tables into third normal form.

We could normalise it for her easily enough, if we understand how it'll be used so we don't c0ck it up from the start

Does one study have many patients, or does one patient have many studies, or is it both - and which is most commonly used from an admin perspective.

Moving onto programming, having selected the first study/person, does the second box (person/study) auto-populate or not.

... and these reports .....

We could be here weeks. I'll probably hide for most of them.

tomterm8

I spent two years programming databases, ERPs and the like when I got out of university, and I used to use Oracle and SQLServer, but I am so rusty I'd probably have to get a book out and teach myself it all again. It's funny how these things fade And I never was much good at Access... when you are programming it is not the best choice.

misskool

PasturesNew wrote: »

I've no easy solution to suggest I'm afraid. Databases are "my thing", but I've not touched one for years, just got the magical degree-level bits of paper in it and never had a need to use it.

Maybe your spreadsheets just need tidying up/automating a bit.

:eek: You've just summed that in 30 seconds what I've been trying to work out in a whole day :rotfl: The only minor difference is that there is one patient for each study but each patient has multiple samples from different dates of different types.

No chance of getting the spreadsheets tidy. One study has a spreadsheet of 60 columns in one tab

That will be most of the rest of next week then

edit to add: I have a normaliser, there is a unique study id for each patient

tomterm8

Well...

Can you list the headers of each spreadhseet?

Not the information, of course.

misskool

Please don't quote this, i will have to remove it....

Spreadsheet 1, main spreadsheet
Pat-ID
Neoadjuvant
DOB
Initials
Hospital
Diagnosis
Incl1
Incl2
Incl3
InclAll
Excl1
Excl2
Excl3
ExclAll
DOReg
SCBase-Done
Surgery-Date
Surgery-Type
Surgery-Type-Dif
Histo
Histo-dif
ER
PR
IHC
FISH
and so forth for another 40 columns

Spreadsheet 2
Pat ID
Sample type
date sample take
location of sample

Spreadsheet 3
Pat ID
Histo number
Histology sample
Location

Spreadsheet 4
Pat ID
TA number

tomterm8

Hm... it's a little difficult to talk about this without including the terms you posted, but IIRC as a board guide you have the ability to remove or edit our posts? If so, that is what you should do if we overstep the boundaries... or PM, but I am going out soon so might not see it til tomorrow...

You can see a number of tables, there, that you probably need to split out. For example, there is a Diagnosis table, in your main spread sheet... presumably many patients can have many Diagnosis , so you need to split that out and tidy it up.

Similarly, there is an obvious surgery table, there. Presumably that is another many to many.

Hospital comes up as another table, and presumably a many to many relationship. I don't see a doctor table there, but that is another obvious many to many.

Presumably, a lot of the fields in the first spreadsheet relate to medical observation, which is a minefield... to normalize... because each patient can have many medical observations of different types at different points in the study, while being cared for by different doctors...


The reason you are having so many difficulties with these spread sheets is they are completely unnormalized, so as they get bigger it becomes more and more difficult to work with them. The problem isn't so much you are using spreadsheets, as there is not enough design... just IMHO, of course.

vivatifosi

misskool wrote: »

I have a number of studies, with different patients, each patient has numerous samples stored in different locations. I need a magic computer whizzy thing that tells me exactly where everything is for that single patient.

so far, I have 4 excel spreadsheets and it's getting painful....

If you don't want to go to a database, you could go to a separate excel spreadsheet that hyperlinks to the relevant data in each separate table.

Or could you have different worksheets and pivot tables?

Just a different approach. I like databases, personally I go down the db route at the drop of a hat, but you've got to work with what's best for you so I thought I'd chuck an alternative in for you.

Don't forget you can use database wizards in Access to make it a bit easier.