A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Database (re)design help



 
 
Thread Tools Display Modes
  #1  
Old October 24th, 2008, 08:04 PM posted to microsoft.public.access.tablesdbdesign
chris
external usenet poster
 
Posts: 2,039
Default Database (re)design help

Hello,

I started a small database many years ago in access that kept track of some
patient details - I work in a GP surgery - and their recalls. This has grown
itself out to holding certain data and results of all patients and keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent an
invitation. And this is my headache. When this setup was introduced the idea
was that I remove patients from the InvitationsTable after 3 non-attendance
but some time ago the policy has changed. In the LastSent now I just enter
the month I'm sending the patient but this way loads of data may get lost if
the patient is a particularly lazy one, and we have quite a few of that. Also
my left thumb is tired of going to the latest Sent field. I'm really hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and quite
comfortably find my way around tables, queries and forms, but I do find some
of the more complex processes... well, a bit complex. Thank you very much for
your help if any.
  #2  
Old October 26th, 2008, 06:07 AM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Database (re)design help

honestly, Chris, i don't think anyone in the newsgroups wants to touch this
one with a ten-foot pole; it has "headache" written all over it. from the
scanty description of the tables, it's pretty clear that the tables are not
normalized, and trying to work within the current structure will be just
miserable - especially remotely, in a newsgroup forum. since it sounds like
the data is important to you, i suggest you look for a local developer in
your area to analyze your business process related to this task, build a new
database to properly house existing and future data, and write a new
application to streamline your day-to-day workflow related to the database.

if you want to tackle it yourself, then i very strongly recommend that you
begin by putting in the time and effort to learn relational design
principles well. next, analyze your own process carefully. then, and only
then, design a new database and user interface based on a sound relational
structure.

hth


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of

some
patient details - I work in a GP surgery - and their recalls. This has

grown
itself out to holding certain data and results of all patients and keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient

needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent an
invitation. And this is my headache. When this setup was introduced the

idea
was that I remove patients from the InvitationsTable after 3

non-attendance
but some time ago the policy has changed. In the LastSent now I just enter
the month I'm sending the patient but this way loads of data may get lost

if
the patient is a particularly lazy one, and we have quite a few of that.

Also
my left thumb is tired of going to the latest Sent field. I'm really

hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations

but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion

was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it

has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and quite
comfortably find my way around tables, queries and forms, but I do find

some
of the more complex processes... well, a bit complex. Thank you very much

for
your help if any.



  #3  
Old October 27th, 2008, 07:34 AM posted to microsoft.public.access.tablesdbdesign
chris
external usenet poster
 
Posts: 2,039
Default Database (re)design help

Hello,

Thank you for the insight. Maybe I should approach my question from another
angle. If even then nobody wants to touch it, then fine.

What I detailed in the original post is how the database looks now. I'm
aware it needs redesigning because it's not efficient any more. Most of the
data that I use is gained from excel files - that's the only way I can do it
-and as far as I'm concerned the whole project can be started from scratch.
What I'm at loss about is I don't know which way to start. I feel like I have
the right tools in my hand to do this I just don't know how to put these
tools to use.

Thanks anyway for the reply.


Chris


"tina" wrote:

honestly, Chris, i don't think anyone in the newsgroups wants to touch this
one with a ten-foot pole; it has "headache" written all over it. from the
scanty description of the tables, it's pretty clear that the tables are not
normalized, and trying to work within the current structure will be just
miserable - especially remotely, in a newsgroup forum. since it sounds like
the data is important to you, i suggest you look for a local developer in
your area to analyze your business process related to this task, build a new
database to properly house existing and future data, and write a new
application to streamline your day-to-day workflow related to the database.

if you want to tackle it yourself, then i very strongly recommend that you
begin by putting in the time and effort to learn relational design
principles well. next, analyze your own process carefully. then, and only
then, design a new database and user interface based on a sound relational
structure.

hth


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of

some
patient details - I work in a GP surgery - and their recalls. This has

grown
itself out to holding certain data and results of all patients and keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient

needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent an
invitation. And this is my headache. When this setup was introduced the

idea
was that I remove patients from the InvitationsTable after 3

non-attendance
but some time ago the policy has changed. In the LastSent now I just enter
the month I'm sending the patient but this way loads of data may get lost

if
the patient is a particularly lazy one, and we have quite a few of that.

Also
my left thumb is tired of going to the latest Sent field. I'm really

hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations

but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion

was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it

has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and quite
comfortably find my way around tables, queries and forms, but I do find

some
of the more complex processes... well, a bit complex. Thank you very much

for
your help if any.




  #4  
Old October 27th, 2008, 04:44 PM posted to microsoft.public.access.tablesdbdesign
Fred
external usenet poster
 
Posts: 1,451
Default Database (re)design help

Chris,

For a situation like yours:

Step 1 Whether you are doing it for yourself or to post the question to the
newsgroup, your first step is to define the nature, structure and
relationships of the data that you wish to store, and the mission of what you
want to accomplish. You are probably assuming that you have done this by
providing info on your current DB design attempt, but you have not.

Step 2 Then design a table structure that stores the above data in a way
that supports accomplishment of your mission.

Step 3 Then design queries, forms and reports that accomplish you mission.

Sincerely,

Fred



  #5  
Old October 27th, 2008, 06:56 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default Database (re)design help

Chris

To add to the excellent observations you've received so far, I typically
advise folks who want to use Access to create an application that they need
to plan on spending time coming up to speed on three separate learning
curves.

1. Relational database design and normalization
2. Access tricks/techniques
3. User(-friendly) graphical interface design

Oh yes, one more ... experience developing software.

If you don't think you'll be able to dedicate the time and resources for all
of these, consider looking for someone who already has.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of
some
patient details - I work in a GP surgery - and their recalls. This has
grown
itself out to holding certain data and results of all patients and keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient
needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent an
invitation. And this is my headache. When this setup was introduced the
idea
was that I remove patients from the InvitationsTable after 3
non-attendance
but some time ago the policy has changed. In the LastSent now I just enter
the month I'm sending the patient but this way loads of data may get lost
if
the patient is a particularly lazy one, and we have quite a few of that.
Also
my left thumb is tired of going to the latest Sent field. I'm really
hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations
but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion
was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it
has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and quite
comfortably find my way around tables, queries and forms, but I do find
some
of the more complex processes... well, a bit complex. Thank you very much
for
your help if any.



  #6  
Old October 28th, 2008, 03:49 PM posted to microsoft.public.access.tablesdbdesign
tina
external usenet poster
 
Posts: 1,997
Default Database (re)design help

take a look at http://home.att.net/~california.db/tips.html, focusing first
on Tips 1 and 2.

hth


"tina" wrote in message
...
honestly, Chris, i don't think anyone in the newsgroups wants to touch

this
one with a ten-foot pole; it has "headache" written all over it. from the
scanty description of the tables, it's pretty clear that the tables are

not
normalized, and trying to work within the current structure will be just
miserable - especially remotely, in a newsgroup forum. since it sounds

like
the data is important to you, i suggest you look for a local developer in
your area to analyze your business process related to this task, build a

new
database to properly house existing and future data, and write a new
application to streamline your day-to-day workflow related to the

database.

if you want to tackle it yourself, then i very strongly recommend that you
begin by putting in the time and effort to learn relational design
principles well. next, analyze your own process carefully. then, and only
then, design a new database and user interface based on a sound relational
structure.

hth


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of

some
patient details - I work in a GP surgery - and their recalls. This has

grown
itself out to holding certain data and results of all patients and

keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some

other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient

needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent

an
invitation. And this is my headache. When this setup was introduced the

idea
was that I remove patients from the InvitationsTable after 3

non-attendance
but some time ago the policy has changed. In the LastSent now I just

enter
the month I'm sending the patient but this way loads of data may get

lost
if
the patient is a particularly lazy one, and we have quite a few of that.

Also
my left thumb is tired of going to the latest Sent field. I'm really

hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations

but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion

was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for

