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 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.