Too MANY entities? Can't create a report that works.

[TC]

Marcia, I'm not able to go through all your details, but I can give you a
tip from long experience.

The only way to approach such problems, is to focus on the question, "what
is the primary key of each table?". You need to list each table, & say what
is the primary key of each one. Once you get the primary keys right,
everything else will "come out in the wash". You will be able to generate
whatever reports make logical sense. Focusing on how to produce a certain
report, at this stage, is going about it the wrong way around, in my
opinion.

Here is an example of how to list the primary key of each table, using
random tables as an example.

tblPerson
PersonID (PK)
name, DOB, sex etc.

tblPersonHoliday
PersonID ( composite )
StartDate ( PK )
authorized-by, etc.

Apolgies if you >have< described the primary keys in your links. I don't
have time to read such links.

HTH,
TC

 

[Marcia]

I am at a total loss as to how to do a certain report that is required
for work, and would appreciate some help.

I have read in many posts that one must design their database with
separate tables for each entity, but I think that I have created TOO
MANY entities because I am now unable to do my report.

The database starts with a "Homes" table that houses many "Residents."
The Homes are also associated with various "Worksites," and the
residents are assigned to a primary Worksite. This is complicated by
the fact that a Resident doesn't necessarily have to be assigned to
the same Worksite that is associated with his/her Home.

The Worksites are scheduled for five weekly sessions (Monday through
Friday). The Worksite schedule is complicated by the fact that,
although a Resident is assigned to one PRIMARY Worksite, the schedule
could be mixed to include a session at OTHER Worksites – again, not
necessarily those sponsored by his Home. I must produce a report,
grouped by Home, then by that Home's Worksites. Underneath each
worksite is a listing of all the residents and their daily sessions
laid out across the page. Please note that ALL residents that attend
the worksite must be listed, even if it is not their primary worksite
and they only attend one session. Management wants the sessions to
be displayed ACROSS the page, not DOWN the page.

I have posted a picture of my Relationships and a sample of the report
that I need to produce here:

