Help Printing a Report with Blank Fields

[Guest]

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

 

[Marc]

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

 

[Guest]

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

 

[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!

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

 

[Marc]

Thanks again Marc! Yes, it would make it easier, however, when I have a

course with a long list of staff attending, it would mean I would have to go
back and find out each staff member's ID and then type that in against the
course title. I want to be able to type the names in as there are about 500
members of staff and I can't possibly remember all their IDs!

 

[Marc]

Thanks again Marc! Yes, it would make it easier, however, when I have a

course with a long list of staff attending, it would mean I would have to go
back and find out each staff member's ID and then type that in against the
course title. I want to be able to type the names in as there are about 500
members of staff and I can't possibly remember all their IDs!

Thanks anyway - I'll keep trying different things until I find a solution!!

Jen

Hi Jen,
I can see your problem, but just hear me out. You're dealing with a many to
many relationship, and it is not the easiest thing to deal with when you're
not used to it.

Table - staff - staffid, first name, last name, ...
Table - course - courseid, coursedescription, ...
Table - coursestaff - courseid, staffid.

Form - frmStaffcbo - create a form with coursestaff as the source, put on it
a combobox to select the staff member.
Form - frmCourse - create a form with course as the source table. Add
frmStaffcbo as a subform.
Test.

In two minutes you'll see you don't have to remember staff ids. And the rest
of the application will also flow much more smoothly.

Marc