Results 1 to 2 of 2
  1. #1
    access is offline Novice
    Windows XP Access 2007
    Join Date
    Jun 2009

    Query to get column names in a table


    I have a table "Student" with fields and entries as below

    StudentName Year Class Subj1 Subj2 Subj3

    I want to write a query to display the list of entries as below

    StudentName Year Class Course Marks
    ... ... .... Subj1 <Value in Subj1 record>
    ... ... .... Subj2 <Value in Subj2 record>
    ... ... .... Subj3 <Value in Subj3 record>

    ie. I want to get the column names from the table. Can any one help me out in this regard. Thanks.

  2. #2
    kevsparky is offline Novice
    Windows XP Access 2003
    Join Date
    Jul 2009
    Your table doesn't have the correct structure. To do what your asking, without any complex coding, you'll need to create 2 tables, one for Students and One for Subjects & Marks, and create a relationship between them. Below is the table structure you need.

    TABLE: Students
    Field: StudentID Datatype: AutoNumber (Make primary key)
    Field: StudentName Datatype: Text
    Field: Year Datatype: Text
    Field: Class Datatype: Text

    TABLE: Subject
    Field: StudentID Datatype: Number
    Field: SubjectName Datatype: Text
    Field: Mark Datatype: Number
    The create a relationship between the 2 tables by going to Tools > Relationships; Add the Student Table and the Subject Table. Click and drag the Field "StudentID" from Subject Table onto Student Table (this is crucial, don't do it the other way) and Enforce Referential Integrity.

    This type of relationship will let you create a custom number of subjects for a given student, and it will let you sort by subject names, classes and years using the Access Wizards.

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

Similar Threads

  1. Query to get column names in a table
    By access in forum Forms
    Replies: 1
    Last Post: 07-01-2009, 02:50 PM
  2. Replies: 5
    Last Post: 03-31-2009, 09:16 PM
  3. Replies: 3
    Last Post: 02-20-2009, 02:28 PM
  4. Adding new column to make-table query
    By dtn118 in forum Access
    Replies: 2
    Last Post: 08-03-2008, 06:51 AM
  5. inserting values in column based another column
    By wasim_sono in forum Database Design
    Replies: 1
    Last Post: 06-27-2006, 05:23 AM

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