As was foretold, we've added advertisements to the forums! If you have questions, or if you encounter any bugs, please visit this thread: https://forums.penny-arcade.com/discussion/240191/forum-advertisement-faq-and-reports-thread/
Options
Help me learn Access 2007?
LaOs
SaskatoonRegistered User regular
SaskatoonRegistered User regular
I've looked through some of the tutorial stuff and teaching lessons in the Microsoft support site, and they're generally helpful for figuring out how to use Access, but I thought I would stop and ask here for some pointed guidance and feedback on the project I have in mind.
At work we have an Excel spreadsheet that we use to track course equivalencies for our pre-requisites and courses at other institutions. It's fairly simple and can be found in a PDF version online for potential applicants. It is, however, completely manual and somewhat unwieldy... it was suggested that it might better work as an Access database.
So, here I am, tasked with figuring it all out. That's all right--I wanted to learn the program anyway, and I like taking on projects like this. I just am not sure if I'm just having trouble wrapping my head around how I have to use/present the data and my unfamiliarity with the program is hampering my efforts or if perhaps I'm after the wrong sort of results... I'm inclined to believe it's the former.
How I want it to behave:
You start off with a form (or just a screen). There are four options (buttons) to choose: New Evaluation, Edit Evaluation, Report by Course, Report by Institution.
New Evaluation is a form. This form has some fields/lists/boxes:
- Institution Name (combo box with ability to type a name that's not on the list)
- Course Name & Number (combo box, list depending on the Institution Name, with ability to write new name & number (BIOL 121 for example))
- U of S Course (combo box/list without ability to type name--we only have 6 courses we look for equivalencies for)
- Equivalent? (radio buttons, either Yes or No)
- Save (button)
Edit Evaluation is another form, nearly the same as New Evaluation:
- Institution Name (combo box without ability to type new name)
- Course Name & Number (combo box, list depending on Institution Name, no ability to write new)
- U of S Course (list based on two above, will usually be one option in the list unless course above has been evaluated for multiple requisites)
- Equivalent? (radio buttons, either Yes or No)
- Expired? (check box or radio button, checked or unchecked)
- Save (button)
Report by Course and Report by Institution are, obviously, reports. Users would either choose a U of S course and see all courses from all other institutions that have been evaluated against it, or they would choose an institution and see all courses from it that have been evaluated against U of S courses. Within each report, results could be filtered by Current Equivalencies, Expired Equivalencies, or All Equivalencies.
At least, these are the rough ideas I have of how I want it to work now. Basically, the database is populated by entering Evaluations in the form (I need to go through the old collection of evaluations anyway, so the fact that this is a slower/manual process is all right). New Institutions can be added via the form as we receive new course requests from applicants, and our U of S courses will, obviously, have a forever-expanding list of courses from other institutes associated with them. Ultimately, I don't want users to have to much around in the tables themselves or have to figure them out.
Other than not knowing for sure how to make Access actually work for me the way I want (this I can learn), I just can't figure out how to design/build the database initially so that it will work how I want. Today's been a bad day for me mentally (I can't focus on anything... I've been back and forth between three different things while writing this message alone!) so I may just not be clicking with things here, but any advice would be appreciated.
At work we have an Excel spreadsheet that we use to track course equivalencies for our pre-requisites and courses at other institutions. It's fairly simple and can be found in a PDF version online for potential applicants. It is, however, completely manual and somewhat unwieldy... it was suggested that it might better work as an Access database.
So, here I am, tasked with figuring it all out. That's all right--I wanted to learn the program anyway, and I like taking on projects like this. I just am not sure if I'm just having trouble wrapping my head around how I have to use/present the data and my unfamiliarity with the program is hampering my efforts or if perhaps I'm after the wrong sort of results... I'm inclined to believe it's the former.
How I want it to behave:
You start off with a form (or just a screen). There are four options (buttons) to choose: New Evaluation, Edit Evaluation, Report by Course, Report by Institution.
New Evaluation is a form. This form has some fields/lists/boxes:
- Institution Name (combo box with ability to type a name that's not on the list)
- Course Name & Number (combo box, list depending on the Institution Name, with ability to write new name & number (BIOL 121 for example))
- U of S Course (combo box/list without ability to type name--we only have 6 courses we look for equivalencies for)
- Equivalent? (radio buttons, either Yes or No)
- Save (button)
Edit Evaluation is another form, nearly the same as New Evaluation:
- Institution Name (combo box without ability to type new name)
- Course Name & Number (combo box, list depending on Institution Name, no ability to write new)
- U of S Course (list based on two above, will usually be one option in the list unless course above has been evaluated for multiple requisites)
- Equivalent? (radio buttons, either Yes or No)
- Expired? (check box or radio button, checked or unchecked)
- Save (button)
Report by Course and Report by Institution are, obviously, reports. Users would either choose a U of S course and see all courses from all other institutions that have been evaluated against it, or they would choose an institution and see all courses from it that have been evaluated against U of S courses. Within each report, results could be filtered by Current Equivalencies, Expired Equivalencies, or All Equivalencies.
At least, these are the rough ideas I have of how I want it to work now. Basically, the database is populated by entering Evaluations in the form (I need to go through the old collection of evaluations anyway, so the fact that this is a slower/manual process is all right). New Institutions can be added via the form as we receive new course requests from applicants, and our U of S courses will, obviously, have a forever-expanding list of courses from other institutes associated with them. Ultimately, I don't want users to have to much around in the tables themselves or have to figure them out.
Other than not knowing for sure how to make Access actually work for me the way I want (this I can learn), I just can't figure out how to design/build the database initially so that it will work how I want. Today's been a bad day for me mentally (I can't focus on anything... I've been back and forth between three different things while writing this message alone!) so I may just not be clicking with things here, but any advice would be appreciated.
LaOs on
0
Posts
That appears to be your main concern from it. What you'll need to learn is something a bit different from programming (though later, when you build the interfaces you speak of, you'll need some basic VBA programming knowledge... but that'll come soon).
What you need is a tutorial on Database design. Database design can require a lot of forethought and learning about how one should and shouldn't design databases. Have you had experience with this before?
Let us know the level of experience you have had with database design (and databases in general) so we know what level of tutorial/advice you might need. For example...
1) (Absolutely Essential knowledge) Have you ever created a table? Do you know what a table is? How about a primary key? Do you know what the purpose of a Primary key is in a table?
2) (Intermediate, but still essential knowledge) Do you know how to relate two tables to each other? Do you know what a "relation" is in a database?
3) (Knowledge that will save you a lot of headaches if your database gets large or your program gets complicated) Have you ever heard of Database Normalization? Do you know how to normalize a database?
Let us know, we can try to point you in the right direction. I was going to toss a w3schools tutorial link up here to start with because they're awesome and I thought they had a database design tutorial, but to my dismay they don't. =(
This is exactly where my mind went.
Access can be pretty unforgiving... if your database is not structured properly right from the start, it can be a bear to get anything done.
The three questions VThornheart asked above are relevant for any relational database platform, whether you were building in Access 2003 or 2007 or Filemaker Pro or SQL or what have you... and you should be able to answer all three of them confidently before you even launch Access.
To use an analogy, it's like you're about to test drive a foreign car and you're asking questions about all the dashboard buttons and dials... which are valid questions, but before you drive any car off the lot we need to know that you know the basic rules of the road.
If not, please, for your own sake and the sake of anybody else you're working with, brush up on basic database architecture. It's not difficult, you can do it in a weekend, and a lot of "Access For Dummies" and "Complete Idiots Guide to Access" type books will devote a whole chapter on it at the very beginning.
the "no true scotch man" fallacy.
1) I think I know what a table is... I mean, I've seen them, read a bit about them, understand that they look like spreadsheets and work similarly but are where the actual data for the database go, etc. I know what a primary key is and a foreign key and somewhat their importance.
2) I've played around a bit with relationships between tables and read some about them on the MS site.
3) I've heard of Normalization but when I read to that point I stopped because I was clearly not at that stage yet, so went looking for more basic and entry-level tutorials on the MS site.
I'm gathering, then, that Access should be able to be used in the way I want, I just have to start learning how to build it? Thanks.
My motive for creating this thread was to first determine if the way I wanted things to work would be accomplishable with Access. The secondary idea was to learn of online resources to help me wrap my head around organising the actual data I will be using and perhaps receive a nudge in the right direction.
For example, I understand that rows (records) are for individual things and the columns (fields) are facts about those things... I am just not clear if I'm thinking about my data in those terms correctly.
I have U of S Courses (pre-requisites), other Institutions, Courses from those institutions, judgements whether or not a course from an institution is equivalent to a U of S course, etc. I'm just not sure how to best organise them into separate tables and then which should be records/fields within those tables.
When I have people at my company asking me questions like this, I literally just loan them a (print) copy of Access for Dummies and say, "read the chapter on relational database design and call me on Monday."
Buying an Access for Dummies book will pay for itself several times over, too, just when you're trying to figure out how to implement a button or a report feature or whatnot.
the "no true scotch man" fallacy.
This is a pretty nice one, all things considered. Give this one a shot first. Though it's talking about designing databases using their program (which you don't need and shouldn't get unless you really want to), the concepts they are teaching are fundamental and true for any database.
About.com's Database Guide - Main Table of Contents - I'm dissatisfied with this guide personally, but it may help you so I'll link it. I think they assume too much for a person who's never been exposed to it before.
When you feel ready, MySQL has a good tutorial on database normalization but be careful to learn what tables, relationships, primary keys, foreign keys etc... are first so it doesn't overwhelm.
Also, if you get confused on any terms, post your confusion here. We can give you examples that will hopefully clarify it (I think that's why there's no good tutorials on this subject... you need a solid example to teach some of these concepts that the user can relate to)
... maybe one of these days I should write a tutorial on it. I don't know if I could succeed where others have failed at making database design tutorials, but I definitely think someone needs to at least try to make one that is good for someone with no prior knowledge of databases.
If anyone is watching, I also received help (mostly from Feral) in the D&D [Chat] thread, starting here and going on for a few pages after that yet.
Right now I'm just thinking about the tables and possible relationships and I want to make sure I'm not missing anything...
Institution Table
Institution ID*
Name
City
External Course Table
External Course ID*
Course Name/Number (BIOL 121 for example)
U of S Course Table
U of S Course ID*
Course Name/Number (CHEM 112 for example)
External/U of S Course Table
External Course ID
U of S Course ID
Equivalency Judgement
Institution/External Course Table
Institution ID
External Course ID
*indicates primary key
Course Name/Number will always be the pair, so there's no need to separate them into two fields
That's what I have thought out so far, but I'm not sure if it's going to completely do what I want it to. There are a couple other things I'm not sure how I would do, but I think they'd just be getting ahead of myself (for example, sometimes a course we evaluate is partially equivalent, and can be paired with any other partial equivalency from that institute and sometimes we have specific pairs of partial equivalencies that must go together, etc.)
Judging by the spreadsheet and looking at what I have planned out, what might I be missing, so far as relationships and data tracking go?
One thing I'd suggest, and it depends heavily on the specifics of what you need...
I get this feeling that the "External Course Table" and "Institution Table" may not need to have a many-to-many relationship with each other. I could be mistaken (as I don't know as much about this domain as you do), but having the "Institution/External Course Table" implies a many-to-many relationship there, which means an institution could have many external courses (which is true) but also that an external course could be given at many institutions (which I'm not sure is true, you'll have to clarify on that).
If it turns out that they shouldn't have a many-to-many, you could change the external course table as such (so that Institutions have many external courses, but not vice versa):
External Course Table
External Course ID*
Institution ID (FOREIGN KEY)
Course Name/Number (BIOL 121 for example)
Now, an institution can still have many external courses, but an external course can only have one institution. But that's only if my assumption is correct on what kind of a relationship they'd have.
Now for partial equivalency, what you really need is something that represents the "list of courses at an institution that match one given course at U of S". Now, that list could just be one course, or it could be many (as you said.
So you'd have something like:
A UofSCourse can have an equivalency agreement with many institutions.
An institution can have an equivalency agreement for many UofS courses.
(CONCLUSION: Institution and UofSCourse have a many to many relationship, which could be called "EquivalencyAgreements" [You called it "External/U of S Course Table", but it might be more appropriately described as an Equivalency Agreement])
An institution can have many ExternalCourses.
An ExternalCourse can have only one institution (again my assumption from above).
(CONCLUSION: Institution and ExternalCourses have a one to one relationship)
An EquivalencyAgreement for a UofSCourse may have one or more ExternalCourses that are required to be fulfilled.
An ExternalCourse can be a part of one or more EquivalencyAgreements for comparable UofSCourses.
(CONCLUSION; EquivalencyAgreement and ExternalCourse have a many to many Relationship, which could be called "EquivalencyComponent" or perhaps "EquivalencyExternalCourse")
That new table I speak of above, EquivalencyExternalCourse, would allow you to do what was proposed above (that is, to have a single table that shows which ExternalCourses (be they one or many) from a given institution are needed to fulfill the equivalency of a given UofSCourse.
Hopefully this helps! You're doing great so far! The scenario above is a bit advanced, because in a way you have a many to many relationship with its OWN many to many relationship... but if you break it up as I did above, you can reduce it down to a series of one-to-many relationships... and if you think carefully about what each "Cross Reference table" (as they call tables that break many to many relationships into one to many relationships) actually IS in the problem, you'll usually find that it represents something concrete (such as the "Equivalency Agreement" for UofS Courses vs. other institutions, or "Equivalency Components" which represent the one or more classes needed from an external institution to fulfill an Equivalency Agreement). Once you realize the actual tangible meaning of these Crossover tables, it all becomes much less confusing to deal with. But that's, generally, the hardest part about requirement gathering and database design.
I've kind of started again, at least with how I'm thinking about it. Here's some of what I've been thinking about:
There are 7 Requirements. Each Requirement can be fulfilled by many Institutions and each Institution can fulfil many requirements. (many-to-many)
Each Institution can have many Courses and each Course can only have one Institution. (many-to-one)
Each Requirement can be satisfied by many Courses and some Courses can satisfy many Requirements. (many-to-many)
For tables, I have:
Requirements
*ID
Intro Biol
Intro Chem
Organic Chem
Intro Physics
Biochem Molecular
Biochem Metabolism
Social Science/Humanity
Institutions
*ID
Name
City
Province/State
Country
Courses
*ID
Name/#
Institution ID
Equivalencies
*ID
Requirement ID
Course ID
But that's all I've been able to figure so far. Ignoring the existence of required pairings of courses to satisfy a Requirement and partial equivalencies, what else do I need to establish one on one equivalencies? Then, of course, I will need to sort out how to handle when one course will partially satisfy a requirement (open partial) and when certain courses will, together, satisfy a requirement (specific partial), and things like that.
Somewhat different this time around is the fact that I'm now talking about general Requirements and will have the courses from our institution here listed and matched just like courses from other institutions. I think this leaves more room for our courses to change in the future as well as including higher-level courses that will satisfy our requirements.
Home Inspection and Wind Mitigation
http://www.FairWindInspections.com/
Also, sorry Liquid Hellz... if you start a new thread, we can answer it there. If it was the exact same problem that'd be one thing, but your situation sounds like a different problem and should probably have its own thread. I'll check it out when you make the thread though! Don't be afraid to make a thread.
Anyways, LaOs... I think you may be barking down the wrong tree with the way you're implementing requirements.
Your first fundamental problem is that you seem to not yet have a firm grasp on what the information on a table is attempting to portray... I'll try to help. It's most apparent in the Requirements table.
Ideally, your requirements table should be able to support arbitrary requirements (with each row representing a single discrete requirement). So rather than your current implementation, which if I'm not mistaken looks like it consists of an ID and a set of boolean values for specific requirements... you'd be better suited with something that can represent requirements more generically (and with only one row representing one requirement).
Requirements
*RequirementID
RequirementDescription
Then what you'd do is have multiple rows in the Requirements table, one for Intro Biol, Intro Chem, etc...
That will be much easier to make queries against, and allows you to actually make relationships between requirements and other entities (such as institutions and classes). The fact that you were having a problem figuring out how to relate them is a direct result of how that Requirements table was implemented.
Next, you have to ask yourself whether it's the institution itself that is fulfilling the requirement, or a specific COURSE that the institution is OFFERING.
The question can be answered by asking yourself the following question: If you merely attended this institution and took no courses at all, would you be fulfilling the requirements?
Answer: definitely not. Therefore, the relation isn't actually between requirements and institutions but rather between requirements and courses.
So a Course can fulfill many requirements, and a requirement can be filled by many courses.
As you may notice, you can't actually fulfill such a requirement by putting the ID of one or another into the existing tables: each one could need multiple.
What you need is what is called a "Cross-reference table". It is a table that looks like the following:
RequirementFulfillingCourses
* FulfilledRequirementID
* CourseID
Note that the primary key is a COMBINATION of the Course ID and the Requirement ID (most notably, of a specific Requirement being fulfilled by the class).
This means that this new RequirementFulfillingCourses is representing the combination of all Requirements fulfilled by all relevant courses. If a course fulfills five requirements, it will have five entries in this table. Likewise, if a requirement is fulfilled by twenty courses, it will have twenty entries in this table. The fact that the primary key is a combination of the two IDs allows for this to happen (as only the COMBINATION of them needs to be unique in the table).
At that point, you would query the RequirementFulfillingCourses table to see what courses fulfill a requirement or vice versa, or use it to join the Requirements and Courses tables as needed.
Let me know if this clarifies the situation.