Re: Custom Sort Order - Combo Box

I believe you need to remove the Max function from the OrderBy in the first
Query.

Larry Linson
Microsoft Access MVP


"Matthias Klaey" <mpky@xxxxxxxxxxx> wrote in message
news:1cf9821ttlr7d2jlso8v4sf4dejmqhqt0f@xxxxxxxxxx
"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: Custom Sort Order - Combo Box
    ... 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)
  • 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)
  • RE: Obtain total without dups
    ... What you say below will give me all the members for that Insurance Type. ... of course I have a date peramiters in my qorginal query. ... adding it to the original query that created the report, ...
    (microsoft.public.access.reports)
  • Re: Creating query based on no-match
    ... The second will show all Total records that have no match in Cost_Service. ... CLAIMS INNER JOIN INSURANCE ON CLAIMS.Provider = Insurance.Provider ... ... --Switch back to Design view ... Run the query and see if you get the results you want. ...
    (microsoft.public.access.queries)
  • Re: Repeating records based on a number in a field
    ... I agree with what Tom Ellison is suggesting, but I thought a numerical example might also be helpful. ... The Query to give you the list you want will contain a filter that will let it produce ONLY the records where the number of the copy is no more than than the field. ... But the filter hides all but those with a small enough part, so you get only 6 from the first record and 2 from the other one. ... simple, I have a Zipcode field, a Carrier Route field, and a Copies field. ...
    (microsoft.public.access.queries)
Loading