Question:

I’m trying to determine why my database’s Report combines information on students who have the same last name. Please see the attached database to follow along.

1. The db has three tables: all students; classroom “modifications” teachers can use to help each student succeed; and a record of which modifications are used for each student.

2. A Query searches all the modifications used for each student.

3. A Report collects and presents data from the Query.

All is fine as long as students have unique last names. But when two or more students share a last name, the Report shows the name of just one of those students AND combines all the modifications of all the students who have that last name.

For example, see how the Report displays the students with last name Ball (3 students) and Smith (2 students). The report shows only one first name for all the Ball kids and one for the Smith kids, and it lumps together all the modifications for the Ball kids and also the Smith kids. Yet the report correctly shows the modifications of students who have different last names.

Where did I go wrong? And, please, I need specific how-to-for-dummies instructions on how to fix this. I am less than a novice.

Solution:

Hi sfschool,

Hmmm… close…..trying not to get too complicated but…

1. Basically you are correct, the error in the report was a flow-through effect caused by the error in the query.

2. No, if you are using the Wizard to create your report, grouping defaults will be determined by the primary key of the table selected to ‘view by’, (if Access can resolve this from your query).

3. Well, if no grouping fields are stipulated in the Wizard then yes, you will get different results depending on the query and underlaying table structure (and their relationships).

Also, don’t forget you can set grouping levels manually in the Wizard and the report design view, which adds a lot of flexibility with your queries.

For example, you could have solved your problem by grouping on LastName and then FirstName as in rpt2 in the example attached. This is based on your original query, but is not a recommended solution as it is possible for two people to have the same first and last names (which will mean you will have the grouping problem again).

Hope this helps…

Modifications.mdb (2.7 MB) (File Type Details)
report example (rpt2)