Results 1 to 11 of 11
  1. #1
    Join Date
    May 2008
    Posts
    10

    Question Unanswered: Changing table Indexes

    I had to take over a VB program from a person, for a while.
    This program was used to enter Student related stuff, here is a breakdown of the tables

    Table name CourseInfo
    Fields:
    CourseID - Primary key, No dupliactes
    CourseName Duplicates OK

    Table name StudentAndSubjects
    Fields:
    StudentInfo - Primary key, Duplicates OK
    SubjectInfo - Prmary key, Duplicates OK
    SubjectStartDate - Not indexed
    SubjectCompleted - Not indexed
    SubjectEndDate - Not indexed

    Table name StudentInfo
    Fields:
    StudentNo - duplicates OK
    FileNo - No duplicates
    TimeSlot - duplicates OK
    Class - duplicates OK
    StartDate - duplicates OK
    FullNames - duplicates OK
    Surname - duplicates OK
    Course - duplicates OK
    Monthly - duplicates OK
    NoPayments - duplicates OK
    CourseID - duplicates OK
    StuRecNum - Primary key, no duplicates

    Table name SubjectInfo
    Fields:
    PK - Primary key, no duplicates
    CourseID - duplicates ok
    SubjectName - not indexed
    StartDate - duplicates ok
    EndDate - duplicates ok

    These are all the tables. everything works beautifully, and links to where it should, because the program has been running for quite some time.

    The only issue is the fact that it allows duplicate student numbers, but not duplicate FileNumbers, in the StudentInfo table. IMHO, it should not allow either, am I right?

    I tried changing StudnetNo to no duplicates, but Access gave me some kind of error.
    So I set both the FileNo and StudentNo Indexed properties to NO. Access didn't complain.

    So, will this affect all existing data, will I lose any info, is it safe to continue?

    I'd actually be please to have StudentNo as no Duplicates.

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So, will this affect all existing data, will I lose any info, is it safe to continue?
    Yes. Changing index details would have no effect on existing data. However, the front end components may rely up these indices to operate propery... it all depends on how well written it is.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    This is a contradiction in terms - can you confirm this is correct?
    Code:
     StudentInfo - Primary key, Duplicates OK
    SubjectInfo - Prmary key, Duplicates OK
    The tables look excessively indexed (by some margin) but if you are not experiencing problems then no worries.

    Try running:
    Code:
    SELECT StudentNo, COUNT(*)
    FROM StudentInfo
    GROUP BY StudentNo
    HAVING COUNT(*) >1

  4. #4
    Join Date
    May 2008
    Posts
    10
    Quote Originally Posted by pootle flump
    This is a contradiction in terms - can you confirm this is correct?
    Code:
     StudentInfo - Primary key, Duplicates OK
    SubjectInfo - Prmary key, Duplicates OK
    The tables look excessively indexed (by some margin) but if you are not experiencing problems then no worries.
    Yes, that is correct

    Quote Originally Posted by pootle flump
    Try running:
    Code:
    SELECT StudentNo, COUNT(*)
    FROM StudentInfo
    GROUP BY StudentNo
    HAVING COUNT(*) >1
    Tried. It said that the syntax is incorrect and I must include brackets

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I can't remember if Access NEEDS aliases - I thought it would fill them in for you:
    Code:
    SELECT StudentNo, COUNT(*) AS county
    FROM StudentInfo
    GROUP BY StudentNo
    HAVING COUNT(*) >1
    If that fails please post the exact message.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by So(u)rcerer
    Yes, that is correct
    Ah - I understand now. A primary key is (under the hood) enforced using a unique, not-null index. Assuming this is a composite primary key then the index on the first column is a waste of space and can be removed. TBH it looks like the person that built this didn't really know what they were doing and indexed EVERYTHING.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    it looks like the person that built this didn't really know what they were doing
    Ya think
    George
    Home | Blog

  8. #8
    Join Date
    May 2008
    Posts
    10
    I trust and thank all of you guys' instincts and knowledge. Hopefully one day I'll properly understand databases.

    I am attaching the db, so that you can see how all the relationships are set up.

    I just don't want it to bomb out when I enter a duplicate file number ( the reason for this is that this app has been running for years, as I understand, and some of the very old records have the same file number as this year's it seems ).

    I don't know, I'm open to any suggestions, I just don't want to lose any information, else I'll get the beating of my life
    Attached Files Attached Files

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You won't lose any information by removing indices.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    May 2008
    Posts
    10
    Thank you sir, let me change them

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    No worries
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •