Using Database Results Wizard for dropdown

[Rich Palarea]

I'm using the Database Results Wizard to populate a dropdown list in a form.
In this case, the field I'm pulling from is a city (text). At present, I'm
getting every record in the dropdown. I only want the unique records. For
example, if Los Angeles is listed in 200 records, I don't want 200 choices
for Los Angeles in the dropdown...only one.

Is there a parameter you can add to the sql of the Database Results Wizard
or elsewhere in the .asp page that will only return the first value and skip
the rest?

Thanks in advance.
Rich

 

[MD Websunlimited]

Hi Rich,

You'll have to use the verb Distinct (assuming a Access DB)

SELECT Distinct orders.City
FROM orders;

--
Mike -- FrontPage MVP '97-'02
http://www.websunlimited.com
J-Bots Plus 2002 87 components for FrontPage
http://www.websunlimited.com/order/Product/JBP2002/jbp_help_dir.htm
FrontPage Add-ins Since '97 2003 / 2002 / 2000 Compatible

 

[Rich Palarea]

Mike -

Works perfectly. Thanks for the fast response, as always!
Rich

 

[Rich Palarea]

A followup question (sorry about re-using the original post):
The distinct function works well. I don't want to limit the chance of returning a record, so I'd like to offer the user the chance to select all records, in addition to being able to select the preferences they want (i.e.: no preference). To give you a better idea of how the sql is structured (MS Access db with .asp):

User can choose to find a group by three fields: GroupMix, MeetingDay, MeetingLocation from the table Smallgroups. All are dropdown fields in a form. GroupMix and MeetingLocation are populated by a SELECT statement to the Smallgroups table. MeetingDay is pre-populated with days of the week.

It is easy to get "no records found" with this setup, because of the limited number of records in the table at present. I would like the user to be allowed the choices of "no preference" inside of the three drop down fields.

Present SQL for the form (showing just the dropdown portion for GroupMix; there is a similar select for MeetingLocation further down in my page):

<% end if %>
<%
fp_sQry="SELECT Distinct Smallgroups.GroupMix FROM Smallgroups ORDER BY GroupMix ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="GroupMix"
fp_sMenuValue="GroupMix"
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&ContactHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

Present SQL for the results page:
<% end if %>
<%
fp_sQry="SELECT * FROM Smallgroups WHERE (status = 'open' AND MeetingDay = '::MeetingDay::' AND GroupMix = '::GroupMix::' AND MeetingLocation = '::MeetingLocation::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&ContactHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=11
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

Present working page: http://smallgroups.gfc.org/IDsite/locator.asp

Thanks!
Rich

 

[Rich Palarea]

Just in case, I will re-post with a new subject line...
A followup question (sorry about re-using the original post):
The distinct function works well. I don't want to limit the chance of returning a record, so I'd like to offer the user the chance to select all records, in addition to being able to select the preferences they want (i.e.: no preference). To give you a better idea of how the sql is structured (MS Access db with .asp):

User can choose to find a group by three fields: GroupMix, MeetingDay, MeetingLocation from the table Smallgroups. All are dropdown fields in a form. GroupMix and MeetingLocation are populated by a SELECT statement to the Smallgroups table. MeetingDay is pre-populated with days of the week.

It is easy to get "no records found" with this setup, because of the limited number of records in the table at present. I would like the user to be allowed the choices of "no preference" inside of the three drop down fields.

Present SQL for the form (showing just the dropdown portion for GroupMix; there is a similar select for MeetingLocation further down in my page):

<% end if %>
<%
fp_sQry="SELECT Distinct Smallgroups.GroupMix FROM Smallgroups ORDER BY GroupMix ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="GroupMix"
fp_sMenuValue="GroupMix"
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&ContactHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

Present SQL for the results page:
<% end if %>
<%
fp_sQry="SELECT * FROM Smallgroups WHERE (status = 'open' AND MeetingDay = '::MeetingDay::' AND GroupMix = '::GroupMix::' AND MeetingLocation = '::MeetingLocation::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&ContactHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=11
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

Present working page: http://smallgroups.gfc.org/IDsite/locator.asp

Thanks!
Rich

 

[Eve]

Rich:

Make sure you choose Custom Query within the Database
Results Wizard and then type in this SQL statement
(substitute your table for Department and your field name
for DepartmentName):

SELECT DISTINCT * FROM Department ORDER BY DepartmentName
ASC

I hope this helps.

 

[Rich Palarea]

Eve - that works great!

I'm hoping, now, to find an answer to this dilemma:

(repost from above):

A followup question (sorry about re-using the original post):
The distinct function works well. I don't want to limit the chance of
returning a record, so I'd like to offer the user the chance to select all
records, in addition to being able to select the preferences they want
(i.e.: no preference). To give you a better idea of how the sql is
structured (MS Access db with .asp):

User can choose to find a group by three fields: GroupMix, MeetingDay,
MeetingLocation from the table Smallgroups. All are dropdown fields in a
form. GroupMix and MeetingLocation are populated by a SELECT statement to
the Smallgroups table. MeetingDay is pre-populated with days of the week.

It is easy to get "no records found" with this setup, because of the limited
number of records in the table at present. I would like the user to be
allowed the choices of "no preference" inside of the three drop down fields.

Present SQL for the form (showing just the dropdown portion for GroupMix;
there is a similar select for MeetingLocation further down in my page):

<% end if %>
<%
fp_sQry="SELECT Distinct Smallgroups.GroupMix FROM Smallgroups ORDER BY
GroupMix ASC"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=True
fp_sMenuChoice="GroupMix"
fp_sMenuValue="GroupMix"
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=
202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&Conta
ctHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>

Present SQL for the results page:
<% end if %>
<%
fp_sQry="SELECT * FROM Smallgroups WHERE (status = 'open' AND MeetingDay =
'::MeetingDay::' AND GroupMix = '::GroupMix::' AND MeetingLocation =
'::MeetingLocation::')"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="GFCData"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&OrgID=3&Name=202&MeetingDay=202&MeetingLocation=202&GroupMix=
202&MeetingTime=202&ChildrenAllowed=202&Status=202&ContactIndLabel=202&Conta
ctHomePhone=202&ContactIndvEmail=202&"
fp_iDisplayCols=11
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>

Present working page: http://smallgroups.gfc.org/IDsite/locator.asp

Thanks!
Rich