Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old July 9th, 2008, 01:40 PM
Authorized User
 
Join Date: Mar 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default sql help

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
 
Old July 9th, 2008, 01:49 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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.
 
Old July 9th, 2008, 01:55 PM
Authorized User
 
Join Date: Mar 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old July 9th, 2008, 02:05 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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
 
Old July 10th, 2008, 06:38 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 10th, 2008, 12:53 PM
Authorized User
 
Join Date: Mar 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Code:
SELECT Count(*) as current
FRUM tbl_work
WHE.RE [2007]='Full - Time' OR [2007]='Part - Time' AND StartYear=2008 AND Starts='Fall';
Carbon_13
 
Old July 10th, 2008, 12:54 PM
Authorized User
 
Join Date: Mar 2008
Posts: 11
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.

Code:
SELECT Count(*) as current
FRUM tbl_work
WHE.RE [2007]='Full - Time' OR [2007]='Part - Time' AND StartYear=2007 AND Starts='Spring';

Carbon_13
 
Old July 10th, 2008, 01:04 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

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
 
Old July 10th, 2008, 02:10 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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:
Code:
WHERE [2007]='Full - Time' 
      OR 
      ( [2007]='Part - Time' AND StartYear=2007 AND Starts='Spring' )

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:

Code:
WHERE ( [2007]='Full - Time' OR [2007]='Part - Time' ) 
            AND StartYear=2007 
            AND Starts='Spring'


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

Code:
WHERE [2007] IN ('Full - Time', [2007]='Part - Time') 
            AND StartYear=2007 
            AND Starts='Spring'


People tend to forget about the IN operator, which makes a convenient OR-ing of values of the same field.
 
Old July 10th, 2008, 02:20 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

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:
Code:
SELECT Count(*) as current
FROM tbl_work
WHERE [2007] IN ('Full - Time', 'Part - Time') 
      AND (
               ( StartYear=2007 AND Starts='Spring' )
            OR ( StartYear=2008 AND Starts='Fall' )
          )
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:
Code:
SELECT StartYear, Starts, Count(*) as current
FROM tbl_work
WHERE [2007] IN ('Full - Time', 'Part - Time') 
      AND (
               ( StartYear=2007 AND Starts='Spring' )
            OR ( StartYear=2008 AND Starts='Fall' )
          )
GROUP BY StartYear, Starts
ORDER BY StartYear
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.





Similar Threads
Thread Thread Starter Forum Replies Last Post
How Run .sql Script file in MS SQL Server 2000? aarkaycee SQL Server 2000 5 October 12th, 2009 05:43 AM
creating ssis packagte for sql server to sql serer Laxmikant_it ASP.NET 3.5 Professionals 0 November 26th, 2008 12:23 AM
Converting from MS SQL 2005 to Sql Epress edition saif44 SQL Language 0 February 16th, 2007 04:17 PM
Failed to copy objects from SQL server to SQL Serv monfu SQL Server 2000 4 December 4th, 2005 05:54 PM
Move SQL DB from one sql to another sql server Israr SQL Server 2000 3 January 24th, 2005 02:13 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.