What is the query syntax in ASP code?
In the section of ASP code below, I want to modify the query text: "SELECT * FROM qrySearchSerialNumberList WHERE (User = '::txtUser::' AND Item = '::txtItem::')" to find the record with the greatest/maximum AsOf Date but am unsure of the syntax. The current query works but returns all records where the User, Item, and Serial Number (SN) match.
Thus I need:
1. A reference or tutorial for ASP query syntax so I can understand the section of ASP code below.
2. To modify the ASP query text to obtain the record with the greatest AsOf (it's a Date) given the User (Customer Number), Item (Model Number), and Serial Number (of the item/Model).
If I were writing the query in VBA for Microsoft Access, it would look like:
SELECT DISTINCT qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN, Max(qrySearchForm.AsOf) AS MaxOfAsOf
FROM qrySearchForm
GROUP BY qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN
HAVING (((qrySearchForm.User)=[tx
Thanks, the ASP code follows:
<%
fp_sQry="SELECT * FROM qrySearchSerialNumberList WHERE (User = '::txtUser::' AND Item = '::txtItem::')"
fp_sDefault="txtUser=&txtI
fp_sNoRecords="No Serial Numbers available for Item."
fp_sDataConn="Database1"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="SN"
fp_sMenuValue="SN"
fp_sColTypes="&User=202&It
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
Thus I need:
1. A reference or tutorial for ASP query syntax so I can understand the section of ASP code below.
2. To modify the ASP query text to obtain the record with the greatest AsOf (it's a Date) given the User (Customer Number), Item (Model Number), and Serial Number (of the item/Model).
If I were writing the query in VBA for Microsoft Access, it would look like:
SELECT DISTINCT qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN, Max(qrySearchForm.AsOf) AS MaxOfAsOf
FROM qrySearchForm
GROUP BY qrySearchForm.User, qrySearchForm.Item, qrySearchForm.SN
HAVING (((qrySearchForm.User)=[tx
Thanks, the ASP code follows:
<%
fp_sQry="SELECT * FROM qrySearchSerialNumberList WHERE (User = '::txtUser::' AND Item = '::txtItem::')"
fp_sDefault="txtUser=&txtI
fp_sNoRecords="No Serial Numbers available for Item."
fp_sDataConn="Database1"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="SN"
fp_sMenuValue="SN"
fp_sColTypes="&User=202&It
fp_iDisplayCols=3
fp_fCustomQuery=False
BOTID=1
fp_iRegion=BOTID
%>
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Question 1: Can we then assume then that this is for an MS Access database and the the SQL syntax is the MS Access dialect?
Answer: Yes
Question 2:
Do you need:
A. The greatest AsOf for each User, Item and Serial Number ? (resulting in one or more rows for each User, Item, Serial Number)
B. The greatest AsOf for all? (just one row total).
Answer: I need the greatest AsOf for the User (its actually a Number like "U1234" assigned to an Organization--sometimes they change their name) given the Item (Model Number) and Serial Number.
Details on the semantic nightmare I inherited: The User (an Organization) is determined in the login.asp page after a person enters their UserNumber and Password. The next page, SearchFormItem.asp, now knows the User's (an organization) ID number. This page is where a person selects an Item (Model Number) that the User sent in for repair. After a person selects an Item (Model Number), the SearchFormSN.asp, opens. This form now knows the User's (an organization) ID number and the Item (model number) they sent in for repair. This page is where a person selects the Serial Number of an Item sent in for repair. Since many items have been sent in more than once, the page lists the latest repair data. This repair data is the transaction with the latest AsOf date. A transaction may be the event when it was received for repair or the event when it was shipped back to the User (an organization). After a person selects a serial number, the SearchFormList.asp page appears and lists information like the date, comments, FedEx tracking number, etc.
The following line of code works fine.:
fp_sQry="SELECT TOP 1 * FROM qrySearchSerialNumberList WHERE (User = '::txtUser::' AND Item = '::txtItem::') ORDER BY AsOf DESC"
Answer: Yes
Question 2:
Do you need:
A. The greatest AsOf for each User, Item and Serial Number ? (resulting in one or more rows for each User, Item, Serial Number)
B. The greatest AsOf for all? (just one row total).
Answer: I need the greatest AsOf for the User (its actually a Number like "U1234" assigned to an Organization--sometimes they change their name) given the Item (Model Number) and Serial Number.
Details on the semantic nightmare I inherited: The User (an Organization) is determined in the login.asp page after a person enters their UserNumber and Password. The next page, SearchFormItem.asp, now knows the User's (an organization) ID number. This page is where a person selects an Item (Model Number) that the User sent in for repair. After a person selects an Item (Model Number), the SearchFormSN.asp, opens. This form now knows the User's (an organization) ID number and the Item (model number) they sent in for repair. This page is where a person selects the Serial Number of an Item sent in for repair. Since many items have been sent in more than once, the page lists the latest repair data. This repair data is the transaction with the latest AsOf date. A transaction may be the event when it was received for repair or the event when it was shipped back to the User (an organization). After a person selects a serial number, the SearchFormList.asp page appears and lists information like the date, comments, FedEx tracking number, etc.
The following line of code works fine.:
fp_sQry="SELECT TOP 1 * FROM qrySearchSerialNumberList WHERE (User = '::txtUser::' AND Item = '::txtItem::') ORDER BY AsOf DESC"
>>The following line of code works fine.<<
Great! Since you have received a solution, please close the question. Here's how:
How do I accept a comment?
https://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi68
Great! Since you have received a solution, please close the question. Here's how:
How do I accept a comment?
https://www.experts-exchange.com/Web/Web_Languages/ASP/help.jsp#hi68
Let's see, doing a group by on 3 fields on a table containing say 1mil rows and then narrowing it down to a few less with user, item and SN vs narrowing down to start with and then from that list, picking up the latest row...
Assuming no indexes anywhere, or an index on User at least, I bet the simplest query is faster.
Besides, how can you teach someone SQL with queries like that?
adilkhan, please explain why you think it's faster and/or better.
Thanks.