Subreport Update Issues - 2 Child Fields 1 Master Field

[tsawner]

Been trying to work my way around this but just can't seem to get it
done.

It involves 1 Master Field and 2 Child Fields (which access can't seem
to deal with) so Im trying to go through VBA.

Simplified version of what im dealing with and my problem. There are
more parts to it, but I don't have any issues with those.

Report with 1 subreport. Main Report lists people by name. Subreport
Lists the responsiblities of the person in the main report. The tricky
part is that often 2 people are responsible for the same issue, the
subreport has two slots (person1, person2) to account for this. The
person listed in the main report could be in either slot. I need both
people on the list, rather than 2 records, because they are supposed to
work together to solve the issue.

Report Layout
--------------------------------------------------------------------------------
Person A
Issue-ID Person1 Person2 Issue 1
--------------------------------------------------------------------------------





Basically, I want it to come out like this.

--------------------------------------------------------------------------------
John Doe
1 John Doe Do XYZ
2 John Doe Tom Smith Do XYZ
3 Tom Smith John Doe Do XYZ

Tom Smith
2 John Doe Tom Smith Do XYZ
3 Tom Smith John Doe Do XYZ
4 Tom Smith Do XYZ

----------------------------------------------------------------------------------





What Im Winding Up With... (All records end up showing on both)

--------------------------------------------------------------------------------
John Doe
1 John Doe Do XYZ
2 John Doe Tom Smith Do XYZ
3 Tom Smith John Doe Do XYZ
4 Tom Smith Do XYZ

Tom Smith
1 John Doe Do XYZ
2 John Doe Tom Smith Do XYZ
3 Tom Smith John Doe Do XYZ
4 Tom Smith Do XYZ

----------------------------------------------------------------------------------


Originally I tried to use Access's Child/Master linking, but that
didn't work unless I used two subreports - Subreport 1 links Person 1
and Subreport 2 links Person 2 - but then you are limited by how you
sort.

I've been working on doing it through VBA in the Subreports Report_Open
in attempt to requery the subreport each time it is opened. The
criteria for the main report is determined through a series of
Multi-Select List Boxes. So I have passed that down to the subreport
and my SQL code looks like this. My problem is that the subreport
doesn't seem to update between occurences

Open Issues is the table that holds all of the subreport Data.
PersonName is the control in the main report that would normally be the
Master field.

From subreport Report_Open

----------------------------------------------------------------------------------------------------------------------------

stringPerson = "(OpenIssues.Person1 = """ & Me.Parent!PersonName.Value
& """ OR OpenIssues.Person2 = """ & Me.Parent!PersonName.Value & """)"

stringSQL = "SELECT DISTINCTROW OpenIssues.* " & _
"FROM OpenIssues " & _
"WHERE " & stringPerson & _
" ORDER BY OpenIssues-ID;"

Set db = CurrentDb
Set qdf = db.QueryDefs("queryOpenIssues")
qdf.SQL = stringSQL
Set db = Nothing
Set qdf = Nothing

Me.RecordSource = "queryOpenIssues"

-----------------------------------------------------------------------------------------------------------------------------

 

[Duane Hookom]

Without reading through your entire post (I'm too lazy), I think you can
just create a UNION query to normalize your data to create two records
rather than one record with Person1 and Person2. This would make the
subreporting behave like regular subreports.

If you can't figure out how to create the union query then come back with
your significant tables and fields.

 

[tsawner]

Thanks for the suggestion, I will try it out tomorrow.

To clarify... you are suggesting the following

SELECT table.*
FROM table
WHERE Person1
UNION
SELECT table.*
FROM table
WHERE Person2


Is there any issue around Unioning a table to itself?

 

[Duane Hookom]

Try something like

SELECT Person1 as Person, Person2 as OtherPerson, Table.*
FROM table
WHERE Person1 Is Not Null
UNION ALL
SELECT Person2, Person1, table.*
FROM table
WHERE Person2 Is Not Null;

 

[tsawner]

I solved my issue. Similar to how you suggested but with a difference.
I used the UNION like you said, but put Person1 and Person2 into the
same field called "ChildLink"

Then on the report design I changed the Master/Child Links and just
didn't display the ChildLink field on the subreport. It worked out
nicely, then I just used the subreports Sorting/Grouping to get it to
display in the order I wanted.


Basic SQL i used is below...

---------------------------------------
SELECT Person1 as ChildLink, Table.*
FROM Table
WHERE Person1 Is (My Criteria)
UNION ALL
SELECT Person2 as ChildLink, Table.*
FROM table
WHERE Person2 Is (My Criteria);
----------------------------------

Master: Person
Child: ChildLink






Using my example from before....

---------------------------------------------------------------------------­-----

Person A
Issue-ID Person1 Person2 Issue 1
---------------------------------------------------------------------------­-----


Table Has
---------------------------------------------------------------------------­-----

Issue-ID Person1 Person2 Issue 1
1 John Doe Do XYZ
2 John Doe Tom Smith Do XYZ
3 Tom Smith John Doe Do XYZ
4 Tom Smith Do XYZ
---------------------------------------------------------------------------­-----



Query winds up with
--------------------------------------------------------------------------------
ChildLink Issue-ID Person1 Person2 Issue 1
John Doe 1 John Doe Do XYZ
John Doe 2 John Doe Tom Smith Do XYZ
Tom Smith 3 Tom Smith John Doe Do XYZ
Tom Smith 4 Tom Smith Do XYZ
Tom Smith 2 John Doe Tom Smith Do XYZ
John Doe 3 Tom Smith John Doe Do XYZ