Parent/Child Relationship to Integrate Data in Reports

  • Thread starter Thread starter Stacey Crowhurst
  • Start date Start date
S

Stacey Crowhurst

Hi. I have a database used to track various projects at my company. They
can be office remodels, construction, etc. Nearly everything in the database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want the
small project [project ID = TCC09-02] to somehow be integrated with the large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02 expenses
have been taken out. Is there anyway to tell Access that TCC09-02 rolls up
into RAC04-01 without rewriting all my queries and reports? Please let me
know if you need more information. Thanks in advance for your assistance. I
don't know where I'd be without the help board!!!

Thanks,
Stacey
 
I thought that I responded to this.
Two ways to go but both require some editing.
Project: IIF([Project ID] = "TCC09-02", "RAC04-01", [project ID])

Or create a translation table --
Project Project ID
TCC09-02 RAC04-01
RAC04-01 RAC04-01
XYZ08-01 XYZ08-01
etc..
 
I thought that I responded to this.
Two ways to go but both require some editing.
Project: IIF([Project ID] = "TCC09-02", "RAC04-01", [project ID])

Or create a translation table --
Project Project ID
TCC09-02 RAC04-01
RAC04-01 RAC04-01
XYZ08-01 XYZ08-01
etc..
 
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under ProjectID,
but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main projects
once only, and summarize (group/sum/count) the fields from the related
projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects under
each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end up
with a project that is its own grandparent. So you have to take steps to
prevent this kind of thing, or else develop a way to handling it if you do
want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields to
ProjectID and the LinkChildFields to ParentProjectID. Cancel the subform's
BeforeInsert event if the main form is at a new record (so you can't enter a
record in the subform without any existing record in the main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to modify
existing queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi. I have a database used to track various projects at my company. They
can be office remodels, construction, etc. Nearly everything in the
database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want the
small project [project ID = TCC09-02] to somehow be integrated with the
large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02
expenses
have been taken out. Is there anyway to tell Access that TCC09-02 rolls
up
into RAC04-01 without rewriting all my queries and reports? Please let me
know if you need more information. Thanks in advance for your assistance.
I
don't know where I'd be without the help board!!!

Thanks,
Stacey
Click to expand...
 
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under ProjectID,
but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main projects
once only, and summarize (group/sum/count) the fields from the related
projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects under
each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end up
with a project that is its own grandparent. So you have to take steps to
prevent this kind of thing, or else develop a way to handling it if you do
want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields to
ProjectID and the LinkChildFields to ParentProjectID. Cancel the subform's
BeforeInsert event if the main form is at a new record (so you can't enter a
record in the subform without any existing record in the main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to modify
existing queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi. I have a database used to track various projects at my company. They
can be office remodels, construction, etc. Nearly everything in the
database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want the
small project [project ID = TCC09-02] to somehow be integrated with the
large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02
expenses
have been taken out. Is there anyway to tell Access that TCC09-02 rolls
up
into RAC04-01 without rewriting all my queries and reports? Please let me
know if you need more information. Thanks in advance for your assistance.
I
don't know where I'd be without the help board!!!

Thanks,
Stacey
Click to expand...
 
Stacey, I should have mentioned that your main projects form is now bound to
a query that has this criteria under the new ParentProjectID field:
Is Null

That stops the subprojects appearing in the main form, i.e. the appear only
in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under
ProjectID, but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main
projects once only, and summarize (group/sum/count) the fields from the
related projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects
under each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end
up with a project that is its own grandparent. So you have to take steps
to prevent this kind of thing, or else develop a way to handling it if you
do want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields to
ProjectID and the LinkChildFields to BParentProjectID. Cancel the
subform's eforeInsert event if the main form is at a new record (so you
can't enter a record in the subform without any existing record in the
main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to
modify existing queries.

message
Hi. I have a database used to track various projects at my company.
Theycan be office remodels, construction, etc. Nearly everything in the
databaseis related to the project ID. I have a new situation where a
small (separately scoped) project is being funded by a larger one. I
want the small project [project ID = TCC09-02] to somehow be integrated
with the large project [project ID = RAC04-01] when reporting. I need
TCC09-02 to be separately tracked, however, when I pull up reports
telling me how many dollars are left in RAC04-01 I need to make sure that
the TCC09-02 expenses have been taken out. Is there anyway to tell
Access that TCC09-02 rolls up into RAC04-01 without rewriting all my
queries and reports? Please let me know if
you need more information. Thanks in advance for your assistance. I
don't know where I'd be without the help board!!!
Click to expand...
Click to expand...
 
Stacey, I should have mentioned that your main projects form is now bound to
a query that has this criteria under the new ParentProjectID field:
Is Null

That stops the subprojects appearing in the main form, i.e. the appear only
in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Allen Browne said:
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under
ProjectID, but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main
projects once only, and summarize (group/sum/count) the fields from the
related projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects
under each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end
up with a project that is its own grandparent. So you have to take steps
to prevent this kind of thing, or else develop a way to handling it if you
do want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields to
ProjectID and the LinkChildFields to BParentProjectID. Cancel the
subform's eforeInsert event if the main form is at a new record (so you
can't enter a record in the subform without any existing record in the
main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to
modify existing queries.

message
Hi. I have a database used to track various projects at my company.
Theycan be office remodels, construction, etc. Nearly everything in the
databaseis related to the project ID. I have a new situation where a
small (separately scoped) project is being funded by a larger one. I
want the small project [project ID = TCC09-02] to somehow be integrated
with the large project [project ID = RAC04-01] when reporting. I need
TCC09-02 to be separately tracked, however, when I pull up reports
telling me how many dollars are left in RAC04-01 I need to make sure that
the TCC09-02 expenses have been taken out. Is there anyway to tell
Access that TCC09-02 rolls up into RAC04-01 without rewriting all my
queries and reports? Please let me know if
you need more information. Thanks in advance for your assistance. I
don't know where I'd be without the help board!!!
Click to expand...
Click to expand...
 
Thanks Allen. I'm almost there! A few follow up questions.

1. How do I cancel the before insert event?

2. How do I tell my queries to add any expenses when ParentProjectID =
ProjectID?

Thanks again!

Allen Browne said:
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under ProjectID,
but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main projects
once only, and summarize (group/sum/count) the fields from the related
projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects under
each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end up
with a project that is its own grandparent. So you have to take steps to
prevent this kind of thing, or else develop a way to handling it if you do
want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields to
ProjectID and the LinkChildFields to ParentProjectID. Cancel the subform's
BeforeInsert event if the main form is at a new record (so you can't enter a
record in the subform without any existing record in the main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to modify
existing queries.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Hi. I have a database used to track various projects at my company. They
can be office remodels, construction, etc. Nearly everything in the
database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want the
small project [project ID = TCC09-02] to somehow be integrated with the
large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02
expenses
have been taken out. Is there anyway to tell Access that TCC09-02 rolls
up
into RAC04-01 without rewriting all my queries and reports? Please let me
know if you need more information. Thanks in advance for your assistance.
I
don't know where I'd be without the help board!!!

Thanks,
Stacey
Click to expand...
Click to expand...
 
Re 1, open the subform in design view, and set its Before Update property
to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Between the "Private Sub Form_BeforeUpdate(...)" and "End Sub, add:
Cancel = True
MsgBox "Enter the main form record first."

Re 2, this will depend on how you designed your tables. Chances are that one
project can have many costs over time, so you have a one-to-many relation
between Project and Expense. A sub-project has its own ProjectID, so it can
have its own records in the Expense table.

If that's the setup, and you want to summarize projects and their costs:
a) Create a query joining the projects and expenses tables.
Depress the Total button on the toolbar.
Group By the ProjectID.
Sum the Amount from the expenses table.
Save as (say) ProjectExpense.

b) Create another query using ProjectExpense as the source 'table.'
Add another copy of ProjectExpense table to the query.
Access will alias it as ProjectExpense_1.
In the upper pane of query design, drag ProjectExpense.ProjectID onto
ProjectExpense_1.ProjectID.

c) Double-click the line Access showed to join the 2 tables.
Access pops up a dialog with 3 options. Choose:
All records from ProjectExpense, and any matches from ProjectExpense_1.
This is known as an outer join. More info:
http://allenbrowne.com/casu-02.html

d) Depress the Total button on the toolbar.
Group By ProjectExpense.ProjectID
First Of ProjectExpense.Amount
Count ProjectExpense_1.ProjectID
Sum ProjectExpense_1.Amount

e) Type this expression into a fresh column, in the Field row:
Total: [FirstOfAmount] + IIf([SumOfAmount] Is Null, 0, [SumOfAmount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks Allen. I'm almost there! A few follow up questions.

1. How do I cancel the before insert event?

2. How do I tell my queries to add any expenses when ParentProjectID =
ProjectID?

Thanks again!

Allen Browne said:
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under
ProjectID,
but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main
projects
once only, and summarize (group/sum/count) the fields from the related
projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects
under
each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end
up
with a project that is its own grandparent. So you have to take steps to
prevent this kind of thing, or else develop a way to handling it if you
do
want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields
to
ProjectID and the LinkChildFields to ParentProjectID. Cancel the
subform's
BeforeInsert event if the main form is at a new record (so you can't
enter a
record in the subform without any existing record in the main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to
modify
existing queries.

message
Hi. I have a database used to track various projects at my company.
They
can be office remodels, construction, etc. Nearly everything in the
database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want
the
small project [project ID = TCC09-02] to somehow be integrated with the
large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02
expenses
have been taken out. Is there anyway to tell Access that TCC09-02
rolls
up
into RAC04-01 without rewriting all my queries and reports? Please let
me
know if you need more information. Thanks in advance for your
assistance.
I don't know where I'd be without the help board!!!
Click to expand...
Click to expand...
Click to expand...
 
Thanks again Allen. I am working on updating my queries but am confident
about it! One last question though. On the code to cancel the before update
procedure I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = True
MsgBox "Enter the parent project on the main form record first."

End Sub

But now, when I am in the project form it won't allow me to create a new
record. Am I missing something?

Thanks again!
Stacey



Allen Browne said:
Re 1, open the subform in design view, and set its Before Update property
to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Between the "Private Sub Form_BeforeUpdate(...)" and "End Sub, add:
Cancel = True
MsgBox "Enter the main form record first."

Re 2, this will depend on how you designed your tables. Chances are that one
project can have many costs over time, so you have a one-to-many relation
between Project and Expense. A sub-project has its own ProjectID, so it can
have its own records in the Expense table.

If that's the setup, and you want to summarize projects and their costs:
a) Create a query joining the projects and expenses tables.
Depress the Total button on the toolbar.
Group By the ProjectID.
Sum the Amount from the expenses table.
Save as (say) ProjectExpense.

b) Create another query using ProjectExpense as the source 'table.'
Add another copy of ProjectExpense table to the query.
Access will alias it as ProjectExpense_1.
In the upper pane of query design, drag ProjectExpense.ProjectID onto
ProjectExpense_1.ProjectID.

c) Double-click the line Access showed to join the 2 tables.
Access pops up a dialog with 3 options. Choose:
All records from ProjectExpense, and any matches from ProjectExpense_1.
This is known as an outer join. More info:
http://allenbrowne.com/casu-02.html

