 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / New Users / April 2004Help Printing a Report with Blank Fields
I hope someone can help me please I set up a report from two tables - "Staff Table" contains details of all staff within the company. "Courses Table" contains staff names plus all courses they have completed Each table contains staff's first name and surname in two separate fields. I have linked these tables using these two fields in order to create a form and a subform I have now created a report with all this information on. However, the report only shows those staff who have completed courses, and not those who have not yet completed a course I want the report to show ALL staff and the courses they have completed (if any). Therefore, how do I get the report to also show staff who have no data in the Course Completed field Many thanks for any help Jen > I hope someone can help me please! > > I set up a report from two tables - "Staff Table" contains details of all staff within the company. "Courses Table" contains staff names plus all courses they have completed. > Each table contains staff's first name and surname in two separate fields. I have linked these tables using these two fields in order to create a form and a subform. > I have now created a report with all this information on. However, the report only shows those staff who have completed courses, and not those who have not yet completed a course. > I want the report to show ALL staff and the courses they have completed (if any). Therefore, how do I get the report to also show staff who have no data in the Course Completed field? > Many thanks for any help! > > Jen Hi In your query window, look at the way the tables are joined. Click (or is it double click?) on the relationship line. It will bring up a dialog with three options - you want the option that says all records on the staff table and any on the course table. Marc Thanks Marc - I've tried that, but Access won't let me run the query that way - I get an error message saying "The SQL statement could not be executed because it ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement. I have no idea what any of this means! The two tables are joined by TWO fields - first name and surname, because several members of staff have the same surname Any ideas? - thanks Jen > Thanks Marc - I've tried that, but Access won't let me run the query that way - I get an error message saying: > > "The SQL statement could not be executed because it ambiguous outer joins. To force one of the joins to be performed first, create a separate query that performs the first join and then include that query in your SQL statement." > I have no idea what any of this means! The two tables are joined by TWO fields - first name and surname, because several members of staff have the same surname. > Any ideas? - thanks! Hi That is why it is better to have an autonumber on the staff table called StaffId, and a reference long number field staffId on the course table. then the join is one field to one field and all is simpler. It is no problem to always show the names instead of the id fields and it makes it easier to manage in Access. Marc |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.