Results 1 to 8 of 8
  1. #1
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051

    Trying to delete "repeated" data

    Through bad planning (in the database where I export data from), i get the following:

    StudentID_PK Firstname Surname UPN YearGp MathsClass MathsTeacher
    702 John Smith abcdefg 10 10xy/Mm2
    703 John Smith abcdefg 10 10x/Mm3

    Unfortunately I only need the second one.

    Can anyone suggest ways to do this.
    The data is in a CSV file. Access imports it from there into a table.

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Not enough info.
    How did the 702, 703 get assigned? Sounds like a normalization issue??
    The record deals with a Student/MathClass/UPN/Year and Teacher (object/construct).

    Here is a free video by Dr. Jennifer Widom that overviews normalization and database design.

    Be aware, she is very quick ---but you can pause, rewind, repeat..

    Good luck.

  3. #3
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    Sorry the 702 and 703 are in the table when the data is imported.

    (the data from the CSV file doesn't include these values)

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Readers need to know more about your set up.
    -what the data represents
    -where it comes from
    -why do you need 703 and not 702?? what are the facts surrounding this??

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    See "Delete Duplicate Records" http://allenbrowne.com/subquery-01.html

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    aytee why do you think these are duplicates?
    They have different values for both
    studentID_PK and MathsClass

    So what we are being shown are NOT duplicates. There's more to the story than what has been posted.

  7. #7
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Right. It is the name that is duplicated, Andy will need more criteria.

  8. #8
    andy49's Avatar
    andy49 is offline VIP
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2016
    Location
    London
    Posts
    1,051
    They aren't really duplicates in the database sense

    Some idiot gave them separate class codes on the school's main Sims database (which I have no control over)


    The Sims database runs a report daily to update my database with Firstname, Lastname, UPN (a special unique number to each student), Maths group and the empty column mathsteacher

    this is stored as a CSV file at about 3am and I update my tblStudent on my access DB with it daily.

    So one of John Smiths classes is a Maths class. The shorter one!!

    I eventually cracked it with this thanks to Aytee111's advice and link

    Code:
    SELECT tblStudent.*
    FROM tblStudent
    WHERE (((Len([tblStudent].[MathsClass]))<>(SELECT min(Len(Mathsclass)) AS Mathsgp FROM tblStudent AS Dupe      
        WHERE (Dupe.Surname =  tblStudent.Surname)                         
        AND (Dupe.FirstName =  tblStudent.FirstName))));
    Thanks

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-13-2016, 09:48 AM
  2. Replies: 13
    Last Post: 12-12-2016, 12:26 AM
  3. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  4. Unbound text box data type and "Delete" function
    By gaker10 in forum Programming
    Replies: 16
    Last Post: 06-13-2014, 10:46 AM
  5. Replies: 13
    Last Post: 05-06-2014, 12:42 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums