query question (1 Viewer)

[GMD]

Hi, I have set up a simple database and done a query. In the Data Sheet view the Fields are all showing. When I switch to the Design View and uncheck the "show" box on some of the Fields they do disappear when I switch back to the Data Sheet View. The problem is that when I go back into the Design View all the Fields that I hid now have moved to the end. They now appear at the end of all the shown Fields. If I recheck them to show again they don't go back to the same order I had when I started. Why are they moving?

thanks
GMD

 

[Minty]

It's a function of the query by example (QBE) design.

If you don't want to display a field but maybe use it as criteria (a common requirement) you can untick the box but still apply criteria against it.
This then makes your actual SQL query look something like

SELECT Field1, Field2 , Field6
FROM MyTable
WHERE Field3 = "Tom" AND Field5 = 24

So in the Access Gnomes internal brains it makes sense to move undisplayed fields to the end of the statement.
It will also can allow you to apply a sort order in the order you want without affecting the displayed order of the fields' EG;

SELECT Field1, Field2 , Field6
FROM MyTable
ORDER BY Field2 DESC, Field1 ASC

If you aren't displaying them, sorting by them or using them to apply criteria it will probably remove them when you save the query
Does that help?

 

[theDBguy]

Hi. Welcome to AWF!

I had nothing to add, just a welcome. Cheers!

 

[Pat Hartman]

QBE is a design tool. It isn't intended to be used by users. It is used by developers to build queries. So, when you uncheck a box, you are telling QBE that you no longer want to see that field so QBE removes it from the query. Users should be looking at data using forms. You can hide/show columns as needed in subforms in DS view which might be what you are looking for.

 

[GMD]

QBE is a design tool. It isn't intended to be used by users. It is used by developers to build queries. So, when you uncheck a box, you are telling QBE that you no longer want to see that field so QBE removes it from the query. Users should be looking at data using forms. You can hide/show columns as needed in subforms in DS view which might be what you are looking for.

I see also that I can switch to Data Sheet View and then I can right click on a column and select "hide". Then if I right click again I can put a check back in the column and it will show again and it does come back to the same order. Is this way of accomplishing my goal an acceptable practice?

Thanks again for all your replies

 

[theDBguy]

I see also that I can switch to Data Sheet View and then I can right click on a column and select "hide". Then if I right click again I can put a check back in the column and it will show again and it does come back to the same order. Is this way of accomplishing my goal an acceptable practice?

Thanks again for all your replies

That depends on your goal. Hiding the column is a UI aspect (user doesn't see what's there but available). Unchecking the Show box does something different - it makes the data in the column not available at all.

 

[Pat Hartman]

Is this way of accomplishing my goal an acceptable practice?

Yes BUT - only for you. Users should never see naked tables and queries because you cannot control updates to those objects. You can only control updates to forms. So, users only interact with Forms/Reports and menus.

Even for your purposes, you should be making queries with only the columns you need.

Best practice is that queries only select the columns you need regardless.

 

[MajP]

I think I know what you want. If you want a tool that does this, the following demo allows you to pick and display any query, choose the columns to hide, and pick the sort order.

 

[MajP]

If you want you can try it by ensuring you import the form and all code modules. It should not require any additional code.

Pick your query. Move fields to sort them. Hide unwanted fields.