Comparing Two Queries in Access DB

  • Thread starter Thread starter T-boy
  • Start date Start date
T

T-boy

I have one main table that feeds three queries named “Qry_MBAâ€, â€Qry_BBAâ€,
and “Qry_ASâ€, and they all consist of (Student_ID, Student_Name, and Degree).
How can I have one student to appear in Qry_MBA only, even though he or she
have all the three Degrees? Or How can I have one student to appear in
Qry_BBA alone, even though he or she have BBA, and AS Degrees?
Would be great if I can get some help here, on this issue!
Thanks in advance.
 
It sounds like your table is laid out like a spreadsheet such as this --
Student_ID, Student_Name, Degree1, Degree2, Degree3, Degree4
and data like this --
213 Bill Jones MBA BBA AA
456 John Rex BBA AA

The trouble is how to determine which is the one that is desired as they may
be entered like this --
479 Sam Brown AA MBA BBA

It should be like this --
Student_ID, Degree, AcqDate, Verified
213 MBA 2/23/2004 Yes
213 BBA 6/22/1998 No
213 AA 9/10/1995 Yes
456 BBA 5/11/2008 No
456 AA 12/1/2001 Yes

With another table for people --
Student_ID, LName, FName, MI, etc..

Use a union to redo do your data --
SELECT Student_ID, Student_Name, Degree1 AS Degree
FROM YourTable
UNION SELECT Student_ID, Student_Name, Degree2 AS Degree
FROM YourTable
WHERE Degree2 Is Not Null
UNION SELECT Student_ID, Student_Name, Degree3 AS Degree
FROM YourTable
WHERE Degree3 Is Not Null
UNION SELECT Student_ID, Student_Name, Degree4 AS Degree
FROM YourTable
WHERE Degree4 Is Not Null:

Then I assume you want the highest degree. So build a table like this --
Dregree Order
PhD 1
MS 2
MBA 3
BA 4
AA 5

In another query join the union query with the Degree-Order table on Degree.
Change to totals query and select Min for the Order field.
 
Back
Top