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

Also i don't know why it won't let me add my sql code onto here. It doesn't post...If you could maybe tell me how to do this i could post it thanks

__________________
Carbon_13

Copy the code to NOTEPAD first and then copy it to here.

When you copy directly from some other programs, the text will contain characters and character combinations that are rejected by this silly forum. (e.g., LFs with no preceding CR, etc., etc.)

NOTEPAD allows most anything to be pasted into it, and then when you copy from NOTEPAD you *will* get text that this forum can handle.

I tried that it still didn't work...All i can post of my code is this (anything after this will not let me post)

SELECT Count(*) AS

Carbon_13

Well, try posting in this forum:

http://www.aspmessageboard.com/forum/databases.asp

That site has many problems, so don't be surprised if you have to try more than one time to even log in.

Or tell you what...I don't generally do this, but...

You can send me email
     OldPedant ATSIGN Gmail PERIOD com

I have never had a problem posting code in this forum, nor have I had a problem logging in.

Here is some code:

Private Sub Form_Load()
Dim rs As ADODB.Recordset
Dim rsLocal As ADODB.Recordset
Dim cn As ADODB.Connection
Dim sSQL As String
Dim sSQL_Local As String

DoCmd.SetWarnings False
DoCmd.OpenQuery "DELETE_UserName"
DoCmd.OpenQuery "DELETE_Vendors"
DoCmd.OpenQuery "DELETE_Assets"
DoCmd.SetWarnings True

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=Server;Initial Catalog=Assets;UID=User;PWD=password"

'Add Asset data
sSQL = "SELECT * FROM vwLaptopUpdate_Assets"
sSQL_Local = "SELECT * FROM Assets"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

Set rsLocal = New ADODB.Recordset
rsLocal.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
    rsLocal.AddNew
    rsLocal("AssetID") = rs("AssetID")
    rsLocal("Asset") = rs("Asset")
    rsLocal.Update
rs.MoveNext
Loop

'Add Vendor data
sSQL = "SELECT * FROM vwLaptopUpdate_Vendors"
sSQL_Local = "SELECT * FROM Vendors"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

Set rsLocal = New ADODB.Recordset
rsLocal.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
    rsLocal.AddNew
    rsLocal("VendorID") = rs("VendorID")
    rsLocal("VendorName") = rs("VendorName")
    rsLocal.Update
rs.MoveNext
Loop

'Add User data
sSQL = "SELECT * FROM vwLaptopUpdate_UserName"
sSQL_Local = "SELECT * FROM UserName"

Set rs = New ADODB.Recordset
rs.Open sSQL, cn, adOpenDynamic, adLockOptimistic

Set rsLocal = New ADODB.Recordset
rsLocal.Open sSQL_Local, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do Until rs.EOF
    rsLocal.AddNew
    rsLocal("UserID") = rs("UserID")
    rsLocal("LOGIN_NAME") = rs("LOGIN_NAME")
    rsLocal("LastName") = rs("LastName")
    rsLocal("FirstName") = rs("FirstName")
    rsLocal.Update
rs.MoveNext
Loop

End Sub

Here is some SQL code:

SELECT LaptopUpdate_Start.DetailsID, LaptopUpdate.LaptopUpdateID, LaptopUpdate.UserID, LaptopUpdate.Notes, LaptopUpdate.DateCreated
FROM LaptopUpdate_Start INNER JOIN LaptopUpdate ON LaptopUpdate_Start.DetailsID = LaptopUpdate.DetailsID;

I am not sure why you would be having this problem.

Most of the users post their contact information so that you can send files and code behind the post if you are working with someone. Just click on my name and send me the code in line with your email text. I will post it and we can have a look at it.

HTH

mmcdonal

Look it up at: http://wrox.books24x7.com

Ok here is the code. I know I misspelled FROM (FRUM) and WHERE (WHE.RE), but it was the only way that I could get the code to post (strange), I also couldn't put both codes on the same reply (strange as well). Well anyways here it is.

Carbon_13

This is the second part of the query from above

Once again what I want it to do is to take both queries and add up the count. The data is pulled from the same table but differs in the StartYear and Starts.


Carbon_13

I am not sure why this query would not work for you. What happens when you run it?

SELECT Count(*) as current
FROM tbl_work
WHERE [2007]='Full - Time' OR [2007]='Part - Time'
AND StartYear=2007
AND Starts='Spring'

There may be an issue with the data types. If 2007 is not a number, for example in the StartYear field, which it shouldn't be.

Also, how many possible values are there in the field "2007"? If there are only full or part time workers, then no need to parameterize this field.

What is the table structure, and how is the query being parameterized? Is this hard coded?


mmcdonal

Look it up at: http://wrox.books24x7.com

OH! OF COURSE!

Here's a question for you, then:

What is the value of

      7 + 3 * 10

If you answered "100" then go back to junior high school math class.

Multiplication has HIGHER PRECEDENCE than addition. So you are supposed to do the multiply *first* and *THEN* do the addition. In other word, it is *AS IF* you had coded

      7 + ( 3 * 10 )

If you intended the addition first, then even in human math you need to use the parentheses:

     ( 7 + 3 ) * 10

Well, guess what: LOGICAL OPERATORS work the *SAME WAY*!!!

The AND operator has HIGHER PRECEDENCE than the OR operator.

So your WHERE clause is *EFFECTIVELY* doing THIS:

In other words, if [2007] is indeed 'Full - Time' then THE ENTIRE REST OF THE EXPRESSION IS IGNORED!!!

The solution is easy, just as it is in arithmetic:



Finally, I should point out that there's an even easier way:



People tend to forget about the IN operator, which makes a convenient OR-ing of values of the same field.

Now that we got your fallacious use of the logical operators out of the way, I'm thinking you still have a question.

Because you wrote "I want it ... to take both queries and add up the count."

So do you mean the *TOTAL COUNT* from BOTH queries as a *SINGLE VALUE*???

If so, guess what: Precedence of operators, again.

Thus:
Here, even though I used the IN( ) operator to avoid the need for parens around the OR of the two [2007] values, I still need parens with the OR operator to get things right. (Actually, there are extra parens in there, but I think it's a lot clearer to use too many and emphasize *exactly* what is happening than to be clever and use only exactly the right number.)

Finally, if what you really meant was that you wanted a SINGLE query to get *BOTH* of those two COUNT(*) values, then you could do it like this: I do have to think that your DB design is screwed up. Seems horrible to me to have a field named [2007] and yet have StartYear values of 2007 and 2008. What do you do when you get to 2010??? Add another field named [2010]??? Ugh. Really really bad.