Want to add additional Drop down options, Tables and Queries to my database ( I am Access Novice)

[chevenowner]

Dear All,



I have been asked to work on this Access database which works perfectly fine at the moment. Its a database to collect Course attendance records, Pretty simple.

I want to add list of 2015 courses and have a seperate tab for 2015 courses(just like 2012,2013,2014).All the courses are setup as queries, which is why i can not seem to add to them.If you go in 2014, 2013 tab you will see list of coures for those years, i just want exactly same thing for 2015. All the courses are same, juwst their name is slightly diff.

I hope i am making sense,

If you can help i will be grateful.

I am attaching the database for you guys

Any help will be appreciated

Thanks
Hamza

[ranman256]

Look at the query, it will tell you what table the data is in.
This is a bad way to design your form.
The year should be in a combo box. user picks the year, THEN gets the courses.
You need a new form design.

This hardcoded design is not easy to modify. (hence your post)

[chevenowner]

I know exactly where all the data is and table, i just can not seem to add 2015 list of courses and have a seperate tab for it.

If you open the database attached, it will make more sense, i know i am not doing a good job of explaining it.

Thanks
Hamza

[ranman256]

Make a new form
with a combo of years (default current year)
a table of courses
member details is find.

sorry about the bad design.

[chevenowner]

Ya i agree with you totally, thats why i can not seem to do it.. So how do i add list of 2015 courses to it ?

Its being design by some one else not me

[Jim Connell]

Hi Hamza:

You've made life a bit difficult for yourself. Your tables need to benormalized.

1 table for Courses
1 table for Students
1 table for courses offered during a semester
1 table for students assigned to courses

This is an no the fly design but I think you'll get the idea

The course table:
CourseKey (autonumber)
Course ID: the ideitifier a Human sees
Course Name
etc

The Student Table:
StudentKey(autonumber)
StudentID: theidentifier a Human sees
FirstName
LastName
Etc.
Courses Offered DuringSemester
CourseSemesterKey:Autonumber
CourseKey
SemesterID
StudentCourseKey
CourseSemesterKey
StudentKey
With thisapproach everything is held in just a few table and you gain the ability to transition to new semesterswith ease

[rpeare]

What ranman is trying to tell you is that you will have to create a new tab for every year as long as this database exists. the tables are not defining the cy/fy of the class, each button you click on each year is using a criteria for a specific query in your object window. This is a terrible terrible design as has already been pointed out and if you intend to keep it without altering the design of the tables/restructuring the whole database you'll have to add data the same way it's already been added and make queries for each class for each year to have this work. Then attach the queries to the appropriate button on a new tab of your form.

Edit: I don't think it was clear in my original post. Your database is basically 'dumb' it can not identify which classes were in which year. It is relying on you to create a query with the correct identifier to tell it which year it's in. The only place I can see any sort of information is in the description of the class which does not carry over to the criteria in the query.

Look at the button on the 2011 tab

IB04 Feb 11
When you click it it brings up the query qry_IB04_Feb_11
The query itself has a specific criteria in the field Introductory Briefing (the value 2)
If you go to your tables tbl_IB_Courses and look up the value for the key 2 you will see the value (course description) IB04 Feb 11

[ranman256]

college2.zip

Examine this version and see if its the direction you want to go?