d) Depress the Total button on the toolbar.
Group By ProjectExpense.ProjectID
First Of ProjectExpense.Amount
Count ProjectExpense_1.ProjectID
Sum ProjectExpense_1.Amount

e) Type this expression into a fresh column, in the Field row:
Total: [FirstOfAmount] + IIf([SumOfAmount] Is Null, 0, [SumOfAmount])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks Allen. I'm almost there! A few follow up questions.

1. How do I cancel the before insert event?

2. How do I tell my queries to add any expenses when ParentProjectID =
ProjectID?

Thanks again!

Allen Browne said:
One way to do this is to include a new field in your projects table:
ParentProjectID Text same size as your ProjectID field.

The new field is null (left blank) for most projects, but for project
TCC09-02, the parent project would be RAC04-01.

You could now write a query:
1. Create a query with 2 copies of the projects table (add the same table
twice.)

2. Join ProjectID from one table to ParentProjectID in the other.

3. Make it an outer join (by double-clicking the join line, and choosing
"All records from Projects, and any matches from Projects_1.)

4. In the Criteria row under your ParentProjectID field, enter:
Null
This limits it so the query reports only the main projects under
ProjectID,
but also has rows for the related projects.

5. (Optional) Make it a Group By query if you want to list the main
projects
once only, and summarize (group/sum/count) the fields from the related
projects.

Alternatively, you could create a main report from the records where
ParentProjectID is null, with a subreport to list the related projects
under
each.

This is an incredibly simple and elegant solution, but gives you one
headache to solve. A related project can, itself have a related project,
which the above query/report doesn't address. It's even possible to end
up
with a project that is its own grandparent. So you have to take steps to
prevent this kind of thing, or else develop a way to handling it if you
do
want to have sub-sub-projects nested to any depth.

Assuming that you don't want sub-sub-projects at all, it's fairly easy to
design the interface that really only lets users create one level of
sub-project. On the form where you enter projects, you don't provide any
text box for the new ParentProjectID field. Instead, you create a subform
where the sub-projects are entered, and you set up the LinkMasterFields
to
ProjectID and the LinkChildFields to ParentProjectID. Cancel the
subform's
BeforeInsert event if the main form is at a new record (so you can't
enter a
record in the subform without any existing record in the main form.)

Once you've done this once, it's actually easier to do that it is to
describe. And since there's only one new field, it's not too hard to
modify
existing queries.

message
Hi. I have a database used to track various projects at my company.
They
can be office remodels, construction, etc. Nearly everything in the
database
is related to the project ID. I have a new situation where a small
(separately scoped) project is being funded by a larger one. I want
the
small project [project ID = TCC09-02] to somehow be integrated with the
large
project [project ID = RAC04-01] when reporting. I need TCC09-02 to be
separately tracked, however, when I pull up reports telling me how many
dollars are left in RAC04-01 I need to make sure that the TCC09-02
expenses
have been taken out. Is there anyway to tell Access that TCC09-02
rolls
up
into RAC04-01 without rewriting all my queries and reports? Please let
me
know if you need more information. Thanks in advance for your
assistance.
I don't know where I'd be without the help board!!!
Click to expand...
Click to expand...
Click to expand...
 
Right: you need to handle the condition when records are/are not allowed to
be created.

Example:
If Me.Parent.NewRecord Then
Cancel = True
MsgBox ...
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message
Thanks again Allen. I am working on updating my queries but am confident
about it! One last question though. On the code to cancel the before
update
procedure I have:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Cancel = True
MsgBox "Enter the parent project on the main form record first."

End Sub

But now, when I am in the project form it won't allow me to create a new
record. Am I missing something?

Thanks again!
Stacey



Allen Browne said:
Re 1, open the subform in design view, and set its Before Update property
to:
[Event Procedure]
Click the Build button (...) beside this. Access opens the code window.
Between the "Private Sub Form_BeforeUpdate(...)" and "End Sub, add:
Cancel = True
MsgBox "Enter the main form record first."

Re 2, this will depend on how you designed your tables. Chances are that
one
project can have many costs over time, so you have a one-to-many relation
between Project and Expense. A sub-project has its own ProjectID, so it
can
have its own records in the Expense table.

If that's the setup, and you want to summarize projects and their costs:
a) Create a query joining the projects and expenses tables.
Depress the Total button on the toolbar.
Group By the ProjectID.
Sum the Amount from the expenses table.
Save as (say) ProjectExpense.

b) Create another query using ProjectExpense as the source 'table.'
Add another copy of ProjectExpense table to the query.
Access will alias it as ProjectExpense_1.
In the upper pane of query design, drag ProjectExpense.ProjectID onto
ProjectExpense_1.ProjectID.

c) Double-click the line Access showed to join the 2 tables.
Access pops up a dialog with 3 options. Choose:
All records from ProjectExpense, and any matches from
ProjectExpense_1.
This is known as an outer join. More info:
http://allenbrowne.com/casu-02.html

d) Depress the Total button on the toolbar.
Group By ProjectExpense.ProjectID
First Of ProjectExpense.Amount
Count ProjectExpense_1.ProjectID
Sum ProjectExpense_1.Amount

e) Type this expression into a fresh column, in the Field row:
Total: [FirstOfAmount] + IIf([SumOfAmount] Is Null, 0, [SumOfAmount])
Click to expand...
Click to expand...
 
Back
Top