Results 1 to 15 of 15
  1. #1
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38

    Single value associated with list of values that must be in primary key

    Hai to all
    I have one table name studentdb having fields like sno,name,age,fName etc
    Another table having name collegedb having fileds like collegename,collegetype etc
    Not I want to create
    Sid is associated with academic year
    Ex hello studied X class in 2013
    Hello studied xi class in 2014
    Here academic year i will give as primary key,
    So far I don't have problem
    But while I entered second student details like
    Ex: xyz studied X calss in 2012
    Xyz studied in xi class in 2013


    Here problem is academic year is primary key
    It's not taking 2013
    So how to solve the problem in this situation
    For each individual student must have list of academic years that should be an primary key
    Please sujject me how to create?

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can a student take more than one class a year? Presuming not, as you example implies, the student and year should be the key values, not the year alone. That is often called a compound key. Another way to go is to have an autonumber key field but an index on those 2 fields, preventing duplicates of the combination.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    It not possible to take more than one class in a year.
    And moreover in stdentdb studentid primary key
    And also academic year also.
    But my doubt is if I entered same academic year to next candidates it not allowed due to primary key
    For individual student academic year must be primary key
    And for second or third student also academic year primary key
    Means 1 St student can study the X class in 2003 then it's not possible to first Student to study some other class in 2003
    But for second student he might be study some any class in 2003. This my criteria.
    I not understand if I applied autonumber how it solve
    Could you tell me clearly

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What does the table look like in design view? Both student and year should be keys.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    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

  6. #6
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    Same way I have created
    And I have created one more table having studentid that I was taken from studentdb as look-up wizard
    And I have one more field that is academic year that was taken from studentclassinyear table
    In both individual table sid and acadetyear primary keys
    While in new table I'm assigning studentid to academic year
    It's working for removal of duplication I was again given primary key to academic year in new table
    Fine it's working but my problem is
    For assigning of second studentid to same academic year it's not possible
    Hope ur understanding my problem...
    i want individual studentid different academic year that should not repeated and another student also have same properties
    But here might chance of two or more students studied in same academic year

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you see

    Quote Originally Posted by pbaldy View Post
    What does the table look like in design view? Both student and year should be keys.
    Or can you attach the db here?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    Here Sid is student ID and acadetyear year is primary key
    For single studentid all academic year values must be unique
    And I'm trying to assiging same academic year to different student it's not allowed due to primary key but I need that one
    Means "every students have unique academic year but different student may have same academic year"

  9. #9
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    every students have unique academic year but different student may have same academic year

  10. #10
    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
    It would be helpful to readers if you could show us your table design.

    Yes, in general, a Class may be offered in different Academic year(s).
    Many Students could take same Class
    Many Students could be in Same Academic year ( and take same or different Classes).

  11. #11
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    Studentdb: Sid(pk),name,dob,fName,pic
    Collegedb: Cid(pk),collegename,colleges,year of establishment
    Academic: academic year(pk),class
    Then I created one more table that has following field
    Sid(taken from studentdb as look-up), academic year(taken from academic table), class( taken from academic table), Cid( from college db)

    These above tables I have

  12. #12
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    Here I'm not getting to send my file
    Could you send it mail id I'll send my access file

  13. #13
    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
    Academic: academic year(pk),class
    This structure only allows for 1 class per year.

    You need a structure that allows for:

    The college will be open to students for 1 or many Academic years
    1 or More Classes will be taught in 1 or Many Academic years (Many to Many)
    In any Academic year 1 or Many Classes will be available
    In any Academic year 1 or Many Students will study 1 or many Classes

    Here is an example:
    Click image for larger version. 

Name:	StudentsStudyManyClassesinManyYears.PNG 
Views:	15 
Size:	26.8 KB 
ID:	36692

    I would not advise using lookups at table field level.

  14. #14
    TskcAccessdev is offline Advanced Beginner
    Windows 7 64bit Access 2007
    Join Date
    Oct 2017
    Posts
    38
    In above example yin academic repeated thrice
    But my requirement is its should unique
    Other student may have same academic year
    But individual student academic year should not be repeated
    This my criteria

  15. #15
    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
    ???
    Here is a query--- InWhatYearsDid_YIN_Study.
    Click image for larger version. 

Name:	QryInWhatYearsDid_Yin_Study.PNG 
Views:	12 
Size:	30.2 KB 
ID:	36698

    With this result
    Click image for larger version. 

Name:	StudentYINStudied.PNG 
Views:	13 
Size:	9.9 KB 
ID:	36700
    This is a SELECT DISTINCT query to only show unique records.

    Here is the sql behind the query graphic.

    Code:
    SELECT DISTINCT Student.StudentID, Student.StudentFName, Student.StudentLName, AcademicYear.OtherAcademicYearInfo
    FROM Student INNER JOIN (StudentTookClassInYear INNER JOIN AcademicYear ON StudentTookClassInYear.AcademicYearID = AcademicYear.AcademicYearID) ON 
    Student.StudentID = StudentTookClassInYear.StudentID
    WHERE (((Student.StudentFName)=[Enter Student FirstName]));

    You will note that "Yin" attended for 1 semester/academic year.



    In post #13, the display is for all students, all classes they studied.

    Since Yin took 3 courses in 2-Fall 2007, the display shows values for the selected fields. The details are displayed for Yin's 3 records.

    Good luck with your project.

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

Similar Threads

  1. Replies: 9
    Last Post: 03-08-2016, 03:25 AM
  2. Single Primary Key for Multiple Tables?
    By lwflip in forum Database Design
    Replies: 2
    Last Post: 07-14-2015, 07:59 AM
  3. Replies: 1
    Last Post: 09-11-2014, 05:53 AM
  4. Replies: 4
    Last Post: 05-07-2012, 12:08 PM
  5. Table with list of values - primary key filed
    By snoopy2003 in forum Database Design
    Replies: 1
    Last Post: 02-23-2011, 09:29 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