Parents with no Child - Reports from multiple tables

[pmcg1]

Hi,
I have a MS access database with (10) mutiple tables with a form for main data entry. The purpose is to track employee's publications, films, exhibitions. The IDTable is the maintable that makes the relationship - one to many to all the others by a field called Faculty_ID.

The problem: When I run a report I do not know how to control the data. I would to be able to pull all fields from all the tables into one report. The issue is only some faculty have Books and some others have films... ect. Not all the records in the ID table have related records in the BooksTable, ExhibitionsTable... so I am getting a lot of BLANKS.

I do not know SQL. Please help.

 

[dhookom]

How do you enter the data in the related tables? Do you use subforms? If so, a similar solution would be used for your report. You would use a subreport for each of the related tables.

Duane
Hook'D on Access
MS Access MVP

 

[pmcg1]

Yes! I do use a subform to enter the data and I have gotten that far with the subreports except the problem is when the reports run ... for example if a person has a book record it will show that record perfectly, if they do not have a exhibition record the labels will appear for the table fields with empty spaces. I think (and I could be wrong here) I need to learn how to control these field properties so "if" the ExhibitionTitle field is empty the ExhibtionsTable is skipped but only for that FacultyID. I am aware of "if" and "where" clauses but I do not know how to write them, where to put them and how powerful they can be. See link for database, there is no confidential info here.

 

[dhookom]

Did you create separate subreports for each related table? A subreport with no records will not display in the main report.

Duane
Hook'D on Access
MS Access MVP

 

[pmcg1]

Hi Duane,
I created subreports for each table like you said. I am still seeing blank records on the main report. The subreports however are not showing any blank records. Am I supposed to be dragging the subreports into the detail area of the main report, cause that's how I am doing it - I get the same results using the wizard. Then linking the Parent/child.
I have tried various ways and experimenting but (changing the parent/child) whenever I stray too far from direction I get the subreports appearing for the correct records but I also get duplicate records within those subreports!! It is one extreme to another.
I am attaching a link to the most recent main report with all the subreports embedded -- Faculty by Department.
Thanks for your help,
Patty

 

[dhookom]

still seeing blank records

How can you see "blank records"? If there are no records in a subreport, it won't display.

What is the record source of your main report? What are the fields entered into the Link Master/Child properties? Your main report should not contain the detail tables from your subreports.

I can't open you Access file since I don't have Access 2007 installed on this PC.

Duane
Hook'D on Access
MS Access MVP