Trying to exclude earlier semesters

mstoller

New member
Local time
Today, 18:03
Joined
Mar 21, 2013
Messages
8
This is my second attempt at designing a database so I'm very new to the process, was hoping someone on here can help me out. I have found plenty of great information here that got me to where I am, but now I could use some help.

I am working on an Access database for a College Bookstore. The database currently is designed so I can import tables of future semesters, and compare those to tables of historical data (of textbooks adopted for those courses).

I have each semester in its own table. Those tables each have the following headers, Term, Department, Course, Section, Instructor, Title, Author, ISBN

Semesters or the Term column are labeled by what is used in the store, A for Spring, J for Summer, Q for Fall, and W for Winter. So Spring 2012 is 12A, Fall 2012 is 12Q, etc.

I have a query (History) in which I used SELECT and UNION ALL sql to make one long table of all of the Historical terms I'd like to compare against the future one (13q).

Query 2 is the query comparing my table 13Q to the table History. I have been able to get this query to show exactly what I am looking for, except I have not found a way to exclude older semesters when newer information is available, ie A professor has taught the same course every semester for 6 semesters, the query is showing all 6 semesters of history. I'd only like to see the most recent semesters books.

One thought I've had is creating a new table, listing the course codes, and assigning the actual dates they occurred. Then somehow I would run what I found on the Microsoft site, about Finding Records with the Most Recent Date against my Query2. I am not sure how I would do this or if this is even the best way (or at all possible) to do what I have described. Help would be greatly appreciated.
 
I have each semester in its own table.
Okay, that's the first thing we need to do. You DO NOT put them in their own table. You just have it all in the same table and have a field to identify the semester.

Fix that first and then we can continue.
 
Bob,

Thanks for taking the time to help me out,

I combined all of the old semester tables into one called History. Table 13J and 13Q are still there since they are the ones we to compare with History. Fields remain the same, Term, Department, Course, Section, Instructor, Title, Author, ISBN.

Thanks again,
Mike
 
Bob,

Thanks for taking the time to help me out,

I combined all of the old semester tables into one called History. Table 13J and 13Q are still there since they are the ones we to compare with History. Fields remain the same, Term, Department, Course, Section, Instructor, Title, Author, ISBN.

Thanks again,
Mike
Nope, still not there. You need to combine them ALL into one. Not kidding here. You want to have this correct and be able to get good data out, you need to do that. There should be a field (or two really - SemYear and Sem - 2013 J for example).
 
Ok, apologies for not understanding that, I have now created one table called All. I have also separated the term field into two fields as suggested, Year (10, 11, 12, 13) and Term (A, J, Q, W).
 
Okay, now that we have that in place, we can move on. Now, I'm not real clear on what it is you are looking to compare. What is it that you are actually wanting to see? What do you mean compare against future semesters?
 
The old spreadsheets (from semesters that have happened already) show what books professors used previously in those courses. The new spreadsheets (for the upcoming Spring and Fall) show the courses being taught. This is what I combined into one table. I would like to see what books were used the last time they taught the same course.
 
I'm not sure how to set up the query even with the one large table. Apologies again for being such a novice.

An example of what I am looking for, if
(Department)AAR(Course)1500(Section)45(Instructor)Taub is in 13Q with a textbook title of No Information Received (Every one is at this point if that helps)
What books did he use in 13A (or prior if not 13A).
 
I was able to recreate what I had done earlier (but now with the normalization you had me do), so I have a query that is working. It lists what books were previously used for the classes being taught in the upcoming Fall semester. The query has the following headers, ID, Year, Term, Department, Course, Section, Instructor, Title, Author, ISBN

I am still trying to remove older instances where a teacher has taught the same course for many years, ie. for one course I am seeing a line item for 13A, 12Q, 12A, 11Q, 11A, etc, and they are all the same information, so ideally I'd like just 13A to show up. The 13 is in column Year and the A would be in column Term.
 
I used two queries to do this, SQL below, first is to pull 13 Q out of the single table, and the second is to compare it to the single table,

First:
SELECT [All].*
FROM [All]
WHERE ((([All].Title)="NO INFORMATION RECEIVED") AND (([All].Year)="13") AND (([All].Term)="Q"));

Second:
SELECT [All].*
FROM [All] INNER JOIN 13Q ON ([All].Instructor = [13Q].Instructor) AND ([All].Section = [13Q].Section) AND ([All].Course = [13Q].Course) AND ([All].Department = [13Q].Department)
WHERE ((([All].Title)<>"NO INFORMATION RECEIVED"))
ORDER BY [All].Instructor, [All].Department, [All].Course, [All].Year DESC , [All].Term DESC;
 
I added a new table called Dates, that table has 4 columns, ID, Year, Term, Startdate. I added a relationship between the Year and Term columns on my two tables, (All and Dates). Now I believe it should be easier to exclude the older entries in my query but I still can not figure out how to do that. SQL on the queries has not changed from above.
 

Users who are viewing this thread

Back
Top Bottom