Seeking suggestion for handling parent/child situation on continous forms

[Larry Kahm]

I'm working on a database where there is a parent record that can contain
one to "n" child records. In turn, each child record can have several
activities associated with it.

My client as asked that if they create an activity of "Done," would I be
able to reflect that in the list of child records?

If I add a Yes/No field to the child record table and in the Activty combo
box's AfterUpdate event, I set the field to True if the Activity is "Done"
or False if it isn't. The query that displays the list of child records
would be updated with the Yes/No field.

However, they also want to know if the parent list of records - shown on a
continous form - can reflect whether all of the child records are flagged
as "done"?

That I'm not sure I can do, so I'm looking for suggestions as to how to
handle this.

Thanks!

Larry

 

[Jeff Boyce]

Larry

So you're saying that each child record has only one Activity ("an activity
of 'Done'") or "each child record can have several activities associated
with it"? These seem to be contradictory requirements.

It seems like you are focused more on the mechanics than the underlying
data.

It would help to know what you're starting with, and what you want to end up
with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Kahm]

Jeff,

To clarify: Each child record can (and often does) have multiple activities.
When the client team believes they have satisfied all of the requirements,
for the child record they want to label that record as complete; thus the
activity with a value of "done."

Here's a general picture:

The parent record contains information that describes a project.

The child record contains information that describes a compay who is
potential partner/bidder on the project.

The activities involve various interactions with the company (send
information, prepare photos, follow-up e-mail, etc.)

The request from the client team is to be able to see - at a glance - what
has been finished and what is still left pending.

I smacked my head when one of the team members said, "Oh, when we used to
use Excel we would highlight the ones that were done in blue. Can we do
that with Access?" My (very) bad for not asking why the rows had different
colors. [Hangs head in shame for thinking they were merely alternating color
bars...]

Larry

 

[Jeff Boyce]

Larry

Based on your description, it sounds like you have the following entities
:
[Projects]
[Companies]
[Requirements]
[CompanyRequirementOnProject]

and it sounds like you are "calculating" that all of a company's
requirements have been satisfied in order to declare them "done".

I suspect you are trying to "commit spreadsheet on Access", treating it like
Excel rather than as a relational database.

Please list your tables and relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

To clarify: Each child record can (and often does) have multiple
activities. When the client team believes they have satisfied all of the
requirements, for the child record they want to label that record as
complete; thus the activity with a value of "done."

Here's a general picture:

The parent record contains information that describes a project.

The child record contains information that describes a compay who is
potential partner/bidder on the project.

The activities involve various interactions with the company (send
information, prepare photos, follow-up e-mail, etc.)

The request from the client team is to be able to see - at a glance - what
has been finished and what is still left pending.

I smacked my head when one of the team members said, "Oh, when we used to
use Excel we would highlight the ones that were done in blue. Can we do
that with Access?" My (very) bad for not asking why the rows had
different colors. [Hangs head in shame for thinking they were merely
alternating color bars...]

Larry

Larry

So you're saying that each child record has only one Activity ("an
activity of 'Done'") or "each child record can have several activities
associated with it"? These seem to be contradictory requirements.

It seems like you are focused more on the mechanics than the underlying
data.

It would help to know what you're starting with, and what you want to end
up with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Larry Kahm]

Jeff,

Thanks for taking the time to review this!

Parent table

tblProjectData with ProjectDataID as key



Child table

tblCompanyDetails with CompanyDetailsID as key

tblProjectData.ProjectDataID = tblCompanyDetails.ProjectDataID (one to
many)



Activities table

tblCompanyActivities with CompanyActivitiesID as key

tblCompanyDetails.CompanyDetailsID =
tblCompanyActivities.CompanyDetailsID (one to many)



Tasks table

tblCompanyActivityTasks with CompanyActivityTasksID as key

tblCompanyActivityTasks.CompanyActivityTasksID =
tblCompanyActivities.ActivityID (one to many)



I'm open to any, and all, suggestions as to handle flagging something as
"done" without comitting Excelicide...

Larry

Larry

Based on your description, it sounds like you have the following entities
:
[Projects]
[Companies]
[Requirements]
[CompanyRequirementOnProject]

and it sounds like you are "calculating" that all of a company's
requirements have been satisfied in order to declare them "done".

I suspect you are trying to "commit spreadsheet on Access", treating it
like Excel rather than as a relational database.

Please list your tables and relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

To clarify: Each child record can (and often does) have multiple
activities. When the client team believes they have satisfied all of the
requirements, for the child record they want to label that record as
complete; thus the activity with a value of "done."

Here's a general picture:

The parent record contains information that describes a project.

The child record contains information that describes a compay who is
potential partner/bidder on the project.

The activities involve various interactions with the company (send
information, prepare photos, follow-up e-mail, etc.)

The request from the client team is to be able to see - at a glance -
what has been finished and what is still left pending.

I smacked my head when one of the team members said, "Oh, when we used to
use Excel we would highlight the ones that were done in blue. Can we do
that with Access?" My (very) bad for not asking why the rows had
different colors. [Hangs head in shame for thinking they were merely
alternating color bars...]

