SQL Help

[carbon_13]

Hello,
I need to combine the following two parts. It would basically add the two queries. The queries work fine by themselves but it fails when combined (I don't know how to combine them). So if I could get some help in combining these two I would really appreciate it. Thanks

sql.doc

[Ron Weller]

Both SQL statements are Identical, I see nothing to combine; but I did notice a possible error in your logic. The 'And' Operator takes presidence over the 'Or' operator so based on Your original statement:

SELECT Count(*) AS [Count]
FROM Tbl_Work
WHERE [2007]='Full - Time' OR [2007]='Part - Time' AND StartYear=2007 AND Starts='Spring';

Count all records where [2007]='Full - Time' and also count the records where [2007]='Part - Time' And StartYear=2007 And Starts='Spring'

This could produce a very defferent result then if you add Parentheses and force a different grouping like this:

SELECT Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2007]='Full - Time' OR [2007]='Part - Time') AND StartYear=2007 AND Starts='Spring';

Count all records where [2007] must have a value of either 'Full - Time' or 'Part - Time' but each record must also have StartYear=2007 and Starts='Spring'

I am not sure which way you wanted it to work; but I thought I would point out the difference.

[carbon_13]

I'm sorry about that. The SQL statements are different. Here they are

sql[1].doc

What it basically needs to do is to add up the two different situations defined by WHERE " ". Both get the same data from the same table.

[Ron Weller]

Try This:

SELECT Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2007]='Full - Time' OR [2007]='Part - Time') AND (StartYear=2007 Or StartYear=200) AND (Starts='Spring' Or Starts='Fall');

[carbon_13]

Yes that is what I wanted, and that does work
I also want to be able to combine two of these in a query (I don't know if this would be a subquery) What is the best way to do this. The queries of course would be for different years (ex. WHERE [2006]='Full - Time' OR [2006]='Part - Time'................)

[Ron Weller]

You could use a summary query. Add Year to the Selection and then Group By year. For example if we use your StartYear, We group by it and find a range like all records from 2005 to 2007. Your SQL would look like this:

SELECT StartYear, Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2007]='Full - Time' OR [2007]='Part - Time') 
AND (StartYear>=2005 And StartYear<=2007) 
AND (Starts='Spring' Or Starts='Fall')
GROUP BY StartYear;

Now if you need to select by a hard coded field name like [2007] then you would have to use a union query, something like this:

SELECT  2007 As Year, Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2007]='Full - Time' OR [2007]='Part - Time') 
AND StartYear=2007 
AND (Starts='Spring' Or Starts='Fall')
UNION SELECT  2006 As Year, Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2006]='Full - Time' OR [2006]='Part - Time') 
AND StartYear=2006 
AND (Starts='Spring' Or Starts='Fall')
UNION SELECT  2005 As Year, Count(*) AS [Count]
FROM Tbl_Work
WHERE ([2005]='Full - Time' OR [2005]='Part - Time') 
AND StartYear=2005 
AND (Starts='Spring' Or Starts='Fall')

In a Union query each query must have the same fields in the select clause so I hard coded the year value and gave it the name 'Year'.
The results of this union query will be 3 records where Year will be one of 2005, 2006, or 2007 and Count will be the count for that year.
Notice each Select statement will return one record for it's selected year.