Page 1 of 3 123 LastLast
Results 1 to 15 of 32
  1. #1
    Join Date
    Jan 2005
    Posts
    37

    Unanswered: Can anyone help me with Access database

    I currently trying to write a sql function to calculate the sum of a field. My objective is to help a company input all the income into database then they can check the total income by just go to this page and is will shoe the total income, expenditure and profit.

    Therefore i ask around and found this code:
    <%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL= "SELECT SUM(Income) AS [strSQL1], * FROM FUND"
    Set RS = MyConn.Execute(strSQL)
    If RS.EOF = false then
    response.write RS("strSLQ1")
    End if
    RS.close
    set RS=nothing
    MyConn.close%>

    But it gave me this error:
    Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

    [Microsoft][ODBC Microsoft Access Driver] Cannot group on fields selected with '*'.

    /i-Metz/iMetzAdminFund.asp, line 123

    line 123 is this code -->> Set RS = MyConn.Execute(strSQL)

    1) Anyone have any ideas what wwrong with the code???

    2) And how to write the code for the profit???

    Thanks alot

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving to Access forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    WHAT!!!
    a moderator on this site who moderates!

    izy
    currently using SS 2008R2

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    "SELECT SUM(Income) AS [strSQL1], * FROM FUND"

    Including the * in your query returns all the colums from the table. First, you need to specify the columns you want to group by, rather than use *. Then you need to tell the database engine that you want to find the SUM of Income for each unique combination of values in the grouping fields:

    Code:
    SELECT	Column1,
    	Column2,
    	etc...
    	SUM(Income) AS [strSQL1]
    FROM	FUND
    GROUP BY Column1,
    	Column2,
    	etc...
    If you just want the Sum of Income for the entire table, your query can be simplified to:

    Code:
    SELECT	SUM(Income) AS [strSQL1]
    FROM	FUND
    No GROUP BY clause is require in this special instance.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jan 2005
    Posts
    37
    Quote Originally Posted by blindman
    "SELECT SUM(Income) AS [strSQL1], * FROM FUND"

    Including the * in your query returns all the colums from the table. First, you need to specify the columns you want to group by, rather than use *. Then you need to tell the database engine that you want to find the SUM of Income for each unique combination of values in the grouping fields:

    Code:
    SELECT	Column1,
    	Column2,
    	etc...
    	SUM(Income) AS [strSQL1]
    FROM	FUND
    GROUP BY Column1,
    	Column2,
    	etc...
    If you just want the Sum of Income for the entire table, your query can be simplified to:

    Code:
    SELECT	SUM(Income) AS [strSQL1]
    FROM	FUND
    No GROUP BY clause is require in this special instance.
    Sorry i am a newbie in SQL, can tell me how can i carry from here, any example
    E.g. If my Income field is in the 2nd column and my expenditure is in 4 column,
    What should i do?

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use the actual names of your fields, not "Column1", "Column2"...

    That was just for example.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2005
    Posts
    37
    This function do i need to execute it?? Like in my previous code
    Set RS = MyConn.Execute(strSQL)

  8. #8
    Join Date
    Jan 2005
    Posts
    37
    Can i write it like this?
    <%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL="SELECT Income, Expenditure
    SUM(Income) AS [strSQL1]
    FROM FUND
    GROUP BY Income, Expenditure"
    Set RS = MyConn.Execute(strSQL)
    If RS.EOF = false then
    response.write RS("strSLQ1")
    End if
    RS.close
    set RS=nothing
    MyConn.close%>

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Did you try it? Did you get an error message?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  10. #10
    Join Date
    Jan 2005
    Posts
    37
    Try, cannot. it gave me this:
    Cannot Display Page

    Error Type:
    Microsoft VBScript compilation (0x800A0409)
    Unterminated string constant
    /i-Metz/iMetzAdminFund.asp, line 122, column 34
    strSQL="SELECT Income, Expenditure

    Line 122-->strSQL="SELECT Income, Expenditure
    SUM(Income) AS [strSQL1]
    FROM FUND
    GROUP BY Income, Expenditure"

    Antone can tell me how to integrate this code into my own code?

  11. #11
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    This sounds more like a VBScript issue, than an Access issue. In Access VBA, one would either concatenate the string

    strSQL="SELECT Income, Expenditure, " & _
    "SUM(Income) AS [strSQL1] " & _
    "FROM FUND " & _
    "GROUP BY Income, Expenditure"

    or put it on one line

    strSQL="SELECT Income, Expenditure, SUM(Income) AS [strSQL1] FROM FUND GROUP BY Income, Expenditure"

    Seems to be a missing comma after Expenditure
    Roy-Vidar

  12. #12
    Join Date
    Jan 2005
    Posts
    37
    I heeded RoVidar advice and change my code to this:
    Income:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;<%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL="SELECT Income, SUM(Income) AS [strSQL1] FROM FUND GROUP BY Income;"
    Set RS = MyConn.Execute(strSQL)
    response.write RS("strSQL1")
    RS.close
    set RS=nothing
    MyConn.close%></p>
    <p>Expenditure:&nbsp;<%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL="SELECT Expenditure, SUM(Expenditure) AS [strSQL2] FROM FUND GROUP BY Expenditure;"
    Set RS = MyConn.Execute(strSQL)
    response.write RS("strSQL2")
    RS.close
    set RS=nothing
    MyConn.close%></p>

    The error is gone but the value it calculated is all wrong:
    E.g My Income should have $83,$25,$30 in the field but it just show $25 after the sum. Then my Expenditure i put in some value, it just shoe $0. Anyone know why is it like that?

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think you want to GROUP BY at all, if all you are looking for is a single SUM for the entire table.

    And a statement like this:
    SELECT Income, SUM(Income) AS [strSQL1] FROM FUND GROUP BY Income;
    ...is not going to give you what you want because you are summing and grouping by the same column. I highly doubt that is what you want...

    Try substituting these two statements:
    strSQL="SELECT SUM(Income) AS [strSQL1] FROM FUND;"

    ...and:
    strSQL="SELECT SUM(Expenditure) AS [strSQL2] FROM FUND;"
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Jan 2005
    Posts
    37
    Yes that what i want. Thanks alot
    Btw how do you do the turnover for my fund(meaning the difference between my income and expenditure)?

  15. #15
    Join Date
    Jan 2005
    Posts
    37
    Can i do this for the difference:

    Income:&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;& nbsp;&nbsp;&nbsp;<%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL="SELECT Income, SUM(Income) AS [strSQL1] FROM FUND GROUP BY Income;"
    Set RS = MyConn.Execute(strSQL)
    response.write RS("strSQL1")
    RS.close
    set RS=nothing
    MyConn.close%></p>
    <p>Expenditure:&nbsp;<%Set MyConn = Server.CreateObject("ADODB.Connection")
    MdbFilePath = Server.MapPath("Admin.mdb")
    MyConn.Open "UID=Admin;PWD=123456;Driver={Microsoft Access Driver (*.mdb)}; DBQ=" & MdbFilePath & ";"
    strSQL="SELECT Expenditure, SUM(Expenditure) AS [strSQL2] FROM FUND GROUP BY Expenditure;"
    Set RS = MyConn.Execute(strSQL)
    response.write RS("strSQL2")
    strSQL3=strSQL1-strSQL2
    response.write RS("strSQL3")
    Set
    RS.close
    set RS=nothing
    MyConn.close RS = MyConn.Execute(strSQL)%>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •