Help with relationship setup? (Or, am I messing it up?)

I'm working on a database for a huge amount of information that realistically
is related ONLY by a person's ID number, unless I'm just not thinking of it
correctly. Basically we collect general demographic information and
information about the person entering the info (I lumped this all in as a
drop-down in the box because it's only one variable and we don't really need
another table to explain who's who). My "main table" is [Main], which
contains information on the following: General Case Information, Last
Specialist Visit, Last 12 month medical overview, and Life Events. I didn't
break these down into separate tables because each person will only have one
entry for each of them and I wanted them to be on the same form. Then I have
other tables: [Last Visit] which contains all visits to any provider on a
given day, [LVMDx] which contains last visit diagnoses from any of the visits
in the [Last Visit] table, [LSVDx] which is only diagnoses from the last
specialist visit, [SA] which contains information on substance use at various
points in time when it was evaluated, [SI] which contains other assessed
medical information at specific (different) timepoints, and [Events] which
contains the ID and year/time of event.

Is this a viable way to break down the information? The people I work with
prefer more information in one table to a lot of information in other places,
so for the most part all my relationships are one-to-many and based on ID.
Earlier on I attempted to integrate the [Last Visit] with the [Main] table
and ended up in a situation where you could add a new subject but evidently
not enough of a new subject to "make it stick" in form view - so it would be
a poor sad little nothing in the table, and I'm hoping to find a way to avoid
that. I don't know if it was related to my setup, or ???

Also, was having errors with multiple people trying to access the DB at
once. I'm not sure if that was a remote server problem or if it was a bad
database problem, but if anyone has any light to shed I would appreciate it.

Hold on a sec...

If the ONLY reason you are putting everything into one table is so that your
FORM can have it all, step away from the PC!

Access tables are NOT like Excel spreadsheets ... you don't have to put it
all in one place.

Access is a relational database ... both you and Access have to work
overtime when you feed it 'sheet data.

Access tables store data, Access forms and reports display it, and Access
queries gather together all the pieces that you want to display. Use a
query to assemble data for display in a form.

That said, here's three areas I recommend getting ahead of the learning
curve to use Access to create an effective application (i.e., one that gets
used):

1) relational data base design and normalization
2) Access tips and tricks (how Access does it)
3) Graphical user interface design (how to help the user get the job
done)

