recursive relationship parent/child on reports

[mglnet]

How I can create a report for a relationship parent/child?

I have a reflexive relation in a table of companies, so that every company
can subcontract to another company. Father - son exists one a relation
between companies

Thanks

 

[BigJohn]

Single level relationships happen through Sub-Reports. And is the easiest to
install. Create a very simple list report on the sub-report, and use the
subreport wizard (add the control to the report and the wizard will start) to
set connection parameters.

Recursive options happen through either modules returning a string value
(the module calls through the recursion string and appends the values
complete with carriage returns and tab characters) or through sub-query
values (the reports main query has multiple sub-queries which itself uses the
higher level sub-query as input and all links to the main ID).

 

[w1fini]

hi,
thank you for your adivce,

our problem is that we have a multi level relationship and the number of
levels are unknown at buildtime.

So we need to create the subreports on runtime. We thought about using
visual basic. But how can you create a subreport within a report using code?

 

[mglnet]

Thanks BigJohn.

I had created a report with one subreport and I have visualized the first
level on the recursion, but the rest of levels doesn´t appear in the correct
order.

Here is an example:

CodCompany Name Father
1 Company1 null
2 Company2 null
3 Company3 1
4 Company4 2
5 Company5 2
6 Company6 4

here is the report that i need.
Company1
Company3
Company2
Company4
Company6
Company5

Thanks

 

[Stefan Hoffmann]

hi,

I had created a report with one subreport and I have visualized the first
level on the recursion, but the rest of levels doesn´t appear in the correct
order.

Here is an example:

CodCompany Name Father
1 Company1 null
2 Company2 null
3 Company3 1
4 Company4 2
5 Company5 2
6 Company6 4

here is the report that i need.
Company1
Company3
Company2
Company4
Company6
Company5

You need a temporary table to get your data filtered and sorted with a
method in VBA:

Public Sub MakeReportTable(ACodCompany As Long)

CurrentDb.Execute "DELETE FROM ReportTable"
RecursionReportTable ACodCompany

End Sub

Private Sub RecursionReportTable(ACodCompany As Long)
' We are doing a recursion top-down.

Dim CodCompany As Long
Dim OrderBy As Long

OrderBy = Nz(DMax("OrderBy", "ReportTable"), 0) + 1
CurrentDb.Execute "INSERT INTO ReportTable(CodCompany, OrderBy) " & _
"VALUES (" & ACodCompany & ", " & OrderBy & ")"

CodCompany = Nz(DLookup("Father", "Table", _
"CodCompany = " & ACodCompany), -1)

If CodCompany <> -1 Then
RecursionReportTable CodCompany
End If

End Sub



mfG
--> stefan <--

 

[mglnet]

Hello Stefan.

Thanks for your answer.

I need to create a temporaly table to save the recursion, isn't it? And I
need to use the "OnLoad" event of the report to create the recursion to get
the order of the companies. Does the report need to be dynamic?
Can you put an example?

Thanks

 

[Stefan Hoffmann]

hi,

I need to create a temporaly table to save the recursion, isn't it?

Yes, somehow. You need a table for your report. I called it temporary,
because the data in it is redundantly stored and only valid for the
actual printing process.
So it is not quite correct to call it temporary as i would create a
persistent table.

And I
need to use the "OnLoad" event of the report to create the recursion to get
the order of the companies. Yes.

Does the report need to be dynamic?

No. Just sort the table by [ORDERBY]. You may group it on the report by
the same field, if you have some dependend data or sub-reports.


mfG
--> stefan <--

 

[mglnet]

Thanks stefan

hi,

I need to create a temporaly table to save the recursion, isn't it?

Yes, somehow. You need a table for your report. I called it temporary,
because the data in it is redundantly stored and only valid for the
actual printing process.
So it is not quite correct to call it temporary as i would create a
persistent table.

And I
need to use the "OnLoad" event of the report to create the recursion to get
the order of the companies. Yes.

Does the report need to be dynamic?

No. Just sort the table by [ORDERBY]. You may group it on the report by
the same field, if you have some dependend data or sub-reports.


mfG
--> stefan <--

 

[JohnLute]

Hi, Stefan.

I have what I think is a similar problem. I have a subreport with the record
source:

SELECT tblProfilesRevisions.txtProfileID,
tblProfilesRevisionsProfiles.txtProfileID,
tblProfilesRevisions.EffectiveDate, tblProfilesRevisions.IssueNumber,
tblProfilesRevisions.Revision, tblProfilesRevisions.Status
FROM tblProfilesRevisions LEFT JOIN tblProfilesRevisionsProfiles ON
tblProfilesRevisions.numProfilesRevisionsID =
tblProfilesRevisionsProfiles.numProfilesRevisionsID
WHERE (((tblProfilesRevisions.Status)="Approved"))
ORDER BY tblProfilesRevisions.EffectiveDate DESC;

This returns the desired data.

My challenge is setting the Child/Master links in the main report. The main
report queries the field txtProfileID which needs to be the Master Link
however this leaves me with two Child Links:
tblProfilesRevisions.txtProfileID
tblProfilesRevisionsProfiles.txtProfileID

I've tried linking these in the main report however when both are made as
Child Links then the subreport returns null.

Can I make both Child Links? If so how can I do this? The solutions
discussed in this particular thread area bit beyond me and I don't think they
necessarily are feasible for my particular challenge.

Thanks!

--
www.Marzetti.com

hi,

I need to create a temporaly table to save the recursion, isn't it?

Yes, somehow. You need a table for your report. I called it temporary,
because the data in it is redundantly stored and only valid for the
actual printing process.
So it is not quite correct to call it temporary as i would create a
persistent table.

And I
need to use the "OnLoad" event of the report to create the recursion to get
the order of the companies. Yes.

Does the report need to be dynamic?

No. Just sort the table by [ORDERBY]. You may group it on the report by
the same field, if you have some dependend data or sub-reports.


mfG
--> stefan <--

 

[Stefan Hoffmann]

hi John,

I have what I think is a similar problem. I have a subreport with the record
source:

SELECT tblProfilesRevisions.txtProfileID,
tblProfilesRevisionsProfiles.txtProfileID,
tblProfilesRevisions.EffectiveDate, tblProfilesRevisions.IssueNumber,
tblProfilesRevisions.Revision, tblProfilesRevisions.Status
FROM tblProfilesRevisions LEFT JOIN tblProfilesRevisionsProfiles ON
tblProfilesRevisions.numProfilesRevisionsID =
tblProfilesRevisionsProfiles.numProfilesRevisionsID
WHERE (((tblProfilesRevisions.Status)="Approved"))
ORDER BY tblProfilesRevisions.EffectiveDate DESC;

This returns the desired data.

tblProfilesRevisions.txtProfileID and
tblProfilesRevisionsProfiles.txtProfileID are not part of the JOIN
condition and the WHERE condition.

My challenge is setting the Child/Master links in the main report. The main
report queries the field txtProfileID which needs to be the Master Link
however this leaves me with two Child Links:
tblProfilesRevisions.txtProfileID
tblProfilesRevisionsProfiles.txtProfileID

Are these two values always the same or are they different in the normal
case?

I've tried linking these in the main report however when both are made as
Child Links then the subreport returns null.

This indicates that the values differ from each other.

Can I make both Child Links? If so how can I do this? The solutions
discussed in this particular thread area bit beyond me and I don't think they
necessarily are feasible for my particular challenge.

In a normalized data model these two different values have a different
contextual meaning. Therefore you need a sub report for _each_ value.


mfG
--> stefan <--

 

[JohnLute]

In a normalized data model these two different values have a different
contextual meaning. Therefore you need a sub report for _each_ value.

That's bad news. I previously used two subreports successfully however it
creates a problem in sorting and results in something rather ugly.

Could a Union Query resolve this?