database design - how to manage one huge table

[andrei2003]

I'm trying to make a simple database (medical) for my research - the problem: I need just one table but with 400 fields (columns) Access doesn't allow me to make so many fields in one table - what way should I go?

how should I link separate tables?
what is the most durable and simple way?

andrei

 

[mpastore]

Use an auto-number field for the first table, create a number field (long integer) on the second table and give it the same number from the corresponding autonumber field in table1.

For instance:

table1

fld1(autonumber) PatientName
1 Smith
2 Jones
3 Brown
etc

table2
fld1 (number) ICD9, etc
1
2
3
etc

Note you will have to set up some method to keep tables in synch when adding, changing records, etc.

The minimum I would do is establish relationships between both tables so that a record cannot be added to table2 without a record with same number first being in table1. This is called referential integrity. Also you can make it so that if you delete a record from table1, the corresponding record from table2 will get deleted.

Look in MS Access help for info on ref. integrity.

Mike Pastore

Hats off to (Roy) Harper

 

[andrei2003]

Thank you Mike
That's what I needed
a right direction

Andrei

 

[fneily]

I would suggest you take a basic to intermediate course in Access. What you have is just a big Excel list in the wrong application. If you "Normalized" your data, you would, more then likely, have four or so tables with 1/5 the typing involved. And you still have future data to content with. You have alot of duplication and repeatitive data. You're also going to want to do some stat. analy. on your data. Again, Excel is the better choice. Why are you using Access anyway if you're not going to construct your tables correctly? Combining my masters in Applied Mathematics from M.I.T and my knowledge of Access, I've set up research databases for a pet hospital, to NIH, to Johns Hopkins Hosiptal. You are heading down the wrong road.

Neil

 

[mpastore]

Sometimes normalized data is not the best route, especially for reporting applications. If this is static data that comes from external utilization or billing systems you should be fine. If you are updating this data real-time, then maybe some degree of normalization will make sense.

Good luck

Mike Pastore

Hats off to (Roy) Harper

 

[fneily]

Yeah, there's nothing like doing medical research analysis like finding correlation coefficients, chi-squares, and negative binomial distributions in Access.

 

[johnnymac43]

And there's nothing like pushing your ideas without regard to what the helpee really wants to do. Bill Gates made it easy to keep the data where it fits best, and to do the complex calculations where they fit best, so why not take advantage of this capability.

John

 

[andrei2003]

Thank you for your help geeks,

In this case my choice is Access because it will be easier to manage data entry in forms. This research has many follow-up data and organized forms for data entry will eliminate confusion (at least I hope so)

also I'll need to run a lot of queries..
and it's easy enough to convert everything into Excel

so far it worked all right in my previous projects
just this time it's got bigger than my experience

andrei

 

[MichaelRed]

One word of caution. Ms. A. -along with most of the relational db 'universe'- limits the number of fields in a record to 255. Unfortunatly, in this instance, 'fields' are counted for other 'objects' in the recordset, such as indicies, Joins and other arcania, so a 400 'field' record will never be able to be displayed from a single 'record', and will require SOME additional learning re the relational db concept.

fneily while I infer some scarcasm in your post, it is quite true. It is also quite pratical. Most of the stat functions in Ms. Excel are available to Ms. A. users with t he simple inclusion of the Excel library as a reference. There are several to numerous examples of such usage in these fora, some of which deal with some of hte stat functions.

On the otherhand, if the ONLY rationale for having the 'data' in Ms. A. is the use of "Forms" for data entry, then that is equally falacious, as Excel supports the use of forms.

So, in the end it seems -to a programmer- quite shakesperian (as in MUCH ADO ABOUT NOTHING).




MichaelRed
m.red@att.net

Searching for employment in all the wrong places