Oh yes, you need to know how to do software development projects too...g

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Allie" wrote in message
...
I'm working on a database for a huge amount of information that
realistically
is related ONLY by a person's ID number, unless I'm just not thinking of
it
correctly. Basically we collect general demographic information and
information about the person entering the info (I lumped this all in as a
drop-down in the box because it's only one variable and we don't really
need
another table to explain who's who). My "main table" is [Main], which
contains information on the following: General Case Information, Last
Specialist Visit, Last 12 month medical overview, and Life Events. I
didn't
break these down into separate tables because each person will only have
one
entry for each of them and I wanted them to be on the same form. Then I
have
other tables: [Last Visit] which contains all visits to any provider on a
given day, [LVMDx] which contains last visit diagnoses from any of the
visits
in the [Last Visit] table, [LSVDx] which is only diagnoses from the last
specialist visit, [SA] which contains information on substance use at
various
points in time when it was evaluated, [SI] which contains other assessed
medical information at specific (different) timepoints, and [Events] which
contains the ID and year/time of event.

Is this a viable way to break down the information? The people I work with
prefer more information in one table to a lot of information in other
places,
so for the most part all my relationships are one-to-many and based on ID.
Earlier on I attempted to integrate the [Last Visit] with the [Main] table
and ended up in a situation where you could add a new subject but
evidently
not enough of a new subject to "make it stick" in form view - so it would
be
a poor sad little nothing in the table, and I'm hoping to find a way to
avoid
that. I don't know if it was related to my setup, or ???

Also, was having errors with multiple people trying to access the DB at
once. I'm not sure if that was a remote server problem or if it was a bad
database problem, but if anyone has any light to shed I would appreciate
it.

On Tue, 14 Oct 2008 11:51:03 -0700, Allie
wrote:

I'm working on a database for a huge amount of information that realistically
is related ONLY by a person's ID number, unless I'm just not thinking of it
correctly. Basically we collect general demographic information and
information about the person entering the info (I lumped this all in as a
drop-down in the box because it's only one variable and we don't really need
another table to explain who's who). My "main table" is [Main], which
contains information on the following: General Case Information, Last
Specialist Visit, Last 12 month medical overview, and Life Events. I didn't
break these down into separate tables because each person will only have one
entry for each of them and I wanted them to be on the same form.

It sounds like you're on the wrong track. Unless you want to erase all visits
except the last, all overviews except the last, and record one and only one
Life Event, I see at least four tables here - Demographics, related one to
many to Visits, Overviews (depending on what's in the overview), and
LifeEvents. DON'T assume that you must have all this information in one table;
you're using a relational database, and joining information from multiple
tables is bread and butter to Access.

Then I have
other tables: [Last Visit] which contains all visits to any provider on a
given day, [LVMDx] which contains last visit diagnoses from any of the visits
in the [Last Visit] table, [LSVDx] which is only diagnoses from the last
specialist visit, [SA] which contains information on substance use at various
points in time when it was evaluated, [SI] which contains other assessed
medical information at specific (different) timepoints, and [Events] which
contains the ID and year/time of event.

Several many to many relationships here.

Is this a viable way to break down the information? The people I work with
prefer more information in one table to a lot of information in other places,

They're wrong, but then they should not care how the data is STORED, just how
it's presented.

so for the most part all my relationships are one-to-many and based on ID.
Earlier on I attempted to integrate the [Last Visit] with the [Main] table
and ended up in a situation where you could add a new subject but evidently
not enough of a new subject to "make it stick" in form view - so it would be
a poor sad little nothing in the table, and I'm hoping to find a way to avoid
that. I don't know if it was related to my setup, or ???

It is.

Also, was having errors with multiple people trying to access the DB at
once. I'm not sure if that was a remote server problem or if it was a bad
database problem, but if anyone has any light to shed I would appreciate it.

Post some information about the nature of the problems.
--

John W. Vinson [MVP]

"John W. Vinson" wrote:

On Tue, 14 Oct 2008 11:51:03 -0700, Allie
wrote:

I'm working on a database for a huge amount of information that realistically
is related ONLY by a person's ID number, unless I'm just not thinking of it
correctly. Basically we collect general demographic information and
information about the person entering the info (I lumped this all in as a
drop-down in the box because it's only one variable and we don't really need
another table to explain who's who). My "main table" is [Main], which
contains information on the following: General Case Information, Last
Specialist Visit, Last 12 month medical overview, and Life Events. I didn't
break these down into separate tables because each person will only have one
entry for each of them and I wanted them to be on the same form.

It sounds like you're on the wrong track. Unless you want to erase all visits
except the last, all overviews except the last, and record one and only one
Life Event, I see at least four tables here - Demographics, related one to
many to Visits, Overviews (depending on what's in the overview), and
LifeEvents. DON'T assume that you must have all this information in one table;
you're using a relational database, and joining information from multiple
tables is bread and butter to Access.

I have all of these as separate tables. The "Main" table contains any
information that only is to be entered once per person, ever. I couldn't
figure out what reason there would have been to separate them.

Then I have
other tables: [Last Visit] which contains all visits to any provider on a
given day, [LVMDx] which contains last visit diagnoses from any of the visits
in the [Last Visit] table, [LSVDx] which is only diagnoses from the last
specialist visit, [SA] which contains information on substance use at various
points in time when it was evaluated, [SI] which contains other assessed
medical information at specific (different) timepoints, and [Events] which
contains the ID and year/time of event.

Several many to many relationships here.

The way I have related them is ID (primary key of demographics) to ID -
(foreign key of each related table). I don't understand why I'd want to link
other things, or link each table to all other tables. Should every variable
that could potentially be linked always be linked to everything it could
relate to? That just seems nightmarish somehow when you have a total of 7
tables...

Is this a viable way to break down the information? The people I work with
prefer more information in one table to a lot of information in other places,

They're wrong, but then they should not care how the data is STORED, just how
it's presented.


Basically, what's going to become of this database is that a handful of
people people will use it in lieu of paper forms while we review medical
charts and collect specific pieces of information. It's focused on form entry
primarily because our stats people are going to want it reformatted and
imported into their programs outside of all things Microsoft. I'm sure
there's probably some way to turn all of my entry-friendly drop down menus
into a numeric system probably through another table and more relationships
(?), but that seems like overkill. Or is it?

so for the most part all my relationships are one-to-many and based on ID.
Earlier on I attempted to integrate the [Last Visit] with the [Main] table
and ended up in a situation where you could add a new subject but evidently
not enough of a new subject to "make it stick" in form view - so it would be
a poor sad little nothing in the table, and I'm hoping to find a way to avoid
that. I don't know if it was related to my setup, or ???

It is.

Would it fix it to separate out [Last Visit] from [Main]? That's what I was
going to work on trying next - I've never run into this problem before where
my data just vanishes from a form...

Also, was having errors with multiple people trying to access the DB at
once. I'm not sure if that was a remote server problem or if it was a bad
database problem, but if anyone has any light to shed I would appreciate it.

Post some information about the nature of the problems.
--

My DB is stored on another server that we access through Citrix. Usually
with the DBs I've worked with, multiple people can work in the database at
the same time, just not in the same record. But when my coworker was
connected and I went to connect, it told me that it was locked by an
administrator on the server... not sure what to make of that.

John W. Vinson [MVP]

On Tue, 14 Oct 2008 18:39:00 -0700, Allie
wrote:

The way I have related them is ID (primary key of demographics) to ID -
(foreign key of each related table). I don't understand why I'd want to link
other things, or link each table to all other tables. Should every variable
that could potentially be linked always be linked to everything it could
relate to? That just seems nightmarish somehow when you have a total of 7
tables...

HUH!? Nobody suggested that, that I can see. I think you have it right - ID
primary key linked to ID foreign key. You would certainly NOT try to link
every table to every other table, only the actual, logical links.
--

John W. Vinson [MVP]

"John W. Vinson" wrote:

On Tue, 14 Oct 2008 18:39:00 -0700, Allie
wrote:

The way I have related them is ID (primary key of demographics) to ID -
(foreign key of each related table). I don't understand why I'd want to link
other things, or link each table to all other tables. Should every variable
that could potentially be linked always be linked to everything it could
relate to? That just seems nightmarish somehow when you have a total of 7
tables...

HUH!? Nobody suggested that, that I can see. I think you have it right - ID
primary key linked to ID foreign key. You would certainly NOT try to link
every table to every other table, only the actual, logical links.
--

John W. Vinson [MVP]


That makes sense... sorry. (I'm not really a computer person - I'm a
research assistant who happens to know a little bit about how to use a
computer, so making this has become my job.) Thanks for your help. Wondering
if anyone can shed light on this one - I have (because people don't want to
enter codes for things, they want to pick their response from combo boxes) a
million and a half drop-down boxes of the same responses. I'd really like to
put all those responses in some kind of ResponseID table and link it to my
variables. But the problem is, it's the responses for everything, and I'm
worried that that will create problems. Is that something people do
successfully? For most questions the responses are "Yes/No/Not Applicable/Not
Mentioned", though there are some variations. I think they only want the
options available to that particular item to show up, so I would need a few
response tables for different types of responses. So I thought I could then
make my comboboxes pull the values from the response tables and store the
response IDs in the fields of the visit tables? Does that work? Sorry for all
the questions... usually the DBs I work with are structured a lot differently
from this.

Allie

This sounds a little like it might be related to a survey or a questionnaire
or a test. If so, take a look at the excellent work Duane H. has done
setting up an Access application to help you generate a well-normalized
"survey" database:

http://www.rogersaccesslibrary.com/O...p#Hookom,Duane

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Allie" wrote in message
...
"John W. Vinson" wrote:

On Tue, 14 Oct 2008 18:39:00 -0700, Allie

wrote:

The way I have related them is ID (primary key of demographics) to ID -
(foreign key of each related table). I don't understand why I'd want to
link
other things, or link each table to all other tables. Should every
variable
that could potentially be linked always be linked to everything it could
relate to? That just seems nightmarish somehow when you have a total of
7
tables...

HUH!? Nobody suggested that, that I can see. I think you have it right -
ID
primary key linked to ID foreign key. You would certainly NOT try to link
every table to every other table, only the actual, logical links.
--

John W. Vinson [MVP]


That makes sense... sorry. (I'm not really a computer person - I'm a
research assistant who happens to know a little bit about how to use a
computer, so making this has become my job.) Thanks for your help.
Wondering
if anyone can shed light on this one - I have (because people don't want
to
enter codes for things, they want to pick their response from combo boxes)
a
million and a half drop-down boxes of the same responses. I'd really like
to
put all those responses in some kind of ResponseID table and link it to my
variables. But the problem is, it's the responses for everything, and I'm
worried that that will create problems. Is that something people do
successfully? For most questions the responses are "Yes/No/Not
Applicable/Not
Mentioned", though there are some variations. I think they only want the
options available to that particular item to show up, so I would need a
few
response tables for different types of responses. So I thought I could
then
make my comboboxes pull the values from the response tables and store the
response IDs in the fields of the visit tables? Does that work? Sorry for
all
the questions... usually the DBs I work with are structured a lot
differently
from this.