Heirarchical report question

[Amy Blankenship]

I have a data set that should look something like this:

Section 1
Field 1
Field 2
Field etc...
Section 2
Subsection 1
Field 1
Field 2
Subsection 2
Field 1
etc...

Note that the fields are bits of data that need to be filled in, but don't
directly correspond to the form fields. I'm having trouble wrapping my head
around how to get the fields to be directly under the section when there is
no subsection, but under the subsection when such exists.

I have a query that I think is giving me the right data, but I need a few
pointers on how to make the report do what I want.

If it helps, this is the query:
SELECT
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),[awAuditToolFieldDef].[AuditGroupDesc],[AuditToolGroup].[AuditGroupDesc])
AS ParentDesc,
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),"",[awAuditToolFieldDef].[AuditGroupDesc])
AS ChildDesc, awAuditToolFieldDef.FieldLabel,
AuditScenarioFieldCorrect.CorrectValueID,
AuditScenarioFieldCorrect.CanBeQuestionable,
AuditScenarioFieldCorrect.QuestionableScore, AuditScenarioFieldCorrect.FYI,
AuditScenarioFieldCorrect.ContextPage,
AuditScenarioFieldCorrect.ContextStart, AuditScenarioFieldCorrect.ContextEnd
FROM (awAuditToolFieldDef INNER JOIN AuditScenarioFieldCorrect ON
awAuditToolFieldDef.AuditToolFieldID = AuditScenarioFieldCorrect.FieldID)
LEFT JOIN AuditToolGroup ON awAuditToolFieldDef.ParentGroupID =
AuditToolGroup.AuditGroupID
WHERE (((AuditScenarioFieldCorrect.ScenarioID)=[Enter ScenarioID]));

Thanks;

Amy

 

[Amy Blankenship]

Hello...?

I wouldn't think this question was too hard for this group to handle, so I
must have forgotten my deodorant...

 

[Amy Blankenship]

I put on deodorant now, and brushed my teeth... I don't suppose any one
would care to field this now, huh?

Hello...?

I wouldn't think this question was too hard for this group to handle, so I
must have forgotten my deodorant...

I have a data set that should look something like this:

Section 1
Field 1
Field 2
Field etc...
Section 2
Subsection 1
Field 1
Field 2
Subsection 2
Field 1
etc...

Note that the fields are bits of data that need to be filled in, but
don't directly correspond to the form fields. I'm having trouble
wrapping my head around how to get the fields to be directly under the
section when there is no subsection, but under the subsection when such
exists.

I have a query that I think is giving me the right data, but I need a few
pointers on how to make the report do what I want.

If it helps, this is the query:
SELECT
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),[awAuditToolFieldDef].[AuditGroupDesc],[AuditToolGroup].[AuditGroupDesc])
AS ParentDesc,
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),"",[awAuditToolFieldDef].[AuditGroupDesc])
AS ChildDesc, awAuditToolFieldDef.FieldLabel,
AuditScenarioFieldCorrect.CorrectValueID,
AuditScenarioFieldCorrect.CanBeQuestionable,
AuditScenarioFieldCorrect.QuestionableScore,
AuditScenarioFieldCorrect.FYI, AuditScenarioFieldCorrect.ContextPage,
AuditScenarioFieldCorrect.ContextStart,
AuditScenarioFieldCorrect.ContextEnd
FROM (awAuditToolFieldDef INNER JOIN AuditScenarioFieldCorrect ON
awAuditToolFieldDef.AuditToolFieldID = AuditScenarioFieldCorrect.FieldID)
LEFT JOIN AuditToolGroup ON awAuditToolFieldDef.ParentGroupID =
AuditToolGroup.AuditGroupID
WHERE (((AuditScenarioFieldCorrect.ScenarioID)=[Enter ScenarioID]));

Thanks;

Amy

 

[John Spencer]

Dear Amy,

Try posting a few records (or partial records) and then indicating how you think
they should appear in your report.

For instance, what field(s) gives you Section titles?
What determines that there is a subsection?

How many levels do you have in your report? Just Section and Subsection or do
Subsections have sub-subsections?

I'm still trying to figure out what you want to do. Until someone gets that
figured out, then no one can tell you how to do it.

I put on deodorant now, and brushed my teeth... I don't suppose any one
would care to field this now, huh?

Hello...?

I wouldn't think this question was too hard for this group to handle, so I
must have forgotten my deodorant...

I have a data set that should look something like this:

Section 1
Field 1
Field 2
Field etc...
Section 2
Subsection 1
Field 1
Field 2
Subsection 2
Field 1
etc...

Note that the fields are bits of data that need to be filled in, but
don't directly correspond to the form fields. I'm having trouble
wrapping my head around how to get the fields to be directly under the
section when there is no subsection, but under the subsection when such
exists.

I have a query that I think is giving me the right data, but I need a few
pointers on how to make the report do what I want.