data,
one for date and one for the name of the bloodtests, but in form view it

has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and

quite
comfortably find my way around tables, queries and forms, but I do find

some
of the more complex processes... well, a bit complex. Thank you very

much
for
your help if any.





  #7  
Old October 28th, 2008, 05:11 PM posted to microsoft.public.access.tablesdbdesign
Steve[_57_]
external usenet poster
 
Posts: 598
Default Database (re)design help

Hello Chris,

I have designed table structures for numerous customers. My fee is very
modest. If you would like my help, contact me at .

Steve


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of
some
patient details - I work in a GP surgery - and their recalls. This has
grown
itself out to holding certain data and results of all patients and keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient
needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent an
invitation. And this is my headache. When this setup was introduced the
idea
was that I remove patients from the InvitationsTable after 3
non-attendance
but some time ago the policy has changed. In the LastSent now I just enter
the month I'm sending the patient but this way loads of data may get lost
if
the patient is a particularly lazy one, and we have quite a few of that.
Also
my left thumb is tired of going to the latest Sent field. I'm really
hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations
but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion
was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it
has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and quite
comfortably find my way around tables, queries and forms, but I do find
some
of the more complex processes... well, a bit complex. Thank you very much
for
your help if any.



  #8  
Old October 28th, 2008, 05:56 PM posted to microsoft.public.access.tablesdbdesign
Keith Wilby
external usenet poster
 
Posts: 812
Default Database (re)design help

"Steve" wrote in message
news
Hello Chris,


Get lost Steve. OP bewa

http://home.tiscali.nl/arracom/whoissteve.html

  #10  
Old October 29th, 2008, 12:45 AM posted to microsoft.public.access.tablesdbdesign
Steve[_57_]
external usenet poster
 
Posts: 598
Default Database (re)design help

Hello Chris,

Ignore the two posters here! I can provide the help you need. If you would
like my help, contact me at .

Steve



"Steve" wrote in message
news
Hello Chris,

I have designed table structures for numerous customers. My fee is very
modest. If you would like my help, contact me at
.

Steve


"Chris" wrote in message
...
Hello,

I started a small database many years ago in access that kept track of
some
patient details - I work in a GP surgery - and their recalls. This has
grown
itself out to holding certain data and results of all patients and
keeping
track of their recalls. Doesn't sound very different but I think the old
structure is very cumbersome to work with nowadays.

What I have:
A MainTable that holds patient details (contacts, test results, some
other
bits of info). All the details get updated every month with the latest
information through append and update queries from imported tables.

An InvitationsTable that holds the PatientID from MainTable and the
following fields:
TestID - A lookup field where I enter the name of the test the patient
needs
to be sent for
SendIn - the number of the month when the patient needs to be sent
CheckIn - the number of the month when I check whether the patient has
attended the test (generally the month following SendIn)
1stSent
2ndSent
3rdSent
4thSent
LastSent - these hold the number of the month when the patient was sent
an
invitation. And this is my headache. When this setup was introduced the
idea
was that I remove patients from the InvitationsTable after 3
non-attendance
but some time ago the policy has changed. In the LastSent now I just
enter
the month I'm sending the patient but this way loads of data may get lost
if
the patient is a particularly lazy one, and we have quite a few of that.
Also
my left thumb is tired of going to the latest Sent field. I'm really
hoping
there is an easier way to do this.

What I need is a setup which supports an easy-entry form for invitations
but
also makes it possible for me to query if a patient has more than 3
invitations in the previous 3 months, so we can recall them.

I have had discussions on this already but no conclusion. One suggestion
was
to keep a record of all invitations ever done - at present I delete the
invitations data if the patient attended or has been excluded from being
invited.

I have tried one-to-many relationships with three tables - one for data,
one for date and one for the name of the bloodtests, but in form view it
has
not been workable at all.

I am moving on to Access 2007 from 2003, am not a total beginner and
quite
comfortably find my way around tables, queries and forms, but I do find
some
of the more complex processes... well, a bit complex. Thank you very much
for
your help if any.





 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:44 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.