Re: Custom Sort Order - Combo Box

"beconrad" <beconrad@xxxxxxxxx> wrote:

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

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
--
www.kcc.ch
.

Relevant Pages

  • Re: Shop Health Insurance
    ... Has the rate your shop pays for employee medical insurance risen recently? ... Our carrier raised it's rates by something like 29%. ... We dont provide benefits for our employees most are young and have insurance ...
    (alt.machines.cnc)
  • Re: Classic Car Insurance Coverage
    ... There's usually a max mileage per year limit in those classic insurance ... My carrier wanted me to put "a value" on my pre-1980 cars before they'd ... The actual price of the vehicle at the time of the loss ...
    (rec.autos.antique)
  • Re: Smashed by Royal Mail
    ... (t seems to me that much of the difficulty in this thread is because it isn't fully appreciated that my most "controversial" remarks were about private sales only). ... That assumes that if the seller offers P&P rates both with and without insurance, that the buyer opting for "without" doesn't of itself authorise the sending without insurance. ... They aren't paying more to ensure it arrives (unless they have some sort of league table for how many parcels each carrier loses on average, and by some lucky chance the more expensive carriers have a better score) what they are paying extra for is the insurance for when the carrier *does* lose or damage it. ...
    (uk.people.consumers.ebay)
  • Re: Custom Sort Order - Combo Box
    ... I have a data entry form with a field called Insurance. ... uses a combo box which takes its information from query on the ... entries they see to represent the three most recently chosen insurance ... SELEC Carrier FROM qry_Rest; ...
    (comp.databases.ms-access)
  • Re: Custom Sort Order - Combo Box
    ... "You tried to execute a query that does not include the specified ... I have a data entry form with a field called Insurance. ... entries they see to represent the three most recently chosen insurance ... SELEC Carrier FROM qry_Rest; ...
    (comp.databases.ms-access)
Loading