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.

[table="width: 500"]
[tr]
[td]Student ID[/td]
[td]Teacher Initials[/td]
[td]Reward Type[/td]
[/tr]
[tr]
[td]213[/td]
[td]BO[/td]
[td]CH[/td]
[/tr]
[tr]
[td]213[/td]
[td]CA[/td]
[td]CH[/td]
[/tr]
[/table]

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.

 

[Joe4]

It sounds to me like you may have a one-to-join relationship between your Pupil_tb and Merit_tb (either that or your JOIN statement is not written correctly so it is creating that illusion).

How many records does this query return, 2 or 4?

SELECT Merit_tb.PupilID, Merit_tb.Type
FROM Pupil_tb INNER JOIN Merit_tb ON Pupil_tb.PupilID = Merit_tb.PupilID
WHERE Merit_tb.PupilID=213;

Note, if PupilID is a text field and not a numeric one, then change the WHERE clause to:

WHERE Merit_tb.PupilID="213";

 

[hblbs]

Thanks for the query, this however returns 4 (i.e. double what should be there). The Pupil ID is stored as a number in both tables.

The doubling up only seems to occur for this particular student ID and not the others. Does access keep any data hidden? I have gone to options to show hidden tables but there do not seem to be any.

Thanks

 

[Joe4]

It is important to understand the nature of your data and how all your tables related to one another.
It sounds like some Pupil ID shows up multiple times in one or both of your tables. Do you see that?
How many times does 213 show up in your Pupil table?
How many times does 213 show up in your Merit table?

 

[hblbs]

Hi Joe4 I think we may be getting there, this database was not created by me and I am very much a novice.
Merit Table - 213 can/should be there multiple times.
Pupil Table - 213 should only be there once, however it is now in there twice.

I import data in to the table using the code below, in theory this code should delete the previous data and insert new data from a CSV file. I have checked the CSV file and 213 is not in there twice, perhaps it is added in the import stage?

Public Sub ImportCSV()
    'This code will import the CSV file(T:\Merits\pupildata.csv)
    'Then run and execute 3 SQL statements
    'First to update any existing recordsf
    'Second to add any new records
    'Last statement deletes any records used in the temporary import table PupilImport_tb
    
    'Store the SQL statements in this variable
    Dim sSQL As String
    
    On Error GoTo ErrorHandler
    
    If MsgBox("Running this action will import new and update existing pupil records with any changes, do you wish to continue?", vbYesNo) = vbYes Then
        'Copy the CSV file to blank temporary table PupilImport_tb
        DoCmd.TransferText acImportDelim, "PupildataSpec", "PupilImport_tb", "T:\Merits\pupildata.csv", 0
        
        'Build the first statement
        sSQL = "UPDATE Pupil_tb INNER JOIN PupilImport_tb " _
        & "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
        & "SET Pupil_tb.TG=PupilImport_tb.TG, " _
        & "Pupil_tb.YG=PupilImport_tb.YG, " _
        & "Pupil_tb.Firstname=PupilImport_tb.Firstname, " _
        & "Pupil_tb.Surname=PupilImport_tb.Surname, " _
        & "Pupil_tb.Gender=PupilImport_tb.Gender, " _
        & "Pupil_tb.Address1=PupilImport_tb.Address1, " _
        & "Pupil_tb.Address2=PupilImport_tb.Address2, " _
        & "Pupil_tb.Address3=PupilImport_tb.Address3, " _
        & "Pupil_tb.Address4=PupilImport_tb.Address4, " _
        & "Pupil_tb.Postcode=PupilImport_tb.Postcode"
        
        'Execute first statement
        CurrentDb.Execute sSQL, dbFailOnError
        
        'Build the second statement
        sSQL = "INSERT INTO Pupil_tb (PupilID,YG,TG,Firstname,Surname,Gender,Address1,Address2,Address3,Address4,Postcode ) " _
        & "SELECT PupilImport_tb.PupilID, PupilImport_tb.YG, PupilImport_tb.TG, PupilImport_tb.Firstname, PupilImport_tb.Surname, PupilImport_tb.Gender, PupilImport_tb.Address1, PupilImport_tb.Address2, PupilImport_tb.Address3, PupilImport_tb.Address4, PupilImport_tb.Postcode FROM PupilImport_tb " _
        & "LEFT JOIN Pupil_tb " _
        & "ON Pupil_tb.PupilID=PupilImport_tb.PupilID " _
        & "WHERE Pupil_tb.PupilID Is Null "
        
        'Execute second statement
        CurrentDb.Execute sSQL, dbFailOnError
        
        'Build the third statement
        sSQL = "DELETE * FROM PupilImport_tb"
        
        'Execute third statement
        CurrentDb.Execute sSQL, dbFailOnError
        
        'Tell the user what just happened
        MsgBox "The file has been imported, changes made and new records added to the Pupil table", vbInformation, "FYI"
    End If
    Exit Sub
    
