access vs sql

[mcalex]

hi all,

what access trickery can you use to stop fields and rows appearing in the order that they should according to straight sql? I'm maintaining a database that somehow manages to have fields come out in a different order than they are SELECTed (which I'm attributing to lookup fields, but haven't investigated too far because I'm not sure whether I look for them at query definition level or whether I have to go back to the original table definitions), and now i've noticed that a query produces rows in a different order than the ORDER BY clause has requested.

Can someone provide suggestions as to how this has been done, and where I can look to fix it?

cheers

mcalex

 

[pbaldy]

If you have lookup fields, it's likely your problem:

http://www.mvps.org/access/lookupfields.htm

 

[mcalex]

hi pbaldy

yep, there is definitely lookup field 'technology' being used.

i guess the strategy from here is to go through the table definitions, removing the lookups, then run the queries to see the changes in results and then JOIN the metadata tables back into the queries?

is there a convertLookupToJoinedTable() function out there?

cheers