Re: Parent/Child Relationship to Integrate Data in Reports

Tech-Archive recommends: Fix windows errors by optimizing your registry

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" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Stacey Crowhurst" <StaceyCrowhurst@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:67DA0F96-5C9F-45E6-8D07-87583CB70CD6@xxxxxxxxxxxxxxxx
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" wrote:

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.

"Stacey Crowhurst" <StaceyCrowhurst@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
news:B1D0BA42-9613-4177-B130-16630507C125@xxxxxxxxxxxxxxxx
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!!!


.

Quantcast