Tables/Relationships

[darwehner]

I have 3 tables:
1. tblPatient (PatientID) as primary key
2. tblTherapy (PatientID) as foreign key
3. tblDays (numbered 1-100) PatientID as foreign key

I have main form with 1st table.
I need subform with table 2 & 3 but it must show
at least 14 days in datasheet view even if empty records.

The tblTherapy has fields as PT, OT, ST which will track
minutes therapist sees patient. We need to be able to see
the 14 days, so the therapist can get an idea where patient
needs to make up minutes.

Any help would be appreciated

 

[Tom Ellison]

Dear Dar:

If you have a table of patients, therapies, and days, it would appear
you need a many-to-many junction table to represent the sets of
therapies that occur for a given patient on each day. I cannot see
how your current design is intended to work. Perhaps you would need
to give us an example of a few rows of data in each table so we can
see the mechanics you intend.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

-----Original Message-----
Dear Dar:

If you have a table of patients, therapies, and days, it would appear
you need a many-to-many junction table to represent the sets of
therapies that occur for a given patient on each day. I cannot see
how your current design is intended to work. Perhaps you would need
to give us an example of a few rows of data in each table so we can
see the mechanics you intend.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.

Ok:

Main Form PatientID

Sub Form
PatientID Day PT OT ST
12345 1 15 25 30
12345 2
12345 3 30 30 30
12345 4 20 15
12345 5
12345 6
12345 7

When user filters for PatientID the form opens showing at
least 14 days, they use this to determine if a patient
needs to be seen by 1 or more therapies for a given amount
of time to qualify for Insurance purposes.

The form should also have a running total by therapies.

I hope this helps. And thank you for responding.
My problem is the subform showing 14 days and still being
able to update. Perhaps you can offer another suggestion,
like using one form for viewing, having some button open
another form for entering info????

 

[Tom Ellison]

Dear Dar:

I expect you have shown here some query output that has joined values
from the 3 tables. You did not expose the data in the 3 tables that
generated this data. You have some columns (PT, OT, and ST) that have
no meaning to me. I am having trouble telling what is going on. Is
that a cross tab showing therapies, or what?

So, giving it my best guess, that it is a crosstab, you would have a
good bit of work building this so it is updatable. We do build
updatable crosstab forms, but it is an advanced "expert" level
project. You must build an intermediate temporary table with the
crosstab columns in it, query the existing data into it, then display
that temporary table in the form. There will be an indefinite number
of crosstab columns generated (the total number of columns not
exceeding 255) and you would probably need the from to scroll
horizontally if the number of columns could exceed the displayable
width. This IS DOABLE but, like I said, it's a pretty piece of work.
It is possible to handle an unlimited number of crosstab columns using
even more advanced techniques, where the temporary table is
regenerated as you scroll.

Short of using such very advanced techniques, crosstabs are not going
to update directly back to your source tables. Updating the source
tables from the temporary table I propose is not simple, especially if
this is a multi-user system where changes must be merged with what is
done by other users, and collisions detected and resolved with yet
more software.

I should probably write an article on this, too. Someday!

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Name:
Minutes
Day
Date
PT
OT
ST
Totals
1
0
2
0
3
0
4
0
5
0
6
0
7
0
8
0
9
0
10
0
11
0
12
0
13
0
14
0
This is an Excel spreadsheet. Under PT;OT;ST will be
minutes, like 15, 20, 35. I have more faith in Acces, that
this can be done.

 

[Tom Ellison]

Dear Dar:

I'd really like to help. I don't yet know what is in your 3 tables.
I don't yet know what the query is that combines these 3 tables. I
don't yet know what it is doing and how you would want it to be
different. If we're to proceed, I need this information.

My previous post asked whether the form is based on a crosstab, then
proceeded on that assumption. You didn't respond to this.

If anyone else can see what it is Dar wants, please feel free to jump
in.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

No, it is not based on a crosstab. I'll try and be REAL clear.

tblPatients has 3 fields.
PatientID (primaryKey) LName, FName
tblTherapy has 5 fields
PatientID (foreign key), PT, OT, ST (which are
headings only. They will hold the minutes seen.) and Day
numbered 1-100.

tblDay has 2 fields, Day (PrimaryKey) and PatientID

My whole problem is I'm not sure how the query should be in
order for the subform to show 14 consecutive days for a
patient so the therapist can enter minutes under their
appropriate heading of PT, OT, or ST.

I have been able to visually get the subform to look like
what I want, but it become un-updateable. I use the two
tbls Patient and Therapy joined by PatientID, and then I
just place the tblDay on the query upper screen and put the
Day field on the query grid and it gives me 100 days for
each patientID.

 

[Tom Ellison]

Dear Dar:

My solution would be to add another table.

You need a table of all the days being considered. I expect your
Therapy and Day tables will be "sparse" - that is, not every patient
will have a row for every possible day in the Therapy table. In order
to generate all possible days on the form so you can enter these you
will need a source for all permissible days, and that would be this
table.

I don't see a need for the tblDay. Perhaps you could fill me in.

You could then base the form you're building on only the Therapy
table. However, this table would not necessarily have a row in it for
every patient/day combination. While you could add new rows to this
table for that every day, that's not the preferred design.

It is better to keep the table sparse, having rows only for those
patient/day combinations for which you have data that has been
entered.

This brings up alternatives for the form. The simplest is for the
user to add a new day to the form when it is needed, not listing all
14 consecutive days in advance as you suggested. The second one I
mentioned is to add a new row to the table for every patient every
day. That's not so great.

The third option is to build a temporary table for just the one
selected patient and put all the desired days in that. Then you can
put the 14 rows in this table and the user can enter data as desired.
When the form closes or moves to another patient you would have to
update, delete, and append the Therapy table for those changes. If
you have multiple users, a system to detect and resolve conflicts
created by this would probably be important.

Doing this by any but the simplest method (which is not what you asked
for) will probably turn into a larger project and require (or result
in the development of) a fairly expert level of development skills.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts