none
Simple Excel Mark Sheet in Access RRS feed

  • Question

  • Hi,

    I have a simple mark sheet with multiple students having different subjects/courses. This is in Excel

    What is the simplest way to convery it into an access form and then try to track the students and their marks.

    Am not very advanced in macros/sql so please bear with me. Is this the right place to ask this question?

    Thanks in advance for all your help.


    Sunday, November 17, 2019 10:24 AM

All replies

  • I don't believe you can create and use and ACCESS form to link to and edit an EXCEL worksheet. You can certainly import EXCEL data into an ACCESS table and then create a form to edit that table data. You can also create a linked EXCEL worksheet table within ACCESS but you could not change the EXCEL worksheet within ACCESS. Your best method is to import the EXCEL data into a new ACCESS table and then create a form. The EXCEL worksheet will no longer be used at all. It does not take any macros or VBA or SQL knowledge to do this. That would come later if you want to automate any actions within ACCESS.
    Sunday, November 17, 2019 4:22 PM
  • When importing data from Excel to Access the aim should be to insert the data into a set of correctly normalized tables.  In your case you would require Students and Courses as the two referenced tables, with a further table, StudentCourses say, to model the many-to-many relationship type between Students and Courses by resolving the relationship type into two one-to-many relationship types.  A student's mark in each subject is an attribute of the relationship type, and consequently a column in the StudentCourses table.

    You'll find an illustration of how data can be imported from Excel and then decomposed into a set of related tables in DecomposerDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    In this little demo file data is inserted into each table by means of an 'append' query.  The rule of thumb is that rows are inserted into the referenced table in each relationship before rows are inserted into the referencing table.  In your case this would mean inserting rows into Students and Courses before inserting rows into StudentCourses.  This is analogous to the insertion of rows into Contacts and Employers in my demo, before inserting rows into ContactEmployers.

    Ken Sheridan, Stafford, England

    Sunday, November 17, 2019 5:43 PM