ErrorHandler:
    MsgBox "There was a problem opening or importing the file (T:\Merits\pupildata.csv) please ensure it exists and is in the right format!"
    Exit Sub

End Sub

 

[Joe4]

Pupil Table - 213 should only be there once, however it is now in there twice.

It sounds like you need to do some data clean-up, as you have some duplicate records in your data table that aren't supposed to be there.

Note, that if each Pupil ID should only show up once in the Pupil table, if you make the Pupil ID field the Primary Key of the table, it will prevent duplicates from ever being added to the table. I would highlight recommend doing this. All your tables should have Primary Keys anyhow (even if they are just Autonumber fields, though if you have a required unique field, you should use that instead of an Autonumber field).

 

[hblbs]

Ok thanks for this, it won't allow me to set the primary key in this table, indexed no duplicates.

I have gone in the table and deleted the additional entry, does the code above offer any insight as to why a duplicate record is being created? Otherwise I will just remember to remove that duplicate ID.

Thanks

 

[Joe4]

Ok thanks for this, it won't allow me to set the primary key in this table, indexed no duplicates.

That could happen for two reasons:
- you still have other duplicates in that table that need to be cleaned up
- you have some blank/null records in that table

If you clean up those two situations, then it should allow you to make that field the Primary Key.

I have gone in the table and deleted the additional entry, does the code above offer any insight as to why a duplicate record is being created?

At quick glance, it looks like it should be OK, but without doing thorough testing, I can't say for sure. It could be that the duplicates were already in there to start with, and this process is not the source of the problem. You could test it out. Clean-up all the duplicates, try importing a new file, and see if there are any duplicates in there after the import.

By the way, here is a little script to check for duplicates:

SELECT PupilID, Count(PupilID) as Pupil_Count
FROM Pupil_tb
GROUP BY PupilID
HAVING Count(PupilID)>1;

This will list all PupilIDs that exist mpre than once in that table, and show you how many times they show up.
You can use this to aid in your clean-up and testing.

 

[hblbs]

It will allow me to set the Pupil ID as primary key now having removed the duplicate/null record, but wont allow me to import using the code above. However upon deleting and re-importing the same data it would appear that there are no more duplicates so I think I will leave this field as it is.

Thank you very much for the extra script to check for duplicates, I will use this periodically to see if there are any problems.

Thanks

 

[Joe4]

but wont allow me to import using the code above

Did you make the Primary Key change to the Pupil_tb table or the PupilImport_tb table? I think you just want to make it to the Pupil_tb table.

Also, are you sure that the error is with the import part and not the part that writes the new records from your Import table to your final table?
If it is trying to import records that would create duplicates, that would make sense that you get errors. That means that there is probably an issue with that part of the code (the second statement) and it was creating duplicate records. We can probably fix that up a bit. Its just probably an issue with the WHERE clause. I may have some time to mess around with that a little later.