Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / General 1 / June 2006

Tip: Looking for answers? Try searching our database.

Custom Sort Order - Combo Box

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
beconrad - 05 Jun 2006 23:10 GMT
Hi all,

I am not sure if what I want to do is possible, and if it is I have not
been able to figure out how to do it.  This is what I would like:

1. I have a data entry form with a field called Insurance.  That field
uses a combo box which takes its information from query on the
Insurance table.  At the moment the drop down list is sorted in
alphabetical order.

2. When a user clicks the drop-down box, I would like the first three
entries they see to represent the three most recently chosen insurance
carriers, i.e. if the last three entries chosen for the table were
carrierA, carrierB and carrieerC, those choices should appear at the
top of the list.  The remainder of the table should continue to be in
alphabetical order.

Before I go any furfher I would just like to know if this is even
possible.  

Thanks,
Bonnie
Matthias Klaey - 06 Jun 2006 00:34 GMT
>Hi all,
>
[quoted text clipped - 18 lines]
>Thanks,
>Bonnie

I assume that you have some number, say "InsuranceNr", that gives the
order of the most recent entries.

I would use three queries.
The first, called "qry_Top3", returns the mos recent 3 entries:

SELECT TOP 3 tblInsurance.Carrier
FROM tblInsurance
GROUP BY tblInsurance.Carrier
ORDER BY Max(tblInsurance.InsuranceNr) DESC;

The second query, called "qry_Rest", gives the other carriers:

SELECT DISTINCT tblInsurance.Carrier
FROM tblInsurance
LEFT JOIN qry_Top3 ON tblInsurance.Carrier = qry_Top3.Carrier
WHERE qry_Top3.Carrier Is Null
ORDER BY tblInsurance.Carrier;

The third query, used as the record source of he combo box, is the
union of the firs two:

SELEC Carrier FROM qry_Top3
UNION ALL
SELEC Carrier FROM qry_Rest;

Of course, you will have to requery the combo box on the OnCurrent
event.

HTH
Mathias Kläy
Signature

www.kcc.ch

Larry Linson - 06 Jun 2006 06:58 GMT
I believe you need to remove the Max function from the OrderBy in the first
Query.

 Larry Linson
 Microsoft Access MVP

>>Hi all,
>>
[quoted text clipped - 50 lines]
> HTH
> Mathias Kläy
Matthias Klaey - 06 Jun 2006 08:42 GMT
No, the Max funcion is essential. If you remove it, you get the error
"You tried to execute a query that does not include the specified
expression 'tblInsurance.InsuranceNr' as part of an aggregate
function".

To see how this query works, consider

SELECT TOP 3 tblInsurance.Carrier, Max(tblInsurance.InsuranceNr)
FROM tblInsurance
GROUP BY tblInsurance.Carrier
ORDER BY Max(tblInsurance.InsuranceNr) DESC;

This is the same query, except that here the Max is shown in the
output.

Greeings, Mathias

>I believe you need to remove the Max function from the OrderBy in the first
>Query.
[quoted text clipped - 56 lines]
>> HTH
>> Mathias Kläy
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.