ok, this might be complicated,
but I am trying to Create a union sql statement, that is... one list (select) all records where programlength field should not equal to "academic year"

and then union with the other sql query that selects all records where programlength field is equal to "academic year" BUT selects only the minimum of the record, which means only select the lowest record of duplicate records by using PIID and Term.

here is my code.. hopefully you are able to get it

oh,and the error I get when I run it is the following:
"You tried to execute a query that does not includethe specified expression 'SID' as part of an aggregate function"
And SID is unique student ID, which is a primary key as well.

[SQL]

SELECT a.PIID, a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS Minor
FROM tblMajor AS a, tblProcessInfo, tblPermInfo, tblHostInfo
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblHostInfo.HostInstitution = tblProcessInfo.HOST AND tblPermInfo.SID = a.SID AND (tblProcessInfo.Term="AY024-031" Or tblProcessInfo.Term="024" Or tblProcessInfo.Term="031" Or tblProcessInfo.Term="032" Or tblProcessInfo.Term="033") AND USCitizen = 'Yes' AND a.ProgramLength NOT LIKE "Academic Year"
GROUP BY a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, b.Major

UNION


SELECT Min(a.PIID) AS [MIN PIID], a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, Min(tblProcessInfo.Term) AS [Min Term], a.Major, tblPermInfo.USCitizen, a.College, (SELECT Max(b.Major)
FROM tblMajor AS b
WHERE b.SID = a.SID
AND a.MajId < b.MajId) AS second_major, (SELECT Max(d.Minor)
FROM tblMinor AS d, tblMinor AS e
WHERE d.SID = a.SID
AND d.Minor <> e.Minor) AS Minor
FROM tblMajor AS a, tblProcessInfo, tblPermInfo, tblHostInfo
WHERE a.MajId = (SELECT Min(c.MajId)
FROM tblMajor AS c
WHERE c.SID = a.SID) AND tblProcessInfo.SID = a.SID AND tblHostInfo.HostInstitution = tblProcessInfo.HOST AND tblPermInfo.SID = a.SID AND (tblProcessInfo.Term="AY024-031" Or tblProcessInfo.Term="024" Or tblProcessInfo.Term="031" Or tblProcessInfo.Term="032" Or tblProcessInfo.Term="033") AND USCitizen = 'Yes' AND a.ProgramLength = "Academic Year"
GROUP BY a.SID, tblPermInfo.LastName, tblPermInfo.FirstName, tblPermInfo.Ethnicity, tblPermInfo.Gender, tblPermInfo.LocalEmail, tblPermInfo.AbroadEmail, tblProcessInfo.Host, tblProcessInfo.Sponsor, tblProcessInfo.Programname, tblHostInfo.Country, tblProcessInfo.Country, tblPermInfo.ClassStatus, tblProcessInfo.ProgramType, tblProcessInfo.Term, a.Major, tblPermInfo.USCitizen, a.College, b.Major
[/SQL]

To make a complicated query simple, I'd suggest you to test both of the queries separately, and when you're sure they work fine, use UNION.

Just a question: how would YOU feel if you had to debug such an unstructured query? I bet you'll get a headache to figure out what is wrong about it. Therefore, I'd suggest you to a) write structured queries, b) use "[ code ]" (without spaces) to produce more readable source code here, on the Forum.

Not much of a help, huh?

thanks for the advice,
well, the first sql statement does work, but the second one doesnt work after I added Min to both PIID and Term fields.

so any clues?

here's your second query, reformatted so that you can read it --you have MIN() on tblProcessInfo.Term in the SELECT, but you left tblProcessInfo.Term in the GROUP BY

this is not all that's wrong with it, either

you also have b.Major in the GROUP BY, and there's no b table in the outer query