candidate list pruning + problem with where clause with AND

OK i am having large amounts of difficulty in getting even the simplest things to work

Note this is all on SQL2000 and using Query Analyzer, and im aware of the known issues from the release notes such as that auto-popup of candidate list doesnt work in Query Analyzer


I type:

select * from <ctrl-space>

I get a list of all tables/views in the database (so far so good)

I select a table called Office from the list, and hit Enter and the table is inserted with an alias

select * from Office AS o

I then type WHERE <ctrl-space>

select * from Office AS o where <ctrl-space>

I get a list that contains lots of objects... the fields from the office table are listed at the top... then the list seems to contain all fields from other database tables and views, starting alphabetically by field name and then all other objects such as tables rows views etc. This seems a bit weird but OK, at least my office table columns are at the start.

The column i want is called OfficeNo, and as i start to type this, the list prunes. However as soon as i have typed a letter, the list is no longe rprioritised by the office columns first etc, it has reverted to a list of the whole database. I proceed with typing the first few chars of the coilumn i want, and i can see all columns/objects in the database pruning down. However when i type the next character after 'off' (eg when the screen shows 'offi'), the list is only showing the 4 tables in the database that start with offi (Office, OfficeFinance, OfficeReport and OfficeSession) but it is no long ershowing the OfficeNo, OfficeName etc fields that are in quite a few tables throughout the database - it was showing these when only 'off' had been typed in to prune the list.

So its somehow like once more than 3 chars are entered, sqlprompt wants me to put in a table rather than a column. Incidentally if i do choose Office table here and then ctrl-space to get the fields, i DONT get the fields in that table instead i get a whole list of database objects/functions with the stupid 'acud' snippet at the top etc.

BUT ANYWAY i digress - assuming i go and pick the field i wanted by scrolling down the candidate list and selecting it, rather than trying to prune the candidate list by typing the first few chars, i can then succesfully choose my field, and it is inserted with the alias etc, and i can type my where condition.

my query now looks like this

select * from Office AS o where o.OfficeNo = 790

I then type "AND <ctrl-space> and i would expect to be once again prompted with the column list from the Office table. Instead i am back to the complete list including snippets etc


What am i doing wrong? Should i be this difficult to type a select query with 2 where clauses?