Filtering using combo

[Alex]

Acc97.

I have a form which within a subform records machine down time. I have
a total of 8 machines. (1-2-3 ect.) What I have been doing is using a
combo box linked to the record table to select, the reasons based upon
generic terms.

The problem is that I have so many records now, that it takes more
time looking and picking out the correct fault reason than it does
completing the rest of the form!

so I need to speed up the whole process of selecting the applicable
reasons for down time.

I would like to do the following..

1) based upon the machine I selection the main form. say using a combo
box..
list the most occuring reasons in priority without limiting my
options.

therefore.. when I open the combo box for machine "A" in the down time
combo the first options visible in order of occurrence are the ones
which have historically been most applicable to machine "A" but also
not exclude the remaining generic reasons.

If some kind person would show me how to do this in the simplest way
possible.

Thanks,

Alex

[Allen Browne]

You could dynamically bring the most frequent reasons to the top by setting
the combo's RowSource to something like this:
SELECT ReasonID, Reason
FROM MyTable
GROUP BY ReasonID
ORDER BY Count(ID) DESC;

If you really want to show different reasons for each machine based on its
history, you could redesign the query above to do that, and assign it to the
RowSource of the combo in its Enter event.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Alex" <ge****@yas1.freeserve.co.uk> wrote in message
news:b7**************************@posting.google.c om...

Acc97.

I have a form which within a subform records machine down time. I have
a total of 8 machines. (1-2-3 ect.) What I have been doing is using a
combo box linked to the record table to select, the reasons based upon
generic terms.

The problem is that I have so many records now, that it takes more
time looking and picking out the correct fault reason than it does
completing the rest of the form!

so I need to speed up the whole process of selecting the applicable
reasons for down time.

I would like to do the following..

1) based upon the machine I selection the main form. say using a combo
box..
list the most occuring reasons in priority without limiting my
options.

therefore.. when I open the combo box for machine "A" in the down time
combo the first options visible in order of occurrence are the ones
which have historically been most applicable to machine "A" but also
not exclude the remaining generic reasons.

If some kind person would show me how to do this in the simplest way
possible.