expanding access database

[sloppyjoe778]

I have this one hw assignment and I need to at least write how i'm going to expand the database before doing it. There are some example ideas as well but I figured I'd ask here since i'm still a newbie with database design. Heres what the assignment is:




One of the earlier Access exercise/homework databases was a college example. Students, courses, registration, instructors, facilities… were reviewed and analyzed.
The database should now be expanded. You may modify it as desired. There are many more business activities and needs in a college. Examples include housing, bookstore, food services, and many more appropriate functions. These are only examples – you may select from these or any other functions.


Any tips or ideas? I guess food services would work okay. Any other good business ideas I could implement? It needs to be business oriented. Also supposed to add a new feature in the design. I'm wondering about doing one of those code/script things where someone does SELECT from etc. Maybe one that shows records in one table that aren't in another.

Any ideas, please let me know. Would need to know by 10am tomorrow.

[alansidman]

Any other good business ideas I could implement?

From a business perspective, you might consider on campus recruiting for graduating students. Schedule companies and students for interviews coupled with interview offices.

Alan

[sloppyjoe778]

Yeah that could work, also maybe I could add something with financial aid offices and different stupid accounts and when a payment is due. Maybe have it set so it'll show by order when latest payments are due and payments to be made the upcoming week. How would you do the scheduling thing? Just one table with fields for companies and dates and than create one for interviews with dates and times? How could I make a useful query out of that? Just looking for ideas.

Okay, any idea on some more advanced features of Access I could implement, that go beyond just a relationship database? Nothing too advanced though. All I can think is finding the excluded records. Like if you find all the students who have a financial aid account, maybe find the ones that don't show up so you can see the new students who still have to have one? Idk. But I'm confused on that because it seems like if you link 2 tables, they are supposed to have the same records so you can't exclude a student can you? Or how does that work. I ran into trouble with that before.

[sloppyjoe778]

Anyone know any examples of using an insert or update query, or a select from and delete from thing? Or a macro? I need to use at least 2 new features.

Basically going to have students, registrations, instructors, departments, courses. And going to try to fit some graduating stuff or financial aid stuff.

[alansidman]

If you have some sort of table for the cost of classes. Per credit hour, then you could run an update query to change the standard costs. Or the number of credit hours to graduate. Just some thoughts

Alan

[sloppyjoe778]

Okay here's what I have so far. Hope it doesn't look too bad but it's my rough draft. I'm still really new to database design. I'm a little confused on linking, like do you have to link every instance of an attribute, like c_Classroom appears in another table but I'm not if I have to link it. Also I'm confused about what to do with Interview_Offices, not sure if I should put c_classroom there and how to make it "fit".



Now I need to do 2 new features at least. I heard a kid in school talking about doing insert queries and update queries and macros. I'm thinking I can do either those 2 queries, or 1 and a macro. Any ideas on what macro to do? Also need to do one report to add something else to it. Heres query and report ideas so far:.

Queries/macros
update query to change the cost per credit hour (never did this but wanted to do something based on the years enrolled, like a freshman pays $100 a credit hour but a senior pays $200 a credit hour, but I guess I’l just change the cost per credit hour, but which table do I put that in???)
update query to change the number of credit hours to graduate
Macros – no idea

Report
show list of students that are eligible to graduate and available for interviews with companies
Show students who will run out of financial aid money before they can graduate
Show only graduating students and their courses

[alansidman]

I usually don't build relationships until they are needed. However, if your instructor needs to see them as part of your course requirements, then I would do them in advance. I usually do them in queries as required.

Here is a good step by step tutorial that I used when I first learned Access. Pay attention to the action queries.

Alan

http://fisher.osu.edu/~muhanna_1/837...tutorials.html

[sloppyjoe778]

Thanks for that link, it's helping a lot. I think I understand how to do the update query now. Going to just do the backup credits thing and than change credits thing, but maybe apply it to cost of credits somehow. Like for instance..

have credits = $100 a credit. and than change it to something else. But the only way I can think of using the $100 part is in a calculated field, or having a column next to every course that says $100. And than it gets changed and that effects the calculated field.

Also can someone please give me an idea for using a macro? I think i'll be set if I do that. Also want to do an insert query. Any ideas for either one?

[sloppyjoe778]

Hey I got some data set up like. Credit Hours Cost Per Credit Course.

The problem is, I want to add up the total cost for all the courses the student is taking. I tried doing a summary query but it hasn't worked. I also have it set so different departments have different Credit Hour prices. Any ideas on how to show the total cost though?