SQL Group By problem

[Mary]

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 !

 

[Bob Barrows]

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 ...

 

[Bob Barrows]

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&[email protected]

http://groups.google.com/groups?hl=...=1&[email protected]