SQL Group By problem

Discussion in 'ASP General' started by Mary, Apr 5, 2010.

  1. Mary

    Mary Guest

    Hi All,

    I am usng ASP and Access 2003 to write the inventory, I need to generate a
    report based on an inventory datebase, please focus on GNO = G11.....

    ExDate = 2010/3/31

    GDate InvNo GNo Qty Price
    2010/3/28 1001 G11 10 30 ---------->Line No1
    2010/3/28 1001 G12 40 40
    2010/3/29 1002 G11 10 50
    2010/3/29 1002 G12 40 60
    2010/3/30 1003 G11 10 70
    2010/3/30 1003 G12 40 80

    SQL = "Select GNo, Last(Price) As LastPrice, Sum(Qty) As AAA From Inventory
    Where GDate <= #" & ExDate & "# And GNo = '" & GNo & "' Group By GNo"

    The result is below.....

    GNo rs("AAA") rs("LastPrice")
    G11 30 70
    G12 120 80

    But, after I delete Line 1 and post a new entry from a form, Access put it
    to the bottom, it become...

    GDate InvNo GNo Qty Price
    2010/3/28 1001 G12 40 40
    2010/3/29 1002 G11 10 50
    2010/3/29 1002 G12 40 60
    2010/3/30 1003 G11 10 70
    2010/3/30 1003 G12 40 80
    2010/3/28 1001 G11 10 30

    Then I run SQL, it give me a wrong result as below, I hope it can still show
    the latest price, the correct latest price of G11 is 70.....

    GNo rs("AAA") rs("LastPrice")
    G11 30 30
    G12 120 80

    Then I add " Order By InvNo" following the SQL syntax, it show error. How to
    solve this problem. Please help, thanks a lot !
     
    Mary, Apr 5, 2010
    #1
    1. Advertising

  2. Mary

    Bob Barrows Guest

    Mary wrote:
    > Hi All,
    >
    > I am usng ASP and Access 2003 to write the inventory, I need to
    > generate a report based on an inventory datebase, please focus on GNO
    > = G11.....
    >
    > ExDate = 2010/3/31
    >
    > GDate InvNo GNo Qty Price
    > 2010/3/28 1001 G11 10 30
    > ---------->Line No1 2010/3/28 1001 G12 40
    > 40
    > 2010/3/29 1002 G11 10 50
    > 2010/3/29 1002 G12 40 60
    > 2010/3/30 1003 G11 10 70
    > 2010/3/30 1003 G12 40 80
    >
    > SQL = "Select GNo, Last(Price) As LastPrice, Sum(Qty) As AAA From
    > Inventory Where GDate <= #" & ExDate & "# And GNo = '" & GNo & "'
    > Group By GNo"
    >
    > The result is below.....
    >
    > GNo rs("AAA") rs("LastPrice")
    > G11 30 70
    > G12 120 80
    >
    > But, after I delete Line 1 and post a new entry from a form, Access
    > put it to the bottom, it become...
    >
    > GDate InvNo GNo Qty Price
    > 2010/3/28 1001 G12 40 40
    > 2010/3/29 1002 G11 10 50
    > 2010/3/29 1002 G12 40 60
    > 2010/3/30 1003 G11 10 70
    > 2010/3/30 1003 G12 40 80
    > 2010/3/28 1001 G11 10 30
    >
    > Then I run SQL, it give me a wrong result as below, I hope it can
    > still show the latest price, the correct latest price of G11 is
    > 70.....
    >
    > GNo rs("AAA") rs("LastPrice")
    > G11 30 30
    > G12 120 80
    >
    > Then I add " Order By InvNo" following the SQL syntax, it show error.

    When grouping, you cannot order by a field that is not included in the
    GROUP BY clause.I'm not sure why you want to order by InvNo, when that
    column is not involved in the results.

    > How to solve this problem. Please help, thanks a lot !


    Access does not necessarily store results in the order they were
    entered. You must have a default index that is causing the results to be
    sorted in the order you are showing. You cannot depend on this - at any
    time, Access may decide to retrieve the results in a different order.
    The only way to make sure the proper order is used is to always use an
    ORDER BY clause.

    While I usually recommend against using the Last() aggregate function,
    since it appears your definition of "last" refers to the GDate column,
    you can try adding an ORDER BY clause to a subquery so that it orders by
    GDate:

    SQL = "Select GNo, Last(Price) As LastPrice, " & _
    "Sum(Qty) As AAA " & _
    "From (SELECT * FROM " & _
    "Inventory Where GDate <= #" & ExDate & "# And GNo = '" & _
    GNo & "' ORDER BY GDate) as q " & _
    "Group By GNo"


    While this might work, it will not be portable to other databases
    (should you ever decide to upsize to SQL Server perhaps). The Last()
    aggregate function is a Jet (Access) proprietary function. While it will
    complicate the query, using the MAX aggregation will be safer. Like
    this:

    Select GNo,
    (Select Price FROM Inventory WHERE GNo = i.GNo AND
    GDate=
    (Select Max(GDate) From Inventory WHERE
    GNo=i.GNo And GDate <= #" & ExDate & "# )
    ) As LastPrice
    ,...
    from Inventory As i ...

    --
    HTH,
    Bob Barrows
     
    Bob Barrows, Apr 6, 2010
    #2
    1. Advertising

  3. Mary

    Bob Barrows Guest

    Mary wrote:
    >
    > SQL = "Select GNo, Last(Price) As LastPrice, Sum(Qty) As AAA From
    > Inventory Where GDate <= #" & ExDate & "# And GNo = '" & GNo & "'
    > Group By GNo"

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, or saved parameter queries
    as
    they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=



    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
     
    Bob Barrows, Apr 6, 2010
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Akaketwa
    Replies:
    1
    Views:
    4,948
    impaler
    Sep 22, 2006
  2. Replies:
    0
    Views:
    548
  3. ecoolone
    Replies:
    0
    Views:
    782
    ecoolone
    Jan 3, 2008
  4. christopher taylor
    Replies:
    0
    Views:
    421
    christopher taylor
    Sep 17, 2008
  5. cpld-fpga-asic
    Replies:
    13
    Views:
    1,205
    rickman
    Jul 6, 2009
Loading...

Share This Page