If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DataBase Structure and Excel Imports
Self taught Access and VBA would appreciate any guidance.
I have two tables tblStudents, tblClass that are causing me difficulties. I can see the problem with my approach but am not sure how to proceed. tblstudents has field studentID,YearID,RollClassID as well as other fields containing information about the student. tblClass has fields RollClassID (an autoNumber), RollClass, SchoolYear, TeacherID. Within the data base a user can add a student through forms where the RollClassID is a lookup based on RollClassID and RollClass in tblClass. A user can also add new rollclasses each year through user friendly forms. Any connections between other forms use master child links based on the RollClassID as a roll class may have the same name from one school year to the next . All seems good. But whats spinning my head out is that I also want to enable users to do a mass import of Roll classes and students from excel sheets. The Roll class xl sheet contains RollClass & Teacher and Access is good enough to add the autonumbers but the Student XL sheet only contains the RollClass not the RollClassID (which really only Access Knows as it is an autonumber) Should I be constructing a primary key using year and RollClass Name for the tblClass? At present the StudentXL sheet makes no reference to what school year it is so I am concerned about making sure I end up cinstructing the correct RollClassID. I can manipulate the xl sheets via VBA if need be. Also school years do line up with calender years as I live in Australia. I know I have got it wrong just not sure what direction to head |
#2
|
|||
|
|||
DataBase Structure and Excel Imports
Sandy
If you know/understand/use Excel and are trying to start using Access, I'm sorry! Many of the techniques and tricks you've learned will have to be unlearned! It all starts with the data for Access. For instance, I'm not quite clear on what, in your situation, you mean by "RollClass". I believe I understand that you can import data from Excel into Access. However, you are NOT limited to keeping data in Access in the same structure that it was in Excel (and it is often a very good idea NOT to). You can still import the (raw) data from Excel, but you'll very often get better use of Access' relationally-oriented features/functions if you then "parse" that raw data into more permanent, well-normalized tables. More info, please... -- Regards Jeff Boyce Microsoft Access MVP Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "Sandy" wrote in message ... Self taught Access and VBA would appreciate any guidance. I have two tables tblStudents, tblClass that are causing me difficulties. I can see the problem with my approach but am not sure how to proceed. tblstudents has field studentID,YearID,RollClassID as well as other fields containing information about the student. tblClass has fields RollClassID (an autoNumber), RollClass, SchoolYear, TeacherID. Within the data base a user can add a student through forms where the RollClassID is a lookup based on RollClassID and RollClass in tblClass. A user can also add new rollclasses each year through user friendly forms. Any connections between other forms use master child links based on the RollClassID as a roll class may have the same name from one school year to the next . All seems good. But whats spinning my head out is that I also want to enable users to do a mass import of Roll classes and students from excel sheets. The Roll class xl sheet contains RollClass & Teacher and Access is good enough to add the autonumbers but the Student XL sheet only contains the RollClass not the RollClassID (which really only Access Knows as it is an autonumber) Should I be constructing a primary key using year and RollClass Name for the tblClass? At present the StudentXL sheet makes no reference to what school year it is so I am concerned about making sure I end up cinstructing the correct RollClassID. I can manipulate the xl sheets via VBA if need be. Also school years do line up with calender years as I live in Australia. I know I have got it wrong just not sure what direction to head |
#3
|
|||
|
|||
DataBase Structure and Excel Imports
On Sat, 20 Mar 2010 03:10:04 -0700, Sandy
wrote: You never want to adjust your database structure because of an import. If you don't have the RollClassID value, why not look it up using a query? So I would attach (not import) this Excel file. It now is a table. I would create a query qryClassCurrentYear which selects all fields from tblClass where the Year is the current year. Then I would create an Append query on this table and qryClassCurrentYear. You can now pick up RollClassID from this query, and other fields from the Excel table, and append the rows to the Students table. -Tom. Microsoft Access MVP Self taught Access and VBA would appreciate any guidance. I have two tables tblStudents, tblClass that are causing me difficulties. I can see the problem with my approach but am not sure how to proceed. tblstudents has field studentID,YearID,RollClassID as well as other fields containing information about the student. tblClass has fields RollClassID (an autoNumber), RollClass, SchoolYear, TeacherID. Within the data base a user can add a student through forms where the RollClassID is a lookup based on RollClassID and RollClass in tblClass. A user can also add new rollclasses each year through user friendly forms. Any connections between other forms use master child links based on the RollClassID as a roll class may have the same name from one school year to the next . All seems good. But whats spinning my head out is that I also want to enable users to do a mass import of Roll classes and students from excel sheets. The Roll class xl sheet contains RollClass & Teacher and Access is good enough to add the autonumbers but the Student XL sheet only contains the RollClass not the RollClassID (which really only Access Knows as it is an autonumber) Should I be constructing a primary key using year and RollClass Name for the tblClass? At present the StudentXL sheet makes no reference to what school year it is so I am concerned about making sure I end up cinstructing the correct RollClassID. I can manipulate the xl sheets via VBA if need be. Also school years do line up with calender years as I live in Australia. I know I have got it wrong just not sure what direction to head |
#4
|
|||
|
|||
DataBase Structure and Excel Imports
"Sandy" wrote in message ... Self taught Access and VBA would appreciate any guidance. I have two tables tblStudents, tblClass that are causing me difficulties. I can see the problem with my approach but am not sure how to proceed. tblstudents has field studentID,YearID,RollClassID as well as other fields containing information about the student. tblClass has fields RollClassID (an autoNumber), RollClass, SchoolYear, TeacherID. Within the data base a user can add a student through forms where the RollClassID is a lookup based on RollClassID and RollClass in tblClass. A user can also add new rollclasses each year through user friendly forms. Any connections between other forms use master child links based on the RollClassID as a roll class may have the same name from one school year to the next . All seems good. But whats spinning my head out is that I also want to enable users to do a mass import of Roll classes and students from excel sheets. The Roll class xl sheet contains RollClass & Teacher and Access is good enough to add the autonumbers but the Student XL sheet only contains the RollClass not the RollClassID (which really only Access Knows as it is an autonumber) Should I be constructing a primary key using year and RollClass Name for the tblClass? At present the StudentXL sheet makes no reference to what school year it is so I am concerned about making sure I end up cinstructing the correct RollClassID. I can manipulate the xl sheets via VBA if need be. Also school years do line up with calender years as I live in Australia. I know I have got it wrong just not sure what direction to head Not sure I understand your table structure from the description, but the standard approach to student-class modeling requires at least 3-4 tables: Students (studentID, nameLast, nameFirst, dateOfBirth, etc.)- a person who can take a class. Courses (courseID, courseTitle, creditHours, etc.)- an instructional offering which can be taught many times per semester, like Algebra 1 or English 3. ClassOfferings (courseID, year, semester, section, etc.)- a particular offering of a course, to which students can be assigned. Enrollments (courseID, year, semester, section, studentID, finalGrade, etc.)- a student taking a class. As far as importing your data, it can be difficult to match course names in Excel to your data, since any typo causes a problem. I usually import data like that into a temporary staging table in Access, and then use queries to identify any bad data for correction. Once the data is clean, you can use queries to append the data from the staging tables to your live tables, and then delete the staged data. |
#5
|
|||
|
|||
DataBase Structure and Excel Imports
Beleive it or not Jeff I know more about access than I do excel which in the
scheme of things is probably not very much- I just didn't explain things very clearly but I think Tom and Peter got the gist of my problem. I was on the right track in that I was doing some checking on the spreadsheet and then bringing it into Access as a temporary table from which I did my updates and appends. Tom and Peter have got me thinking about doing further checks on the temp table data using queries and particularly the qryCurrentClass using current year to pull the RollClassID . I have had success with that today so thanks I greatly appreciate your advise You never want to adjust your database structure because of an import. If you don't have the RollClassID value, why not look it up using a query? So I would attach (not import) this Excel file. It now is a table. I would create a query qryClassCurrentYear which selects all fields from tblClass where the Year is the current year. Then I would create an Append query on this table and qryClassCurrentYear. You can now pick up RollClassID from this query, and other fields from the Excel table, and append the rows to the Students table. -Tom. Microsoft Access MVP . |
Thread Tools | |
Display Modes | |
|
|