If it helps, this is the query:
SELECT
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),[awAuditToolFieldDef].[AuditGroupDesc],[AuditToolGroup].[AuditGroupDesc])
AS ParentDesc,
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),"",[awAuditToolFieldDef].[AuditGroupDesc])
AS ChildDesc, awAuditToolFieldDef.FieldLabel,
AuditScenarioFieldCorrect.CorrectValueID,
AuditScenarioFieldCorrect.CanBeQuestionable,
AuditScenarioFieldCorrect.QuestionableScore,
AuditScenarioFieldCorrect.FYI, AuditScenarioFieldCorrect.ContextPage,
AuditScenarioFieldCorrect.ContextStart,
AuditScenarioFieldCorrect.ContextEnd
FROM (awAuditToolFieldDef INNER JOIN AuditScenarioFieldCorrect ON
awAuditToolFieldDef.AuditToolFieldID = AuditScenarioFieldCorrect.FieldID)
LEFT JOIN AuditToolGroup ON awAuditToolFieldDef.ParentGroupID =
AuditToolGroup.AuditGroupID
WHERE (((AuditScenarioFieldCorrect.ScenarioID)=[Enter ScenarioID]));

Thanks;

Amy

 

[Amy Blankenship]

The AuditToolGroup field gives the names for both the sections and
subsections, which is the reason for the IIF statements. If there is a
value in ChildDesc, then there is a subgroup. The subsections do not have
further sections, but they do have AuditToolFields.

I'm not so concerned about the specifics of this, but more about a general
approach. For instance, this is a sample answer that of course is probably
not going to be the answer to my question but gives you a general feel for
what I'm looking for. (I'm technically pretty adequate but I do need a
general direction to look in.):

"Your query won't do what you want. Instead, you need to have one query
that only returns the sections, then a second query that contains the
subsections. A third query would define the fields. Use the wizard to
create a report from the first query, then in its details section insert a
subreport which uses the second query as its recordsource and embed another
subreport for the fields in the subsection. When there are no subsections
in a section, use code to switch the subreport so you're using the fields
subreport directly in the Section report."

This would roughly be the answer I'd give for making a form for entering the
data, but I don't have a lot of experience with reports. Don't suppose you
could just give me a general approach that would just point me in the right
direction...? I'd put on makeup...



-Amy

Dear Amy,

Try posting a few records (or partial records) and then indicating how you
think
they should appear in your report.

For instance, what field(s) gives you Section titles?
What determines that there is a subsection?

How many levels do you have in your report? Just Section and Subsection
or do
Subsections have sub-subsections?

I'm still trying to figure out what you want to do. Until someone gets
that
figured out, then no one can tell you how to do it.

I put on deodorant now, and brushed my teeth... I don't suppose any one
would care to field this now, huh?

Hello...?

I wouldn't think this question was too hard for this group to handle,
so I
must have forgotten my deodorant...

I have a data set that should look something like this:

Section 1
Field 1
Field 2
Field etc...
Section 2
Subsection 1
Field 1
Field 2
Subsection 2
Field 1
etc...

Note that the fields are bits of data that need to be filled in, but
don't directly correspond to the form fields. I'm having trouble
wrapping my head around how to get the fields to be directly under the
section when there is no subsection, but under the subsection when
such
exists.

I have a query that I think is giving me the right data, but I need a
few
pointers on how to make the report do what I want.

If it helps, this is the query:
SELECT
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),[awAuditToolFieldDef].[AuditGroupDesc],[AuditToolGroup].[AuditGroupDesc])
AS ParentDesc,
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),"",[awAuditToolFieldDef].[AuditGroupDesc])
AS ChildDesc, awAuditToolFieldDef.FieldLabel,
AuditScenarioFieldCorrect.CorrectValueID,
AuditScenarioFieldCorrect.CanBeQuestionable,
AuditScenarioFieldCorrect.QuestionableScore,
AuditScenarioFieldCorrect.FYI, AuditScenarioFieldCorrect.ContextPage,
AuditScenarioFieldCorrect.ContextStart,
AuditScenarioFieldCorrect.ContextEnd
FROM (awAuditToolFieldDef INNER JOIN AuditScenarioFieldCorrect ON
awAuditToolFieldDef.AuditToolFieldID =
AuditScenarioFieldCorrect.FieldID)
LEFT JOIN AuditToolGroup ON awAuditToolFieldDef.ParentGroupID =
AuditToolGroup.AuditGroupID
WHERE (((AuditScenarioFieldCorrect.ScenarioID)=[Enter ScenarioID]));

Thanks;

Amy

 

[John Spencer]

If your query works to give you the correct data, then I would say you could
cancel the appropriate section depending on the value in ParentDesc or
ChildDesc.
Your report would group on ParentDesc and Child Desc with the details
section consisting of your fields.