Larry

Larry

So you're saying that each child record has only one Activity ("an
activity of 'Done'") or "each child record can have several activities
associated with it"? These seem to be contradictory requirements.

It seems like you are focused more on the mechanics than the underlying
data.

It would help to know what you're starting with, and what you want to
end up with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm working on a database where there is a parent record that can
contain one to "n" child records. In turn, each child record can have
several activities associated with it.

My client as asked that if they create an activity of "Done," would I
be able to reflect that in the list of child records?

If I add a Yes/No field to the child record table and in the Activty
combo box's AfterUpdate event, I set the field to True if the Activity
is "Done" or False if it isn't. The query that displays the list of
child records would be updated with the Yes/No field.

However, they also want to know if the parent list of records - shown
on a continous form - can reflect whether all of the child records are
flagged as "done"?

That I'm not sure I can do, so I'm looking for suggestions as to how to
handle this.

Thanks!

Larry

 

[Jeff Boyce]

Larry

I'm not sure I follow how a Company is a "child" to a Project -- in my
world, projects exist whether or not I've assigned a company to work on
them. This suggests to me that they are separate entities, and I need a
third table to resolve which company is assigned to which project.

Again, in my world, the same company could be assigned to more than one
project, and one project could have, over time, more than one company
assigned. This is a many-to-many relationship -- hence the need for the
third table to handle assignments.

Once I have that third table (CompanyAssignedToProject), I use the primary
key of THAT table to show which Activities are associated with that
assignment. Once again, because the same Activity (e.g., Write an
Environmental Impact Statement) could show up for more than one company,
more than one project, I'd use an Activity table and a
"join/junction/resolver" table between [CompanyAssignedToProject] and
[Activity].

Finally, I would use a query (or a series of queries) to determine that
every activity related to an assigned company-on-project had been completed
.... and THAT represents "done", not some other activity. If all are not
completed, not done!

Best of luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for taking the time to review this!

Parent table

tblProjectData with ProjectDataID as key



Child table

tblCompanyDetails with CompanyDetailsID as key

tblProjectData.ProjectDataID = tblCompanyDetails.ProjectDataID (one to
many)



Activities table

tblCompanyActivities with CompanyActivitiesID as key

tblCompanyDetails.CompanyDetailsID =
tblCompanyActivities.CompanyDetailsID (one to many)



Tasks table

tblCompanyActivityTasks with CompanyActivityTasksID as key

tblCompanyActivityTasks.CompanyActivityTasksID =
tblCompanyActivities.ActivityID (one to many)



I'm open to any, and all, suggestions as to handle flagging something as
"done" without comitting Excelicide...

Larry

Larry

Based on your description, it sounds like you have the following entities
:
[Projects]
[Companies]
[Requirements]
[CompanyRequirementOnProject]

and it sounds like you are "calculating" that all of a company's
requirements have been satisfied in order to declare them "done".

I suspect you are trying to "commit spreadsheet on Access", treating it
like Excel rather than as a relational database.

Please list your tables and relationships...

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

To clarify: Each child record can (and often does) have multiple
activities. When the client team believes they have satisfied all of the
requirements, for the child record they want to label that record as
complete; thus the activity with a value of "done."

Here's a general picture:

The parent record contains information that describes a project.

The child record contains information that describes a compay who is
potential partner/bidder on the project.

The activities involve various interactions with the company (send
information, prepare photos, follow-up e-mail, etc.)

The request from the client team is to be able to see - at a glance -
what has been finished and what is still left pending.

I smacked my head when one of the team members said, "Oh, when we used
to use Excel we would highlight the ones that were done in blue. Can we
do that with Access?" My (very) bad for not asking why the rows had
different colors. [Hangs head in shame for thinking they were merely
alternating color bars...]

Larry


Larry

So you're saying that each child record has only one Activity ("an
activity of 'Done'") or "each child record can have several activities
associated with it"? These seem to be contradictory requirements.

It seems like you are focused more on the mechanics than the underlying
data.

It would help to know what you're starting with, and what you want to
end up with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

I'm working on a database where there is a parent record that can
contain one to "n" child records. In turn, each child record can have
several activities associated with it.

My client as asked that if they create an activity of "Done," would I
be able to reflect that in the list of child records?

If I add a Yes/No field to the child record table and in the Activty
combo box's AfterUpdate event, I set the field to True if the Activity
is "Done" or False if it isn't. The query that displays the list of
child records would be updated with the Yes/No field.

However, they also want to know if the parent list of records - shown
on a continous form - can reflect whether all of the child records are
flagged as "done"?

That I'm not sure I can do, so I'm looking for suggestions as to how
to handle this.

Thanks!

Larry

 

[aikochen]

Larry

So you're saying that each child record has only one Activity ("an
activity of 'Done'") or "each child record can have several activities
associated with it"? These seem to be contradictory requirements.

It seems like you are focused more on the mechanics than the underlying
data.

It would help to know what you're starting with, and what you want to end
up with.

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP