Queries & Missing Fields

Question

Unfortunately I'm an Access novice and recently my company upgraded from an ancient copy of Office '97 to Office 2010 with no training and I'm more than a bit lost here and could use some help...

#1) In older versions of Access I found it very easy to run queries. We have a set of simple tables to keep monthly stats, basically lists of groups and attendance and revenues. Very basic stuff - there's nothing fancy about these, they are not linked to anything. We have a set of simple queries that count the number of programs, totals attendance and revenue etc.

In the new version of Access I have been very lost - it seems overly complicated, almost baffling, to run a query. The only way I seem to be able to do it is to use the Simple Query Wizard, picking the Available Fields and running the query. This has been working, but the down side is that every time I run one it makes a duplicate and now I have dozens of copies of the same query!

#2) Now here comes the weird thing - a co-worker copied the database and started filling in monthly stats into tables for 2016. But now the queries are all empty! All of the "Available Fields" are gone and so I can't even run a simple query now. Even carefully making a copy of the old database and importing the new tables into it still doesn't work - all the Available Fields disappear.

So I'm looking for some suggestions as to how to get those Available Fields back. TIA!

Answer 1

Like I said the nav pane is very similar to the old Database window. Just takes a little to get used to it.

However, if you are going to have multiple people working on this database, then it NEEDS to be split between a front end and a back end (Just the tables). 

And users need to know NOT to just rename or add tables as this affects others.

Answer 2

Ah...son of a gun. Yes I see what you mean. I guess when my co-worker copied the file they changed slightly the names of tables not realizing that would "break" the existing queries!

One of my big mistakes is not using the "Nav panel" properly to display the queries. When I choose the "Datasheet View" and see the results I'm looking for (I can also delete all those darned extra copies of the queries I created!!)

Thanks very much for your help!!

Answer 3

Good question I will try that.

No the tables are not linked...all local to the same database file.

Answer 4

First, In older versions of Access you ran queries from the Database window. Each type of object had its own tab. You selected the object you wanted and double clicked it. Access 2010 works VERY similarly. instead of the Database Window you have a navigation Pane (Nav Pane for short). If you right click in the title bar you can select to Show all Groups and sort by group. Each group corresponds to a tab in the old Database window. Select the object and double click to run it. 

Second, If your co-worker made a copy of the database are you working with that copy? Unless the database was setup to share the data tables they are in separate files so you would not see what he did in your copy of the database. Even if your are working on the same file. You said: " into tables for 2016" were separate tables created for 2016? If so, then you have to rewrite the queries using those tables. 

To see what tables and columns are in a query you right click on the query in the Nav Pane and select Design Mode.

Answer 5

Hi. Does this happen to all computers in the workplace or just in yours? Are the tables linked to an external database file or are they local to the same database file?