Microsoft Query Criteria Sort Order

[Guest]

We have users who user Microsoft Query to bring data into an Excel
spreadsheet. Sometimes they need to set criteria for a field and do so by
double clicking the value field on the query form. A dialog box appears
showing the field, operator and value. On the right hand side of this dialog
box is a box called Values. When you click this box it gives you a list of
the distinctive values of the field.

Apparently these values used to be in sorted order, but now they come in an
order that makes no sense.

Does anyone have any idea why this would change? They get similar results
calling from Oracle tables or SQL tables. I thought it might have something
to do with the sort order collation of the database. Users have indicated
that it is a must that these values be in sorted order, but I cannot figure
out a way to set the sort order on this dialog box.

Any ideas???

 

[Guest]

As far as I can see, they are in the order as per the database.....some are
alphabetical, while others are not.......I don't think you can change them
during the import.....where the users say they used to be in order, maybe
they used to use a column that was in order then but is not now, or maybe
they have changed their criteria to a column that just is NOT in
order..........

hth
Vaya con Dios,
Chuck, CABGx3

 

[Guest]

I read somewhere that SQL will select data in the most efficient method,
based on whats in cache and how the leaf level of database is read. I am
thinking how this data is displayed may even change from day to day. Say the
day after a optimization could be different then 5 days later. I found some
fields had the data in order while others didn't. I personnally don't think
it is anything we will be able to resolve without creating custom queries.

 

[Guest]

Hi gert......

I agree..........and of course for any Query that you have to do frequently,
you can write a macro in Excel to do the whole thing for you. I usually just
"record" one, and then go in and edit it as needed.........then again, if the
data return is not too great, then you can always do the filtering in
Excel......actually even as part of the same macro if you wish.......I did
one where I even constucted a Pivot Table on the imported data and then
extracted data from it to fill in other tables in Excel......and then even to
print out 30-40 combinations of charts and tables......it can really get
crazy...........

hth
Vaya con Dios,
Chuck, CABGx3