Relationships (labeled "First Attempt):
http://postedinfo.homestead.com/index.html

Sample Report:
http://postedinfo.homestead.com/SampleReport.html

For clarification purposes, I have formatted the names of the
residents in blue if it is their primary worksite, and have formatted
them in red if they are only attending one session.

I have tried basing this report on various queries (including a
crosstab query), but was unable to find the right combination that
worked. I got stuck every single time.

Since I was having so much trouble, I decided to try another database
design… I eliminated some tables by putting the five worksite session
fields (Monday – Friday) in the tblResidents table. Although I could
still assign the resident to a primary worksite (in
tblWorksitesResident), it was very simple to assign the individual's
daily sessions through drop-down boxes linked to tblWorksites. I
also posted a picture of this design in the above link (labeled Second
Attempt).

By using this second design, I got much further in creating my report
and ALMOST finished it. BUT, it only lists the Residents who are
primarily assigned to the worksite; I can't figure out how to make it
include the Residents who attend the worksite for only one session.

So, now I'm back to the beginning and getting very discouraged.

Does anyone know how to make either one of these designs produce the
report I need?

Thanks so much for any assistance offered.

Jessi

 

[Marcia]

I'm sorry. I thought it would be easier to understand my situation
if I just posted a picture of the relationship design, and I also
thought the primary keys would appear in bold.

Can you tell which fields are the primary keys at this address?
http://postedinfo.homestead.com/index.html

If not, please post back, and I'll define all the tables and primary
keys.

ALSO...
I forgot to post the error message I get when I try to create the
report using a crosstab query (with the second design):
"You can't use a pass-through query or a non-fixed column crosstab
query as a record source for a subform or subreport."
I have no idea what that means, or how to fix it.

Thanks!
Jessi

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

PMFJI

I'm sorry. I thought it would be easier to understand my situation
if I just posted a picture of the relationship design, and I also
thought the primary keys would appear in bold.

Yes, they do.

Can you tell which fields are the primary keys at this address?
http://postedinfo.homestead.com/index.html

This helps: and thank you for not posting the picture in your message!

One of the problems that leaps up straightaway is the method of naming the
relationships (the Access team generally has a problem with this: it's not
your fault!). Names like 'tblWorksitesResidents' do not help anyone
understand what the table is modelling: from your original script,

The Worksite schedule is complicated by the fact that,
although a Resident is assigned to one PRIMARY Worksite

it seems that this table should be called Assignments; and there should be
a field to designate the Primary one. There is also a relationship via the
Enrollment table -- can residents be assigned to worksites other than ones
they have enrolled sessions at? If not, then the first table is redundant.
Perhaps naming the relationships and tables would make this a bit clearer.

In the second schema, again the presence of attributes like Monday,
Tuesday, Wednesday, and so on screams of the need to normalise.

I looked at the sample report too, without understanding very much more.
What do A, B, C and so on stand for? And is the colour significant?


I have to say right here that I don't understand very much about your
project, but I don't think you have the entities right yet. I get the
impression of: (as usual, * means PK, + means FK)

Residents(*ID, Name, HomedAt+)

Homes(*ID, Colour)

Worksites(*ID, Name, HomeNumber+, Instructor, Teacher)
// what does it mean for a Worksite to be at a Home? Is this different
// from the home of the resident attending sessions there? Which Home
// do you want to see in the report?
// I also notice you have dropped the Instructor and Teacher attributes
// in the second schema? Are they not needed at all?

DaysOfWeek(*DayName)

IsAllocatedTo(*Resident+, *Worksite+, *Home+, *WeekDay+, MagicLetter)
// FWIW, this is a four-table version of Fifth normal form (I think...)
// and relies on the fact that there could be potentially any
// combination of Resident, Worksite, Home and Weekday.

Assignments(*Resident, Home+, IsPrimary)
// Assuming that these assignments are independent of the session
// allocations. Unfortunately there is no easy way of forcing exactly
// one IsPrimary assignment for each Resident: if this is vital then
// there are other models that could do it.

I very much doubt this is right, but it might point some way through for
you.

Best wishes


Tim F

 

[TC]

I'm sorry. I thought it would be easier to understand my situation
if I just posted a picture of the relationship design, and I also
thought the primary keys would appear in bold.

No probs It's just that questions are more likely to be answered if
they are self-contained, & do not involve following links.

Can you tell which fields are the primary keys at this address?
http://postedinfo.homestead.com/index.html

Tim F. is on the right track. Notice how he is not >primarily< concerned
with the report. He is much more concerned to understand the nature of the
actual data, & the way the things relate to each other "in real life". All
those issues must be sorted, before you can be confident of the primary
keys. And the primary keys must all be sorted, before you can confidently
write the report.

HTH,
TC

 

[Marcia]

Thanks for your help. I have tried to answer your questions as
follows. I also renamed some of the tables and my "Home" names to make
them less confusing:


Okay, I renamed it to "tblPrimaryWorksite." I needed a way to assign
the resident to one primary worksite in order to generate other
reports. But... even though the resident technically belongs to one
worksite, he may also be placed in ANY number of other worksites on
any given day.

at?

Yes. So in my way of thinking, it is almost like the resident's
Primary Worksite assignment is a completely different entity from
his/her actual Daily Worksite assignment. I have now renamed the two
tables involved in this second relationship to:

1. "tblWorksiteSessions" (which contains all the possible
combinations of worksite sessions that a resident can be assigned to.
For example, Worksite "A"'s Monday session, Worksite "A"'s Tuesday
session, Worksite "A"'s Wednesday session, and so forth with Worksite
B, C, etc.) The primary key (fldSessionID) is an autonumber, along
with fields for the Worksite ID, and the session time (Mon., Tues.,
etc.)

2. "tblDailyWorksite" (which links the SessionID from the above
table to the Resident ID. I wanted to assign this particular resident
to this particular worksite session).

I have probably made this much harder than it has to be. I would love
to know a better way to accomplish this. Should I delete the first
table (tblPrimaryWorksite) and replace it somewhere with a dropdown
combo box that is linked to tblWorksites?


I agree, but I couldn't figure out how to create my report in the
format that management wanted. (Actually, this second design came
very close to succeeding; I was able to create the report EXCEPT it
omitted those residents who only attended one or two sessions of the
Worksite.)


I'm sorry (grin). A, B, C, D stands for the worksite's name! I
should have pointed that out.

I also used the different colors in an attempt to point out the
difference in assignments. The blue residents are assigned to that
worksite as their PRIMARY worksite. The red residents only appear
under the worksite's name because they have one or more sessions in
that worksite... their primary worksite is somewhere else. (I didn't
mean to confuse you with the color... the formatting has nothing to do
with the name of the Home, so I have now changed the Home names to
avoid further confusion.)

For example, Mr. Arrow is listed in blue under worksite "A" because
this is his primary worksite, but he also appears in red under
worksite "C" because he attends "C" on Thursday.

Phew... I know this sounds really confusing. Can you see why I'm so
frustrated? And because I've taken one course in Access, my boss is
halfway expecting me to put all this together in a database. Right
now, we're keeping up with all this manually.

Let me try to answer your remaining questions:
The residents live in "Homes" – there are five different Homes (I have
only listed two in my sample report, though: Trout Home, Bass Home,
etc. At times I will need to run reports that link the residents
back to their respective home, so I linked tblHomes to tblResidents.

Each Home sponsors 3 or more Worksites. At times I will need to run
reports sorted first by Home, then by their Worksites, so I also
linked tblHomes to tblWorksites.

The residents are all assigned to one Primary Worksite. But... even
though Mr. Arrow's primary worksite is "Worksite A" (which is
sponsored by the Trout Home), he may actually live in the Bass Home.
I will need to run resident reports for both – by Home and/or by
Worksite, depending on the situation.

IDEALLY, WHAT I HOPED FOR WAS:
To be able to open a form/subform(s) based on the Resident's name,
then choose his Primary Worksite through a drop-down box, and then
also assign his Daily Worksite sessions by clicking on drop-down
boxes.

After all the data is entered, I need to be able to run a Schedule
report that is sorted:
1. First by Home, then
2. By Worksite (for that Home), then
3. List all Residents who attend that Worksite (even though the
Worksite may not be sponsored by the Home they actually live in, and
even though it may not be their Primary Worksite. Indeed, most
residents will be listed several times... once for their Primary
Worksite, and again under each additional worksite they may be
assigned to).

I'm not sure that I understand your "DaysOfWeek" table. Are you
suggesting that I combine the tblWorksiteSessions with
tblDailyWorksite?

I hope my comments have made more sense. If not, I'd be happy to
clarify further.

Thanks,
Jessi

 

[Marcia]

I guess I should re-post the links since I have now changed some names in my tables.

Relationship:
http://postedinfo.homestead.com/index.html

Desired Report:
http://postedinfo.homestead.com/SampleReport.html

Thanks!
Jessi

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

I am going to take some of the things you said out of order: let me know if
I've made a complete Horlicks of it!

The residents live in "Homes" – there are five different Homes (I have
only listed two in my sample report, though: Trout Home, Bass Home,
etc.

Okay: this is easy. You need a field in Residents called LivesAt and it's a
FK into the Homes table. This is a simple many-one relationship (unless
you're about to say that residents can live in more than one home at a
time).

Each Home sponsors 3 or more Worksites.

I assumed at first that this was a many-many relationship, but it looks
from your diagram as though a worksite is only sponsored by one home, so
it's just a many-one with a SponsoredBy field in Worksites.

The residents are all assigned to one Primary Worksite. But... even
though Mr. Arrow's primary worksite is "Worksite A" (which is
sponsored by the Trout Home),

Okay: another simple many-to-one relationship, with a field in the
Residents table called PrimaryWorkSite.

he may also be placed in ANY number of other worksites on
any given day.

Yes. So in my way of thinking, it is almost like the resident's
Primary Worksite assignment is a completely different entity from
his/her actual Daily Worksite assignment.

Eeek: so we actually do have another entity for DailyAssignments, or is
this part of the Sessions stuff? I don't think I am clear yet on these
allocations.

1. "tblWorksiteSessions" (which contains all the possible
combinations of worksite sessions that a resident can be assigned to.
For example, Worksite "A"'s Monday session, Worksite "A"'s Tuesday
session, Worksite "A"'s Wednesday session, and so forth with Worksite
B, C, etc.) The primary key (fldSessionID) is an autonumber, along
with fields for the Worksite ID, and the session time (Mon., Tues.,
etc.)

2. "tblDailyWorksite" (which links the SessionID from the above
table to the Resident ID. I wanted to assign this particular resident
to this particular worksite session).

Yes: I think this is what I was getting at above, and it seems that you are
saying that the daily assignments are the same as the sessions.

I'm not sure that I understand your "DaysOfWeek" table. Are you
suggesting that I combine the tblWorksiteSessions with
tblDailyWorksite?

No: it's a table with seven rows in it (five?) to control the creation of
records in WorksiteSessions.

I have probably made this much harder than it has to be. I would love
to know a better way to accomplish this. Should I delete the first
table (tblPrimaryWorksite) and replace it somewhere with a dropdown
combo box that is linked to tblWorksites?


I agree, but I couldn't figure out how to create my report in the
format that management wanted.

Worry about UI stuff after you have the logical design right -- this is
probably going to an easy pivot table.

I also used the different colors in an attempt to point out the
difference in assignments. The blue residents are assigned to that
worksite as their PRIMARY worksite.

Again, this can probably be implemented with some conditional formatting or
a DLookup() somewhere. Later, later.

Phew... I know this sounds really confusing. Can you see why I'm so
frustrated? And because I've taken one course in Access, my boss is
halfway expecting me to put all this together in a database. Right
now, we're keeping up with all this manually.

Well, you know where you are with a piece of paper! Actually this is far
from a simple project after "one" Access course...

At times I will need to run reports that link the residents
back to their respective home, so I linked tblHomes to tblResidents.

At times I will need to run
reports sorted first by Home, then by their Worksites, so I also
linked tblHomes to tblWorksites.

Yeah: it's all about semantics and you have to be really picky about the
language you use. The difference between Daily Assignments, Primary
Assignments and Worksite Sessions is crucial... It looks as though we are
getting closer though!

This is roughly what I see at the moment:


SESSIONSPACES >-- slotted in to -- SESSIONS >-- located at
V |
| |
assigned to |
| |
RESIDENTS >--- live in --- HOMES -- sponsor of --< WORKSITES
V |
| |
+----------- primarily assigned to --------------+






B Wishes


Tim F

 

[Marcia]

Thanks for your help! I will now compare what I have with your
comments to see where I need to adjust things:

HOMES:

In reference to your "LivesAt" field in tblResidents...

One Home contains many Residents, so I have linked the fldHomeID (PK
in tblHomes) to the fldHomeID in tblResidents as a foreign key. Is
this what you had in mind? If not, let me know.

it looks from your diagram as though a worksite is only sponsored by one
home, so it's just a many-one with a SponsoredBy field in Worksites.

I think that I have this. One Home sponsors many Worksites, so I
linked the PK fldHomeID in tblHomes to a FK fldHomeID in tblWorksites.
Is this correct?

PRIMARY WORKSITE:

Okay: another simple many-to-one relationship, with a field in the Residents
table called PrimaryWorkSite.

If I am following you correctly, I should eliminate
tblPrimaryWorksite, which is the joining table between tblWorksites
and tblResidents. Instead, I should link them directly by creating a
FK field in tblResidents called "fldPrimaryWorksite," which would be
linked to the primary key field of fldWorksiteID in tblWorksites.
Right???


DAILY WORKSITE SESSIONS:

Eeek: so we actually do have another entity for DailyAssignments, or is this
part of the Sessions stuff? I don't think I am clear yet on these
allocations. It seems that you are saying that the daily assignments are the
same as the sessions.

Right. I believe the Resident's "daily" worksite sessions would be a
separate entity because the worksite can vary from day to day;
whereas the "primary" worksite remains constant (until they decide to
change it – ha!).

I think that I have this right, but let me know if I am not
understanding you. From your diagram it looks like:
My tblDailySessions is your SessionSpaces; and
My tblWorksiteSessions is your Sessions
Right?

DAYS OF WEEK TABLE:

it's a table with seven rows in it (five?) to control the creation of records
in WorksiteSessions.

I still don't understand this. Do I need to modify my
tblWorksiteSessions?

Worry about UI stuff after you have the logical design right -- this is
probably going to an easy pivot table.

I hope so... I have invested too much time in this not to be able to
get it to work.

We can forget about this... The red/blue color formatting doesn't need
to be on the actual report. I was just trying to help people
understand my problem with making the residents (in red) who are not
primarily assigned to the worksite still appear under the worksite if
they have only one session. (You see, I was able to create my report
using the second schema EXCEPT for that problem).

Actually this is far from a simple project after "one" Access course...

That's nice to know. I was beginning to think I hadn't learned much!

This is roughly what I see at the moment:

SESSIONSPACES >-- slotted in to -- SESSIONS >-- located at
V |
| |
assigned to |
| |
RESIDENTS >--- live in --- HOMES -- sponsor of --< WORKSITES
V |
| |
+----------- primarily assigned to --------------+


Okay... After I eliminate my tblPrimaryWorksite table (and link
tblResidents directly with tblWorksites), it appears that my diagram
will then resemble yours. Is that the way you see it? Or do I need
to make other adjustments concerning your "DaysOfWeek" table?

http://postedinfo.homestead.com/index.html

Thanks for being so nice and helpful!

Jessi

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

One Home contains many Residents, so I have linked the fldHomeID (PK
in tblHomes) to the fldHomeID in tblResidents as a foreign key. Is
this what you had in mind? If not, let me know.

Yup, spot on.

One Home sponsors many Worksites, so I
linked the PK fldHomeID in tblHomes to a FK fldHomeID in tblWorksites.
Is this correct?

Yes again.

If I am following you correctly, I should eliminate
tblPrimaryWorksite, which is the joining table between tblWorksites
and tblResidents. Instead, I should link them directly by creating a
FK field in tblResidents called "fldPrimaryWorksite," which would be
linked to the primary key field of fldWorksiteID in tblWorksites.

That's right. If each resident has one Primary Worksite, then you don't
need another table: it's a simple one-to-many relationship.

Right. I believe the Resident's "daily" worksite sessions would be a
separate entity because the worksite can vary from day to day;
whereas the "primary" worksite remains constant (until they decide to
change it – ha!).

That's clear...

I think that I have this right, but let me know if I am not
understanding you. From your diagram it looks like:
My tblDailySessions is your SessionSpaces; and
My tblWorksiteSessions is your Sessions

The joy of db design: the language that makes sense to me is never the same
as what makes sense to you..!

I still don't understand this. Do I need to modify my
tblWorksiteSessions?

Hmm: first of all, I don't think I'd lose too much sleep about this bit. I
was thinking of the WorksiteSessions looking like

Worksite DayOfWeek (you have SessionTime rather than DayOfWeek)
======== =========
A Monday
A Tuesday
A Wednesday
A Thursday
A Friday
B Monday
B Friday
C Monday
C Tuesday
C Wednesday
etc

and make the Worksite a FK referencing Worksites, and DayOfWeek a FK
referencing a new table WeekDays. In this way, when someone comes along and
announcing that Wednesdays are off, but there will be worksite sessions on
Saturday instead, you can manipulate the WeekDays table instead of diving
into loads of ValidationRule entries. Horses and courses: for my money it's
a simplifying feature, while other people find it more fussy.

Okay... After I eliminate my tblPrimaryWorksite table (and link
tblResidents directly with tblWorksites), it appears that my diagram
will then resemble yours. Is that the way you see it? Or do I need
to make other adjustments concerning your "DaysOfWeek" table?

I think that is about right.

One thing that struck me after: do the daily sessions change from week to
week, and do you need to keep track of which week is which? There is no
date information here anywhere, and I am wondering if I have missed a
layer?

B Wishes


Tim F

 

[Marcia]

I had two night classes this evening and didn't get home until late,
so I won't be able to make the table/link adjustment until tomorrow
night or Saturday morning.

Please bear with me... I'll post back sometime this weekend.

Thanks for your help!

Jessi

 

[Marcia]

Worksite DayOfWeek (you have SessionTime rather than DayOfWeek)

======== =========
A Monday
A Tuesday
etc

Okay... I think you are talking about my tblWorksiteSessions where I
have combined the fldWorksiteID with the fldSessionTime (with a PK
autonumber called fldSessionID). EXCEPT I don't have a separate
"DaysOfWeek" table. Instead, when I defined the fldSessionTime field,
I made it a lookup ComboBox based on a value list. [I guess now is
the time to explain something else... I tried to keep things as simple
as possible when I first posted my message, so I referred to the
sessions as Monday, Tuesday, etc. ACTUALLY, there are TWO sessions
each day (one in the morning, and one in the afternoon), so I referred
to the sessions as MAM (Monday morning) and MPM (Morning afternoon),
etc. and put this as the value list in the lookup property of my
fldSessionTime field.

I didn't think about arranging for the fldSessionTime to lookup the
appropriate session day/time in a separate table. Are there any
advantages to using lookup with a table versus a value list? If so,
please let me know, and I'll go back and do this.

One thing that struck me after: do the daily sessions change from week to
week, and do you need to keep track of which week is which? There is no
date information here anywhere, and I am wondering if I have missed a
layer?

No... The sessions currently do not change from week-to-week.


Okay... I think I have made all the changes, and have re-posted the
picture of the relationship. Do you see any additional revisions I
need to make?

Thanks!
Jessi

 

[TC]

Hi Marcia (Jessie?)

Notice how much work is required before you can start on your forms &
reports!

Do you now see what I meant, in my first answer to you?

Cheers,
TC

Worksite DayOfWeek (you have SessionTime rather than DayOfWeek)
======== =========
A Monday
A Tuesday
etc

Okay... I think you are talking about my tblWorksiteSessions where I
have combined the fldWorksiteID with the fldSessionTime (with a PK
autonumber called fldSessionID). EXCEPT I don't have a separate
"DaysOfWeek" table. Instead, when I defined the fldSessionTime field,
I made it a lookup ComboBox based on a value list. [I guess now is
the time to explain something else... I tried to keep things as simple
as possible when I first posted my message, so I referred to the
sessions as Monday, Tuesday, etc. ACTUALLY, there are TWO sessions
each day (one in the morning, and one in the afternoon), so I referred
to the sessions as MAM (Monday morning) and MPM (Morning afternoon),
etc. and put this as the value list in the lookup property of my
fldSessionTime field.

I didn't think about arranging for the fldSessionTime to lookup the
appropriate session day/time in a separate table. Are there any
advantages to using lookup with a table versus a value list? If so,
please let me know, and I'll go back and do this.

One thing that struck me after: do the daily sessions change from week to
week, and do you need to keep track of which week is which? There is no
date information here anywhere, and I am wondering if I have missed a
layer?

No... The sessions currently do not change from week-to-week.


Okay... I think I have made all the changes, and have re-posted the
picture of the relationship. Do you see any additional revisions I
need to make?

Thanks!
Jessi

 

[TC]

Once you get the primary keys right, you can develop any report that makes
logical sense.

Not to flog a possibly dead horse, but: do you now understand that? In other
words, in future projects, will you now forget the forms & reports, until
you have designed the table structures & got the primary keys right?

Cheers,
TC
(off for the day)

 

[Marcia]

Hi, TC:

My name is "Jessi," but I answer to both.

Now that I have belabored this project, I just **know** that you guys
are going to be able to help me get my report!

Marcia

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

[I guess now is
the time to explain something else... I tried to keep things as simple
as possible when I first posted my message,

That's fine: I'm all for simplifying assumptions <g>

so I referred to the

sessions as Monday, Tuesday, etc. ACTUALLY, there are TWO sessions
each day (one in the morning, and one in the afternoon), so I referred
to the sessions as MAM (Monday morning) and MPM (Morning afternoon),
....

Are there any
advantages to using lookup with a table versus a value list? If so,
please let me know, and I'll go back and do this.

As you have introduced the new information about the sessions, it seems to
make even more sense to me to use a table to control this data. In general
terms, it is unlikely that someone is going to introduce a new day into the
week (Blairsday, Bushday, anyone?), so it can be reasonable to assume them.
It's very rare, for example, to have a look up table for Male and Female.

As soon as you start moving away from these givens, however, you need a
method of controlling what goes in. "Look up tables" are about control, not
about convenience! To put it another way, the whole point of modern
database design is to prevent garbage getting in in the first place. So a
table that simply lists, "Monday AM", "Monday PM", etc may not seem to be
achieving very much, until you mis-spell an query and enter a "Thursday QM"
by mistake. Important integrity rules should _always_ be implemented at the
DB engine level (i.e. referential integrity, validation rules, unique
indexes, and so on): the job of the user interface is to hide them.

One day, someone _will_ want to introduce a Saturday AM session, or cancel
the Wednesday afternoon. You don't want to start digging round value lists
in forms to implement that: you use the database itself to manage the
database rules.

Sorry: that was a bit blurby, but it's important. The big problem with the
old file manager systems like dBase and Paradox was that they depended on
the user interface to preserve their integrity and provided no protection
against modification via another UI. The RDBMS model moves that protection
back a step, so that any data access has to go through the rules, whether
it's via Access or Excel or SQL or MSQuery or whatever. It's a powerful
tool and deserves to be used properly.

All the best


Tim F

 

[Marcia]

As you have introduced the new information about the sessions, it seems to

make even more sense to me to use a table to control this data.

Your explanation makes a lot of sense, so I created a new lookup table
(tblSessionLookup) to store the sessions. It has only one field
(fldSessionLookup) where I have typed the various sessions (MAM, MPM,
TAM, etc.) I also changed the fldSessionTime in tblWorksiteSessions
to lookup the values in the new table.

I did NOT "link" fldSessionTime to fldSessionLookup (see posted
picture). Should I have?

http://postedinfo.homestead.com/index.html


If everything about my designs now appears to be in order, do you have
any suggestions about how to begin my report?

I. The query/report needs to be sorted first by:
Home (fldHomeName from tblHomes)

A. Then by Worksite (fldWorksiteName from tblWorksites)

1. Then list each Resident that has ANY sessions in that
Worksite,
along with their sessions, as shown in the sample report.
fldResidentName (from tblResidents)....

(and this is where I always get stuck).

Thanks,
Jessi

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

I did NOT "link" fldSessionTime to fldSessionLookup (see posted
picture). Should I have?

Yes: make tblSessionLookup.fldSessionLookup the PK for the table, and make
tblWorksiteSessions.fldSessionTime a FK that points at it. You'll need to
check first for any fldSessionTime values that are invalid or else Access
will not be able to create the relationship.

If everything about my designs now appears to be in order, do you have
any suggestions about how to begin my report?

Ugh: I hate all these complex queries! We should be able to get somewhere
though.

Start with a new query grid, and add the Homes table, and then the
Worksites. Check the join so that it's the Homes.HomeID to
Worksites.SponsoredBy (now you see why good naming is so critical); and
make it "Include all Homes records and ..." Add the Homes.Name field and
sort on it, then the Worksites.WorksiteName and sort on it too. Check this
in datasheet view to make sure it looks right for the outer two grouping
levels.

We are going to need the "set of all residents who have at least one
DailySession at the particular Worksite". I think you'll need to hand code
the VBA for this, and it's a bit hard to do this virtually without setting
up the database to play with. If I get a chance to do this over the week,
I'll have a go (but I have a new play to learn too...). In the meantime you
can practise along the lines of (in a new query):-

SELECT Worksite.WSName, Residents.RName
FROM Residents, Worksites
WHERE Residents.ResidentID IN
( SELECT DailySessions.ResidentID
FROM Worksites
LEFT OUTER JOIN (WorksiteSessions
LEFT OUTER JOIN (DailySessions
ON DailySessions.SessionSlot = SessionSlots.SlotID)
ON WorksiteSessions.Location = Worksites.WorksiteID)
)
ORDER BY 1,2;

This is untested so it's bound to need amending. It should produce a list
of all the worksites and each resident that has any sessions in that
worksite. We'll probably put together a piece of VBA to produce the ten
DailySession.WorksiteID values, in a tab-delimited list so as to display
them horizontally.


Hope this helps


Tim F

 

[Marcia]

If I get a chance to do this over the week, I'll have a go (but I
have a new play to learn too...).

I consider your help a HUGE favor, so *please*... take all the time
you need!

Yes: make tblSessionLookup.fldSessionLookup the PK for the table, and make
tblWorksiteSessions.fldSessionTime a FK that points at it.

I did this. I tried to make it a one-to-many relationship by
dragging fldSessionTime to fldSessionLookup, but it insisted on doing
it the other way. I hope that's okay.

http://postedinfo.homestead.com/index.html


FIRST QUERY:

Start with a new query grid, and add the Homes table, and then the
Worksites.

I did this. I checked the relationship and chose the second option -
"Include all records from tblHomes, and only those records from
tblWorksites where the join fields are equal." The query worked
fine.


SECOND QUERY:
I'm impressed! You're obviously way over my head. We didn't learn
THIS in my class <g>.

I did play with it, though. I opened a new query grid, closed the
"Show Tables" box without adding any tables, and clicked on the "View
SQL" mode (because I assume that's what this is). Here is what I
added:

SELECT tblWorksites.fldWorksiteName, tblResidents.fldResidentName
FROM tblResidents, tblWorksites
WHERE tblResidents.fldResidentID IN
( SELECT tblDailySessions.fldResidentID
FROM tblWorksites
LEFT OUTER JOIN (tblWorksiteSessions
LEFT OUTER JOIN (tblDailySessions
ON tblDailySessions.fldDailySessionID =
tblWorksiteSessions.fldSessionID)
ON tblWorksiteSessions.fldWorksiteID =
tblWorksites.fldWorksiteID)
)
ORDER BY 1,2;

It didn't work, though. Said it had a "Syntax" Error, and I didn't
know how to fix it. Do you see it?

PLEASE... take your time, though. I definitely understand time
constraints - I work fulltime and go to school parttime. I just
appreciate your help!

Thanks!
Jessi

 

[Tim Ferguson]

(e-mail address removed) (Marcia) wrote in

Hi Jessi

I did this. I tried to make it a one-to-many relationship by
dragging fldSessionTime to fldSessionLookup, but it insisted on doing
it the other way. I hope that's okay.

Looks fine. You might consider making (WorsiteID, SessionTime) a unique
key, but don't worry too much.

tblWorksites where the join fields are equal." The query worked
fine.
Cool!

I'm impressed! You're obviously way over my head. We didn't learn
THIS in my class <g>.

Not too impressed: it didn't work!

SELECT tblWorksites.fldWorksiteName, tblResidents.fldResidentName

etc etc

Okay: perhaps we can shoot this from the middle outwards. See if the bit in
the brackets works first:

SELECT tblWorksites.fldWorskiteID,
tblDailySessions.fldResidentID
FROM tblWorksites
LEFT OUTER JOIN (tblWorksiteSessions
LEFT OUTER JOIN (tblDailySessions
ON tblDailySessions.fldDailySessionID
= tblWorksiteSessions.fldSessionID)
ON tblWorksiteSessions.fldWorksiteID = tblWorksites.fldWorksiteID)

The idea is to get a list of all the residents daily-assigned to each
WorksiteID. If it doesn't work, then you should be able to build this bit
in the query grid by adding each table in turn: Worksites ->
WorksiteSessions -> DailySessions.

As an aside, if you had decided to use (Worksite, SessionTime) as the PK
for the WorksiteSessions table, and (Worksite, SessionTime, ResidentID) as
the PK for the DailySessions table, then this would not have required any
joins at all! I mention this only because of the thread that has just
started above about natural vs artificial keys... <g> and it is a good
example of how simplifying in one area makes things worse in another.

All the best


Tim F