Complex Database - How do I accomplish the Goal?

[MStadnik]

I have a very complex database - I'll admit from the get go that I'm probably
in over my head.

Having said that... hiring a programmer is not really an option for our
small non-profit firm... so I put on my "database designer" hat, took a quick
and dirty Access class, and jumped right in. Gotta love working for a
nonprofit ;o)

The project:
A database that tracks information for our adult day clients. This
information includes basic participant info (primary table) - name, social,
address, phone, etc., then additional tables related to each participant
entitled - family-caregiver, funding, medical, plan of care, progress notes &
incident reports. In total there are 7 tables. The primary key in the
participant table is "participantID" which is an auto number field. This
field is then the foreign key to all the other tables. Each of these tables
has many fields and in some cases there can be multiple entries in one
category - such as up to four family members for each participant by type
(primary, paid, familymember1, familymember2, familymember3)

It occurred to me as I was designing the Forms that the data entry person
having to remember each participants auto generated "ID" to enter into the
other tables (to ensure that each participant is linked properly to the info
in the other tables) would be a real pain. So my first challenge is that I
want to bring in the participant's actual name instead - from a dropdown (I
guess) but I'm open on how best to accomplish this. I started to add this by
incorporating a lookup list based on the participant table and choosing the
fields - firstname, MI and LastName. This "works" sort of but you end up only
see the firstname of the participant in the field instead of a combination of
the 3 fields. That's when I showed up here and started reading about the
problems with combo boxes and am now totally confused as to how to accomplish
this.

My other challenge is that I want to create a form whereby the user can
easily enter the information for all the appropriate tables - my instructor
suggested the use of a "tab form". I created one starting with the
participant form and putting each of the other table's forms as subforms on
each tab. Worked sort of - but for some reason now my participant form (the
main tab) now shows no existing data - only an autonumber for entering a new
record and all the subforms seem to have all the data... that's a problem and
I'm not sure how this happened. But it also occurs to me that this may not be
the best way to accomplish what I'm trying to do - which is for the user to
only see the data for each participant - one at a time... right now you can
scroll through all the records on the subform and unless you know what you're
looking at it would be impossible to tell which family-caregiver records
relate to which participant.

So in summary - the two questions are:
1) How do I link the participant to his other tables in a more meaningful
way other than "participantID" which is a number?
and
2)What is the best way to design a form that would display the information
for each participant one at a time, rather than all the records for each form
which is what I seem to have now... if such a thing is possible?

Thanks for any help in pointing me in the right direction... the good news
is that I think the tables themselves are pretty well designed... I just need
a little help in organizing this so that it is user friendly and makes sense

 

[Klatuu]

Can you share with me what the non profit is and who sponsers it?

 

[Evi]

I'll answer the first bit of your question (I'd stick to 1 question per
letter if I were you)

Create a query based on your Participant Table

Add ParticipantID. In the next field write

FullName: [SurName] & ", " & [FirstName]

If you wish, you can add the Surname field to the query again so that you
can sort by that.

When you create your combo in your Form (and that IS the way to go, don't
get sucked into using those nasty lookups in tables or subdatasheets),
choose this Query instead of a table and when the wizard opens the query
pull the ID field closed so that you can't see it. The ID field will be the
Value of your combo and will be the one that you choose when the Wizard says
Store that Value in This field.

Evi

 

[George Nicholson]

1)

I started to add this by
incorporating a lookup list based on the participant table and choosing
the
fields - firstname, MI and LastName. This "works" sort of but you end up
only
see the firstname of the participant in the field instead of a combination
of
the 3 fields.

A combo will only display one field at a time when "at rest" so, *within the
query* you need to create one field that combines those 3 values.

Create a saved query called qcboParticipants. The SQL would be something
like:

SELECT ParticipantID, FirstName & " " & MI & " " & LastName as FullName
FROM tblParticipants
ORDER BY FirstName, LastName

Set the Rowsource of your combobox to qcboParticipants

Column Count =2
ColumnWidths = 0, 2 (hides the IDField. FullName becomes the first visible
field so it will show when "at rest")
Bound Column = 1 (the ID field)

This should make the ID number invisible to the user (which is fine), but it
is still the glue that holds things together.

2) The Rowsource for your participants form should be a query based on
tblParticipants (or the table itself, but a query is better). You should be
able to move from record to record and see all participants. Get this
working first.

Now, add a tab control. On one page of the tab, add a subform for one of
your sub-tables, say family-caregiver. In the properties of the new subform
control set Link Master/Child Fields to ParticipantID (a field both tables
have in common). This is what synchronizes the forms. There are other
methods, but this is the simplest non-code code method, so if you can go
this route, do so.

The result *should be*, as you scroll from record to record in participants,
you will only see the Family/Caregiver records related to the currently
displayed participant.

Add another page to the tab, add another subtable, etc., Repeat

 

[MStadnik]

Hi,
Yes... I don't see why I can't share that info. We aren't sponsored by
anyone really - we are funded by a variety of sources including state
medicaid programs, private donations and grants. The name of our organization
is Bennington Project Independence - we are an adult day center/day health
rehabilitation center located in Bennington, VT.

 

[MStadnik]

Sorry about the multiple questions in one post. Thanks for the info. I'm
still quite a newbie and have really only used queries from the design
screen... this is different?

I'll answer the first bit of your question (I'd stick to 1 question per
letter if I were you)

Create a query based on your Participant Table

Add ParticipantID. In the next field write

FullName: [SurName] & ", " & [FirstName]

If you wish, you can add the Surname field to the query again so that you
can sort by that.

When you create your combo in your Form (and that IS the way to go, don't
get sucked into using those nasty lookups in tables or subdatasheets),
choose this Query instead of a table and when the wizard opens the query
pull the ID field closed so that you can't see it. The ID field will be the
Value of your combo and will be the one that you choose when the Wizard says
Store that Value in This field.

Evi

I have a very complex database - I'll admit from the get go that I'm probably
in over my head.

Having said that... hiring a programmer is not really an option for our
small non-profit firm... so I put on my "database designer" hat, took a quick
and dirty Access class, and jumped right in. Gotta love working for a
nonprofit ;o)

The project:
A database that tracks information for our adult day clients. This
information includes basic participant info (primary table) - name, social,
address, phone, etc., then additional tables related to each participant
entitled - family-caregiver, funding, medical, plan of care, progress notes &
incident reports. In total there are 7 tables. The primary key in the
participant table is "participantID" which is an auto number field. This
field is then the foreign key to all the other tables. Each of these tables
has many fields and in some cases there can be multiple entries in one
category - such as up to four family members for each participant by type
(primary, paid, familymember1, familymember2, familymember3)

It occurred to me as I was designing the Forms that the data entry person
having to remember each participants auto generated "ID" to enter into the
other tables (to ensure that each participant is linked properly to the info
in the other tables) would be a real pain. So my first challenge is that I
want to bring in the participant's actual name instead - from a dropdown (I
guess) but I'm open on how best to accomplish this. I started to add this by
incorporating a lookup list based on the participant table and choosing the
fields - firstname, MI and LastName. This "works" sort of but you end up only
see the firstname of the participant in the field instead of a combination of
the 3 fields. That's when I showed up here and started reading about the
problems with combo boxes and am now totally confused as to how to accomplish
this.

My other challenge is that I want to create a form whereby the user can
easily enter the information for all the appropriate tables - my instructor
suggested the use of a "tab form". I created one starting with the
participant form and putting each of the other table's forms as subforms on
each tab. Worked sort of - but for some reason now my participant form (the
main tab) now shows no existing data - only an autonumber for entering a new
record and all the subforms seem to have all the data... that's a problem and
I'm not sure how this happened. But it also occurs to me that this may not be
the best way to accomplish what I'm trying to do - which is for the user to
only see the data for each participant - one at a time... right now you can
scroll through all the records on the subform and unless you know what you're
looking at it would be impossible to tell which family-caregiver records
relate to which participant.

So in summary - the two questions are:
1) How do I link the participant to his other tables in a more meaningful
way other than "participantID" which is a number?
and
2)What is the best way to design a form that would display the information
for each participant one at a time, rather than all the records for each form
which is what I seem to have now... if such a thing is possible?

Thanks for any help in pointing me in the right direction... the good news
is that I think the tables themselves are pretty well designed... I just need
a little help in organizing this so that it is user friendly and makes sense

 

[MStadnik]

Hi,
Thanks for the info... I'll play with the info I received here and if I have
more questions afterwards I'll be back. Thanks a bunch!

 

[MStadnik]

Well... frustrated... I can't figure out how to "write" these queries. I'm
guessing it's in the expression builder but I keep getting the error "the
expression you entered contains invalid syntax". I'm not getting it

 

[John W. Vinson/MVP]

Well... frustrated... I can't figure out how to "write" these queries. I'm
guessing it's in the expression builder but I keep getting the error "the
expression you entered contains invalid syntax". I'm not getting it

Create a new query in the query window - don't add any tables.

Select View... SQL from the Menu to get into the SQL query editor. The query
grid isn't really the query - it's just a tool to create SQL, which is the
"real" query.

Copy and paste the suggested queries from the posts here; edit your
tablename and fieldname to match your actual object names.

Note that you will need Forms - not just queries!! - to make effective use
of Access. To answer your question about users needing to memorize ID
numbers: users should not need to even *SEE* ID numbers, much less type them
or memorize them! You can have a Form based on your Individuals table with
Subforms based on the related tables, using the ID as the subform's
master/link field property to automatically synch them.

Also, your family table needs rethinking. "Fields are expensive, records are
cheap" - rather than four *fields* for children, you need a one to many
relationship with one child per record.

Good luck with the project, and don't hesitate to post back!