Query editor change

[jimatqsi]

What has happened to the Access query editor? Sometime in the last 6 months or so I lost the ability to simply assign an alias to a table in the query editor. The rectangle representing a table in the query editor now has a yellow border around it. And when I click on a table, instead of getting a property box for field list properties, I get query properties. If I can't get to field list properties I can't assign an alias unless I edit the SQL code.

I don't see this on every system, it seems to be limited to the Office 365 installations, but not all of them. Has anyone else seen this problem? Solved it?

Thanks,

Jim

[NeoPa]

Hi Jim.

It is a known issue with some releases of 365. I don't have the details to hand ATM I'm afraid but it depends on which update channel you're on. Here (Cannot change table Alias in Query Design view Access 365) is a link to a Microsoft site (Thus valid to post here for Microsoft software.) where my good friend Tom vS gives a more helpful answer.

PS. I just looked at another thread you were involved in and checked out your profile to see you started here 13 days before I did in October 2006. Cool. I think we can both be described as 'Stayers' ;-)

[jimatqsi]

Hi there. Me, before you? I'm shocked! We may both be stayers but our post counts tell a different tale. You are perhaps the most active poster on the site, and surely one of the most helpful.

About this issue, I had been delaying, as I always do, the latest Office update. Actually skipped June because it wrecked Access for one of my clients. Just updated from May to July (version 2004 to 2006) and my problem is now taken care of. Thank you for this link. I never could find a MS discussion on it.

I'm going to try to catch up to you :)

Best regards!

[NeoPa]

Indeed you were Jim.

I saw your member number and realised it was only 5 digits, and started with a 4. Mine's 5 too but starts with a 5. It must have clocked over from 4 to 5 in that fortnight many years ago.

When I saw that I checked out your profile for your start date - 6 October 2006. It was all quite different then but I'm very glad that you stuck with us and are still active and valuable even now :-)

[isladogs]

The change was an unfortunate and possibly unintended side effect of improvements to the query design window introduced in a recent A365 update (Feb 2020?). For more info on the update, see the What's New section in Access or the online version at https://support.microsoft.com/en-us/...rs=en-us&ad=us

For now, your options are to wait for MS to fix this irritation (which I believe has already happened), roll back to an earlier version of A365 or just edit your query in SQL view to add the alias.

[NeoPa]

For what it's worth, I was adding table ALIASes in QueryDefs using SQL view long before I ever realised this could also be done easily from the Properties pane within the QBE designer.

The SQL view, in combination with a powerful text editor, of which there are a few, gives you the power to make sweeping changes in just seconds that otherwise may take much longer. I'm a big fan of using SQL view with QueryDefs.

[jimatqsi]

I've toyed with writing my own query editor. Maybe a good one already exists.

[isladogs]

What features could you add that don't already exist?

In any case, as MS is in the process of making improvements to the query editor, I would wait awhile and see what they come up with first .

[jimatqsi]

I want filters by data type. I want saved templates. I want bundles (common groups that always go together, like Name, Addr1, Addr2, City, State, Zip).

Jim

[isladogs]

Him
Apologies for not replying.
However, I'm not at all clear what any of those three 'wants' actually mean. Sorry if I'm being dense but perhaps you would like to elucidate

[jimatqsi]

Filters by data type: When I'm building a query I'd like to be able to see for a moment only the date fields. I'm looking for a particular date field or I want to keep all the dates adjacent in the resulting recordset, so I want a button to limit the selected table(s) display to only the date fields. Or money or string fields beginning with 'WH' or whatever the case may be.

Templates: I'm often making queries with the same set of tables. I wish there was a subsection of queries in the database that could serve as starting points for a new query. I could easily accomplish the same thing by saving my own templates within the queries, but I wind up forgetting to do a save as new name and corrupt my templates.

Bundles: are time-saving combinations of fields within a table. I could click or drag 5 different fields to add the address fields to a query, but it would be so much nicer to be able to associate a bundle of those 5 fields with a table and then I could click the name of the bundle and bingo, I'd have the 5 fields. ItemCode, Price, Cost, Quantity, Extended Price, Extended Cost and Net would be a bundle I'd associate with many of the detail order/invoice tables in most ERPs.

I'd also add a limitation that prevents me from joining a string field to a numeric field. It's not going to execute, so why'd you let me make that join!

[isladogs]

Thanks for the explanation.
I agree completely that the query editor is long overdue for an update but the only items in your list that seem essential to me are
1. Creating 'templates' for future use. I currently do this by adding a tilde at the start ~qry... then hiding the query
2. Preventing joins between objects of different datatypes

However I have my own wish list
a) allow the query editor window to be zoomed in both design view and sql view. In fact all areas of Access should have this like Excel & Word
b) allow a query to be opened directly to SQL view without first going to design view. That would save me a lot of time as I do it repeatedly every single day
c) add colour formatting to SQL view to assist with analysis and bug fixing
d) allow full outer joins in Access queries ...OK that's not strictly a query editor issue but a wider point but it seems relevant in this context

[NeoPa]

I have very strict restrictions on what I can share but I will say keep a look-out for changes in the query editor. You are not the only people who feel it could do with a little love.

The A-Team are still working on the product. Don't believe a lot of the nonsense that's written about the demise of Access on the Desktop. Most of it comes from quarters who would like to see that, but wishing doesn't make it so. Access is still alive & kicking and looks to be continuing that way even in a world where Cloud is seen to be the be-all and end-all of computing.

Some things are just so good they won't die off.

[jimatqsi]

Amen to that! I use a lot of other stuff but I still view Access as essential.