If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help 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 |
#2
|
|||
|
|||
Help Printing a Report with Blank Fields
"Jen" wrote in message ... 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 |
#3
|
|||
|
|||
Help Printing a Report with Blank Fields
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 |
#4
|
|||
|
|||
Help Printing a Report with Blank Fields
"Jen" wrote in message news 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 |
#5
|
|||
|
|||
Help Printing a Report with Blank Fields
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 |
#6
|
|||
|
|||
Help Printing a Report with Blank Fields
"Jen" wrote in message ... 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 |
#7
|
|||
|
|||
Help Printing a Report with Blank Fields
"Jen" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|