Custom Sort Order - Combo Box

[beconrad]

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]

"beconrad" <be******@yahoo.com> 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

[Larry Linson]

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

Larry Linson
Microsoft Access MVP
"Matthias Klaey" <mp**@hotmail.com> wrote in message
news:1c********************************@4ax.com...

"beconrad" <be******@yahoo.com> 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

[Matthias Klaey]

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

"Larry Linson" <bo*****@localhost.not> wrote:

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

Larry Linson
Microsoft Access MVP
"Matthias Klaey" <mp**@hotmail.com> wrote in message
news:1c********************************@4ax.com.. .

"beconrad" <be******@yahoo.com> 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