Solved Getting a list in My report

[Teri Bridges]

I have a report for Courses. This report has several sub reports, one of which is roles to course.

In my case, the user assigns many roles to one course. This information is posted to my "role to course" table.

I am trying to bring that list into my report.

For example, OTC101 has 2 roles assigned CRM102 has one role assigned.

When I try to bring in the roles, I get a list for all the roles assigned or i get a list with just one role assigned.

I appreciate the help.

 

[theDBguy]

Without seeing your db/report in action, I'm not sure what to recommend. Are you able to share a sample copy of your db with test data.

 

[Teri Bridges]

Without seeing your db/report in action, I'm not sure what to recommend. Are you able to share a sample copy of your db with test data.

Yes I sure can

 

[June7]

The posted report does not have any subreports. Listboxes won't work nicely in a report - won't grow/shrink.

I see circular referencing in Relationships window. This can certainly cause confusion when building query for report. Fortunately, you did not keep circular links in report query. However, not clear to me yet how these tables should be linked in query. Quite possible you are attempting to put too much into one query.

CourseRoles is junction table for Course and ListRoles yet you only link ListRoles to it. You are linking ListRoles to Course via ListModule. Why even include CourseRoles? The only field pulled from it is RoleID.

Users really should not need to see ID fields.

 

[Teri Bridges]

The posted report does not have any subreports. Listboxes won't work nicely in a report - won't grow/shrink.

I see circular referencing in Relationships window. This can certainly cause confusion when building query for report. Fortunately, you did not keep circular links in report query. However, not clear to me yet how these tables should be linked in query. Quite possible you are attempting to put too much into one query.

CourseRoles is junction table for Course and ListRoles yet you only link ListRoles to it. You are linking ListRoles to Course via ListModule. Why even include CourseRoles? The only field pulled from it is RoleID.

Users really should not need to see ID fields.

What I want to see is the names of the roles assigned to the course. But I have to link the tables by ID.

 

[Teri Bridges]

The posted report does not have any subreports. Listboxes won't work nicely in a report - won't grow/shrink.

I see circular referencing in Relationships window. This can certainly cause confusion when building query for report. Fortunately, you did not keep circular links in report query. However, not clear to me yet how these tables should be linked in query. Quite possible you are attempting to put too much into one query.

CourseRoles is junction table for Course and ListRoles yet you only link ListRoles to it. You are linking ListRoles to Course via ListModule. Why even include CourseRoles? The only field pulled from it is RoleID.

Users really should not need to see ID fields.

Could you be more specific on circular referencing? I am still learning and am not sure i understand the concept.

 

[June7]

Review https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design

I suspect you need to break up this query and use subreports and/or VBA to build strings of data for display in textbox http://allenbrowne.com/func-concat.html

 

[Teri Bridges]

Review https://www.codeproject.com/articles/38655/prevent-circular-references-in-database-design

I suspect you need to break up this query and use subreports and/or VBA to build strings of data for display in textbox http://allenbrowne.com/func-concat.html

I am using sub reports to enter the data. The vba on the other hand is new tom. I will have to do a lot more research on theat. Thank you for the help.

 

[June7]

You don't use subreports to enter data - you use subforms.

 

[Teri Bridges]

You don't use subreports to enter data - you use subforms.

You are correct, I mis-typed there. I am using a sub-form to select the role from a cbobox. Sorry for any confusion.

 

[mike60smart]

You are correct, I mis-typed there. I am using a sub-form to select the role from a cbobox. Sorry for any confusion.

Teri
See the modified report in the attached.

You cannot include every table as the Record Source

I added a subreport for the Roles

You need to do the same for all other sections of the Report.

 

[Teri Bridges]

Teri
See the modified report in the attached.

You cannot include every table as the Record Source

I added a subreport for the Roles

You need to do the same for all other sections of the Report.

Thank you Mike, most helpful.