Show sub report whether data exists in master or not!

[proximity]

Hi,

Hope someone can help me with this little puzzle. Tried all sorts of variants on HasData and what have you, all to no avail. Anyway, here is the problem:

I have a report with an embedded subreport with the correct master/child relationship.

Now, if there is data in the main source and there's data in the sub source all is sweetness and light. If there is no data in the main source, but data in the subreport, nothing gets displayed. And vice versa.

Main data is displayed in GroupHeader0 and the subreport data is displayed in the Report Footer.

How can I rectify this so that whatever happens, data is displayed thus:

No master data, display sub
No sub data, display master

Hope the above makes sense!

Thanks.

 

[MajP]

1. No sub data, display master
This tells me that you have some mistake in your main reports recordsource because this is not normal behavior. My guess is that you have an inner join in your recordsource that returns only the parent records with child records. Since you are using subreports you do not need to include the child records in the main forms recordsource (or use a outer join to return all parent records)

2. No master data, display sub
How would you display this. You now have

Parent 1
Child
Child
Child
Parent 2
Child
Child

Now you have some orphans. Child records without parents. Where would they go? You could add a footer and put in another subreport. The recordsource would be all orphan records. Use the query wizard to return all unmatched records. Or you could get tricky. I assume your orphans have a null foreign key to the parent table. You could then do a Union query to the main form recordsource where you add a record with a null primary key. Something like

SELECT Employees.EmployeeID, Employees.LastName
FROM Employees UNION SELECT Null as EmployeeID, "Not Assigned" as LastName from Employees

This gives me data that looks like

EmployeeID LastName
Not Assigned
1 Davolio
2 Fuller
3 Leverling
4 Peacock
5 Buchanan
6 Suyama
7 King
8 Callahan
9 Dodsworth
10 Davolino

So any child that has a null EmployeeID foreign key would fall under Not Assigned