For instance something like the following code in the ChildDesc groupheader
section's Format event should cancel the section being generated if
ChildDesc was null or a zero-length string

Private Sub GroupHeader1_Format(Cancel As Integer, FormatCount As Integer)
Cancel = Len(Me.ChildDesc & vbNullString) = 0
End Sub

Of course, this all hinges on your query returning the correct values.

Another option might be to use sub-reports which work very much like
sub-forms. If a sub-report has no matching data it will not show up in the
report. I'm not sure how I could use that given your current scenario.

The AuditToolGroup field gives the names for both the sections and
subsections, which is the reason for the IIF statements. If there is a
value in ChildDesc, then there is a subgroup. The subsections do not have
further sections, but they do have AuditToolFields.

I'm not so concerned about the specifics of this, but more about a general
approach. For instance, this is a sample answer that of course is
probably not going to be the answer to my question but gives you a general
feel for what I'm looking for. (I'm technically pretty adequate but I do
need a general direction to look in.):

"Your query won't do what you want. Instead, you need to have one query
that only returns the sections, then a second query that contains the
subsections. A third query would define the fields. Use the wizard to
create a report from the first query, then in its details section insert a
subreport which uses the second query as its recordsource and embed
another subreport for the fields in the subsection. When there are no
subsections in a section, use code to switch the subreport so you're using
the fields subreport directly in the Section report."

This would roughly be the answer I'd give for making a form for entering
the data, but I don't have a lot of experience with reports. Don't
suppose you could just give me a general approach that would just point me
in the right direction...? I'd put on makeup...



-Amy

Dear Amy,

Try posting a few records (or partial records) and then indicating how
you think
they should appear in your report.

For instance, what field(s) gives you Section titles?
What determines that there is a subsection?

How many levels do you have in your report? Just Section and Subsection
or do
Subsections have sub-subsections?

I'm still trying to figure out what you want to do. Until someone gets
that
figured out, then no one can tell you how to do it.

I put on deodorant now, and brushed my teeth... I don't suppose any one
would care to field this now, huh?

Hello...?

I wouldn't think this question was too hard for this group to handle,
so I
must have forgotten my deodorant...

I have a data set that should look something like this:

Section 1
Field 1
Field 2
Field etc...
Section 2
Subsection 1
Field 1
Field 2
Subsection 2
Field 1
etc...

Note that the fields are bits of data that need to be filled in, but
don't directly correspond to the form fields. I'm having trouble
wrapping my head around how to get the fields to be directly under
the
section when there is no subsection, but under the subsection when
such
exists.

I have a query that I think is giving me the right data, but I need a
few
pointers on how to make the report do what I want.

If it helps, this is the query:
SELECT
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),[awAuditToolFieldDef].[AuditGroupDesc],[AuditToolGroup].[AuditGroupDesc])
AS ParentDesc,
IIf(IsNull([AuditToolGroup].[AuditGroupDesc]),"",[awAuditToolFieldDef].[AuditGroupDesc])
AS ChildDesc, awAuditToolFieldDef.FieldLabel,
AuditScenarioFieldCorrect.CorrectValueID,
AuditScenarioFieldCorrect.CanBeQuestionable,
AuditScenarioFieldCorrect.QuestionableScore,
AuditScenarioFieldCorrect.FYI, AuditScenarioFieldCorrect.ContextPage,
AuditScenarioFieldCorrect.ContextStart,
AuditScenarioFieldCorrect.ContextEnd
FROM (awAuditToolFieldDef INNER JOIN AuditScenarioFieldCorrect ON
awAuditToolFieldDef.AuditToolFieldID =
AuditScenarioFieldCorrect.FieldID)
LEFT JOIN AuditToolGroup ON awAuditToolFieldDef.ParentGroupID =
AuditToolGroup.AuditGroupID
WHERE (((AuditScenarioFieldCorrect.ScenarioID)=[Enter ScenarioID]));

Thanks;

Amy

 

[Amy Blankenship]

If your query works to give you the correct data, then I would say you
could cancel the appropriate section depending on the value in ParentDesc
or ChildDesc.
Your report would group on ParentDesc and Child Desc with the details
section consisting of your fields.

For instance something like the following code in the ChildDesc
groupheader section's Format event should cancel the section being
generated if ChildDesc was null or a zero-length string

So, how would I see the fields in this scenario if there are no child
groups? The fields need to be grouped under the Parent if there are no
children and under the child if there are.

Thanks;

Amy

 

[John Spencer]

Hmm, I thought that would work. I am assuming you tested it.

Ok, then just hide the ChildDesc group (visibility property)

Use code in the format or print event of the section, something like the
following.

Me.Section(acGroupLevel2Header).Visible = Len(me.ChildDesc &
vbnullstring) > 0

You would still be grouping by the value in Child Desc, but since it is null
or zero-length string ("") you don't print it.