How to show only unique records in a query?

[wes228]

I am trying to run a query that shows all of our professors who are teaching in the current Fall or upcoming Spring semester only. I have a table that has each course listed, the semester, and the person teaching it. I am trying to pull a query using this table. However, many professors teach more than one course, not only within each semester but also across semester: e.g. John Smith teaches Photography I and Photoshop in Fall, then Photography I again in Spring.

When I try to run this query, John Smith will therefore appear three times in my query, once for each class. I have tried running a totals query with "First" selected under Faculty Last Name, under Semester, and under both, but this doesn't work ("First" selected under Last Name will knock out quite a few professors entirely!). I have also tried going into SQL mode and changing SELECT to SELECT DISTINCT but this doesn't work either.

I'd prefer a solution that does not require SQL mode.

Thank you!

[rpeare]

What's your data structure?

You should have a 'classes' table, a 'staff' table and a 'staffclasses' table

The classes table would list all possible classes taught
The staff table would list all the possible staff
the staffclasses table would list which teachers are teaching which classes during which terms

If you take your staff classes and are able to identify the correct span you would create an aggregate query (which it sounds like you did)
Select the STAFFID (the foreign key in your staffclasses table that links to your staff table) make sure it says GROUP BY
in the date or semester field that you're using to search on a given time period you want the totals line to say WHERE

So let's say you had a table called
tblStaffClasses
SC_ID Staff_ID Class_ID Start_Date -----> other information

your query would be something like

SELECT Staff_ID From tblStaffClasses GROUP BY Staff_ID WHERE Start_Date between #1/1/2013# and #12/31/2013#

this would give you a list of individual staff ID's that have a class anywhere in the period you're interested in, once that query is established and showing what you want you can then add the staff table to retrieve the staff name/any other information you need for your request.

[wes228]

That is precisely my structure. I have a Faculty table, a Course Inventory table, and then a Faculty Courses table. The semesters are not stored as dates however, they are stored as codes (1138 = Fall 2013, 1142 = Winter 2014, 1144 = Spring 2014). Is there a way to input this in the Query Design view? I have to show this to other people in the office to do and I know their heads will spin if they have to open up SQL View (I don't even really want to use it myself).

[rpeare]

SELECT Staff_ID From tblStaffClasses GROUP BY Staff_ID WHERE Semester_Number between [Enter the Starting Semester Number] and [Enter the Ending Semester Number]

it's this will prompt the user to put in the starting and ending semester numbers and pull anything within the range

Just save the query with a name and you can provide a switchboard (form) where they can just click a button and retrieve the query (or report if you build one) themselves.