Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Retrieve ang Display Distinct Record From A Database
- Thread starter sonper
- Start date
dougcranston
Technical User
sonper,
Can we assume you are using Access? You did not state that, however, we must assume whatever database you are using it does support SQL.
If so, your query should look like:
"SELECT DISTINCT tblStores.txtRegion FROM tblStores ORDER BY tblStores.txtRegion;" Atleast this is acceptable to Access.
From Devguru, check:
The key is to either user DISTINCT or ROWDISTINCT. See the above page for details.
Hope this helps.
DougCranston
Can we assume you are using Access? You did not state that, however, we must assume whatever database you are using it does support SQL.
If so, your query should look like:
"SELECT DISTINCT tblStores.txtRegion FROM tblStores ORDER BY tblStores.txtRegion;" Atleast this is acceptable to Access.
From Devguru, check:
The key is to either user DISTINCT or ROWDISTINCT. See the above page for details.
Hope this helps.
DougCranston
- Thread starter
- #3
Hi DougCranston! Thank you for sparing your time. Sorry, I forgot to mention the database format that I'm using. You're right! It's MS ACCESS.
I tried using your code but it gave me this error message:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/searchreso.asp, line 400
I looked at line 400, and it says:
<td><%=objRs("ResoTitle"
.Value%></td>
Correct me if I'm wrong but it appeared to me that field "ResoTitle" was not declared or defined properly in my SELECT statement.
My SELECT statement is this:
sql = "SELECT DISTINCT tblCat.ResoNum FROM tblCat "
Select Case Trim(LCase(Request("kind"))
Case "ordi": sql = sql & "ORDER BY OrdNum "
Case Else: sql = sql & "ORDER BY ResoNum "
End Select
'sql = sql & "ORDER BY tblCat.ResoNum "
'--Sort Order
Select Case Trim(LCase(Request("sortorder"))
Case "desc": sql = sql & "DESC;"
Case Else: sql = sql & "ASC;"
End Select
Any observation or correction?
I tried using your code but it gave me this error message:
Error Type:
ADODB.Recordset (0x800A0CC1)
Item cannot be found in the collection corresponding to the requested name or ordinal.
/searchreso.asp, line 400
I looked at line 400, and it says:
<td><%=objRs("ResoTitle"
.Value%></td>Correct me if I'm wrong but it appeared to me that field "ResoTitle" was not declared or defined properly in my SELECT statement.
My SELECT statement is this:
sql = "SELECT DISTINCT tblCat.ResoNum FROM tblCat "
Select Case Trim(LCase(Request("kind"
))Case "ordi": sql = sql & "ORDER BY OrdNum "
Case Else: sql = sql & "ORDER BY ResoNum "
End Select
'sql = sql & "ORDER BY tblCat.ResoNum "
'--Sort Order
Select Case Trim(LCase(Request("sortorder"
))Case "desc": sql = sql & "DESC;"
Case Else: sql = sql & "ASC;"
End Select
Any observation or correction?
dougcranston
Technical User
sonper,
Sorry, been out of town.
Apologize if my quick example through you.
You can either go SELECT * .... and it will pull all of the fields or your SELECT statement needs to specify just what fields you want pulled back and available.
Your code only asks for tblCat.ResoNum, and as such it cannot find ResoTitle
The following is a sample of an SQL statement from one of my VBScript's. It is solely to show you that you can pull more than one field but selectively indicate only those you want and need. In my case I have over 35 fields in two tables in this DB, but I am pulling only 11 fields in the order I want/need them. Also, due to the length of the query I broke it up into multiple lines and I am concantenating them together for the query.
' Define main query
sql = "SELECT DISTINCTROW tblStores.txtStoreNo, "
sql = sql & "tblStores.txtStoreRCECd, tblStores.txtRegion, "
sql = sql & "tblStores.txtStoreName, "
sql = sql & "tblStores.txtState, "
sql = sql & "tblRequests.dtRqstSubmitted, "
sql = sql & "tblRequests.dtRptDate, tblRequests.txtRptType, "
sql = sql & "LCase([txtRequestor]), "
sql = sql & "tblRequests.txtBatchNo, "
sql = sql & "LCase([txtRqstReason])"
sql = sql & "FROM tblRequests INNER JOIN tblStores "
sql = sql & "ON tblRequests.txtStoreRCECd = tblStores.txtStoreRCECd "
sql = sql & "WHERE (((tblRequests.dtRqstSubmitted) Between #" & dtFirstDayofLastMonth & "# And #" & dtLastDayofLastMonth & "#)) "
sql = sql & "AND tblStores.txtRegion = '" & rs1.Fields(0).Value & "' "
sql = sql & "ORDER BY tblStores.txtRegion, "
sql = sql & "tblStores.txtStoreNo, "
sql = sql & "tblRequests.dtRqstSubmitted;"
Hope this helps clear it up.
DougCranston
Sorry, been out of town.
Apologize if my quick example through you.
You can either go SELECT * .... and it will pull all of the fields or your SELECT statement needs to specify just what fields you want pulled back and available.
Your code only asks for tblCat.ResoNum, and as such it cannot find ResoTitle
The following is a sample of an SQL statement from one of my VBScript's. It is solely to show you that you can pull more than one field but selectively indicate only those you want and need. In my case I have over 35 fields in two tables in this DB, but I am pulling only 11 fields in the order I want/need them. Also, due to the length of the query I broke it up into multiple lines and I am concantenating them together for the query.
' Define main query
sql = "SELECT DISTINCTROW tblStores.txtStoreNo, "
sql = sql & "tblStores.txtStoreRCECd, tblStores.txtRegion, "
sql = sql & "tblStores.txtStoreName, "
sql = sql & "tblStores.txtState, "
sql = sql & "tblRequests.dtRqstSubmitted, "
sql = sql & "tblRequests.dtRptDate, tblRequests.txtRptType, "
sql = sql & "LCase([txtRequestor]), "
sql = sql & "tblRequests.txtBatchNo, "
sql = sql & "LCase([txtRqstReason])"
sql = sql & "FROM tblRequests INNER JOIN tblStores "
sql = sql & "ON tblRequests.txtStoreRCECd = tblStores.txtStoreRCECd "
sql = sql & "WHERE (((tblRequests.dtRqstSubmitted) Between #" & dtFirstDayofLastMonth & "# And #" & dtLastDayofLastMonth & "#)) "
sql = sql & "AND tblStores.txtRegion = '" & rs1.Fields(0).Value & "' "
sql = sql & "ORDER BY tblStores.txtRegion, "
sql = sql & "tblStores.txtStoreNo, "
sql = sql & "tblRequests.dtRqstSubmitted;"
Hope this helps clear it up.
DougCranston
- Thread starter
- #5
DougCranston,
I tried using the code:
sql = "SELECT DISTINCTROW tblCat.ResoNum, tblCat.CatDescription, tblCat.ResoTitle, tblCat.FileLoc FROM tblCat "
And it gave me the same result. It displayed all the records in the table including duplicates.
I tried replacing DISTINCTROW BY DISTINCT, but it gave me an error message like this:
Error Type:
Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/searchreso.asp, line 124
What does it tells me?
Thanks.
I tried using the code:
sql = "SELECT DISTINCTROW tblCat.ResoNum, tblCat.CatDescription, tblCat.ResoTitle, tblCat.FileLoc FROM tblCat "
And it gave me the same result. It displayed all the records in the table including duplicates.
I tried replacing DISTINCTROW BY DISTINCT, but it gave me an error message like this:
Error Type:
Microsoft JET Database Engine (0x80040E57)
The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.
/searchreso.asp, line 124
What does it tells me?
Thanks.
dougcranston
Technical User
sonper,
How big is your mdb?
Can you ship me a copy to look at. Willing to take a look at it, with no guarentees.
Select Distinct fieldname, fieldname, fieldname etc. will pull back UNIQUE records where ALL the requested fieldnames(contents really) are unique, not just the first one as I may have implied.
Select DistinctRow compares EVERY FIELD in a record to all others to ensure you have a unique record. If any field is slightly different it will be interpreted as a unique and distinct record.
My email address is douglas.cranston@verizon.com
Please zip it up, but don't make it a selfextracting file. Our system will not let those pass.
DougCranston
How big is your mdb?
Can you ship me a copy to look at. Willing to take a look at it, with no guarentees.
Select Distinct fieldname, fieldname, fieldname etc. will pull back UNIQUE records where ALL the requested fieldnames(contents really) are unique, not just the first one as I may have implied.
Select DistinctRow compares EVERY FIELD in a record to all others to ensure you have a unique record. If any field is slightly different it will be interpreted as a unique and distinct record.
My email address is douglas.cranston@verizon.com
Please zip it up, but don't make it a selfextracting file. Our system will not let those pass.
DougCranston
dougcranston
Technical User
sonper,
I have tried my best, and I can get close but no success, and my boss is on me for not getting my project done.
What I have done is found some links you might be able to use. Otherwise you might want to repost. Sorry I could not help more.
thread705-509618
DougCranston
I have tried my best, and I can get close but no success, and my boss is on me for not getting my project done.
What I have done is found some links you might be able to use. Otherwise you might want to repost. Sorry I could not help more.
thread705-509618
DougCranston
- Thread starter
- #8
dilettante
MIS
This kind of question may get an answer more quickly in one of the Access or SQL forums.
You'll find more people there that deal with this sort of thing more often and have broader experience with it than some of us here.
You'll find more people there that deal with this sort of thing more often and have broader experience with it than some of us here.
- Status
Similar threads
- Locked
- Question
- Locked
- Question