MS Access 07 Count query duplication

[hblbs]

Hi all

I have a query that is mean to count the number of students who have attained a reward and total them up based on student ID. My table is as follows Student ID, Teacher Initials and Reward type. Rows created based on rewards for each student.

Student ID	Teacher Initials	Reward Type
213	BO	CH
213	CA	CH

The trouble I am having is that for one particular student (213) the total for the reward types are being doubled up ( CH should be 2 but is 4). Im not sure what is causing this, the original the table is fine however when I join the table with student address details based on Student ID to link address details the total just for this one student doubles up. Is there something in MS Access that stores hidden data that needs to be removed? Or is the query wrong? The SQL code is below if anyone could take a look, I am a novice with Access so can post image of the query in design view if needed.

SELECT Merit_tb.PupilID, Merit_tb.Type, Count(Merit_tb.Type) AS CountOfType
FROM Pupil_tb INNER JOIN Merit_tb ON Pupil_tb.PupilID = Merit_tb.PupilID
GROUP BY Merit_tb.PupilID, Merit_tb.Type;

Thanks in advance for any help.

 

[hblbs]

Many thanks.

The Primary Key change was to the Pupil_tb table, the PupilImport_tb table didn't have a Primary Key however the Pupil ID was Indexed = Yes (No Duplicates). I'll change these around and see if that helps.

You may be right about the stage that writes to the final table, though I could never be sure as I don't understand most of it.

Thanks

 

[Joe4]

I just reviewed how a Unmatched Query that you use to Append records should look like, and your SQL code for that second statement looks valid.

If you still have problems after making those other changes, let me know. We can add some Message Boxes in your code to see if we can find out where the issue may be occurring.