Prev: Begining asp begining
Next: IIS Randomly Hanging
From: Mary on 5 Apr 2010 17:42
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 !


From: Bob Barrows on 5 Apr 2010 19:35
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


From: Bob Barrows on 5 Apr 2010 21:11
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=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1&selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl



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


 | 
Pages: 1
Prev: Begining asp begining
Next: IIS Randomly Hanging