Using 2 criteria for the same field

[Flavio Argueta]

I need assistance with creating a select query or sql statment. Below are the details

table name: StudentSemester

Table structure:

Term Name
Fall 2008 John
Spring 2009 John
Fall 2008 Jane
Spring 2009 Mark


I need the query to only select Spring 2009 records and exclude records where a student may have a Fall 2008 and Spring 2009 term.

For this example; I would like the query to return with Mark's record. John would not be selected because a Fall 2008 and Spring 2009 exists. Any help would be appreciated.

EggHeadCafe - Software Developer Portal of Choice
..NET Asynchronous Events And Progress Bars
http://www.eggheadcafe.com/tutorial...a6d-a24a02ba2b8a/net-asynchronous-events.aspx

 

[KARL DEWEY]

Try this substituting yyou table name --
qryFall_Spring --
SELECT YourTable.FName, YourTable.Term, YourTable_1.Term
FROM YourTable INNER JOIN YourTable AS YourTable_1 ON YourTable.FName =
YourTable_1.FName
GROUP BY YourTable.FName, YourTable.Term, YourTable_1.Term
HAVING (((YourTable.Term)='Fall 2008') AND ((YourTable_1.Term)="Spring
2009"));

SELECT YourTable.FName
FROM YourTable LEFT JOIN qryFall_Spring ON YourTable.FName =
qryFall_Spring.FName
WHERE (((qryFall_Spring.FName) Is Null));

 

[John Spencer]

SELECT [Name], [Term]
FROM [SomeTable]
WHERE [Term] = "Spring 2009"
AND NOT Exists
(SELECT *
FROM [SomeTable] as TEMP
WHERE Temp.Term <> "Spring 2009"
AND Temp.[Name] = [SomeTable].[Name])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Flavio Argueta]

Thanks John. That did it! I was hoping that perhaps you could help me with the following.

Looking at the same table. How can I select records where the Term is Fall 2008 and Spring 2009. I would like to be able to view both terms in the output. Thank you in advance. I really appreciate your assistance with this.



John Spencer wrote:

SELECT [Name], [Term]FROM [SomeTable]WHERE [Term] = "Spring 2009"AND NOT
30-Oct-09

SELECT [Name], [Term
FROM [SomeTable
WHERE [Term] = "Spring 2009
AND NOT Exist
(SELECT
FROM [SomeTable] as TEM
WHERE Temp.Term <> "Spring 2009
AND Temp.[Name] = [SomeTable].[Name]

John Spence
Access MVP 2002-2005, 2007-200
The Hilltop Institut
University of Maryland Baltimore Count

Flavio Argueta wrote:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
C# Threading Http Requests
http://www.eggheadcafe.com/tutorial...69-7010659bd748/c-threading-http-request.aspx

 

[John Spencer]

Do you mean you want to select Names where both terms are associated with the
name? Assuming that there is only one record per combination of Name and Term
then the following should work.

SELECT [Name]
FROM [SomeTable]
WHERE [Name] in
(SELECT [Name]
FROM [SomeTable]
WHERE Term in ("Fall 2008","Spring 2009")
HAVING Count([Name]) = 2)

If you mean to get records where the Name is associated with either then d

SELECT [Name], [Term]
FROM [SomeTable]
WHERE Term in ("Fall 2008","Spring 2009")

If a name can be associated with a term more than once then you have a couple
of choices. Simplest might be the following.

SELECT [Name]
FROM [SomeTable]
WHERE [Name] in
(SELECT [Name]
FROM [SomeTable]
WHERE Term ="Fall 2008")
AND [Name] in
(SELECT [Name]
FROM [SomeTable]
WHERE Term ="Spring 2009")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County