Share via

Why Can't I See the Table in the Query Design View?

Anonymous
2020-06-09T12:12:53+00:00

This seems to be more a puzzle than a problem.  In my database, I have four Tables, one for 2017 data, one for 2018, one for 2019, one for 2020. For each table, I have a query.  I designed the queries in the Design View of Access because I have poor knowledge of SQL.  In Design View there is a split screen normally showing the table(s) used in the query on the upper screen and the fields selected on the bottom screen.  My 2017, 2018, and 2020 queries all look like I expect them to look in the Design View.  For reasons unknown to me, in the design view the 2019 query does not show the table that it is based on in the upper screen.  (I've scrolled all around the area and cannot find it.)  This does not seem to cause a problem with the query's performance, but it certainly puzzles me why this is happening.  I've copied the SQL version of the 2019 query and the 2020 query below.  Both look the same to me except for source table and different field names; so, as I said, the 2019 query seems to be working OK.  It's just that the display in the Design View is not what I normally expect.  Any comments, suggestions as to why this is happening?  Thanks

Query based on 2019 Table:

SELECT [2019_Table].[Parcel_ID Trim], [2019_Table].[Location Trim], [2019_Table].[Owner_No1 Trim], [2019_Table].Digest_Class, [2019_Table].Total_Acres, [2019_Table].Tax_District, [2019_Table].Homestead_Exemption, [2019_Table].Improvement_Value, [2019_Table].Accessory_Value, [2019_Table].Land_Value, [2019_Table].Total_FMV, [2019_Table].NBC

FROM 2019_Table;

Query based on 2020 Table:

SELECT [2020_Table].[Parcel_No (Trimmed)], [2020_Table].[Location (Trimmed)], [2020_Table].HOUSE_NO, [2020_Table].STDIRECT, [2020_Table].[Street_Nam (Trimmed)], [2020_Table].[Owner1_Name (Trimmed)], [2020_Table].[Owner2_Name (Trimmed)], [2020_Table].ADDRESS2, [2020_Table].ADDRESS3, [2020_Table].CITY, [2020_Table].STATE, [2020_Table].ZIP, [2020_Table].DIGCLASS, [2020_Table].TOTALACRES, [2020_Table].TAXDISTRIC, [2020_Table].HOMEEXEMPT, [2020_Table].Improvement_Value, [2020_Table].FMVACC, [2020_Table].Land_Value, [2020_Table].CURR_VAL, [2020_Table].NEIGHBHOOD, [2020_Table].Conservation_Land_Value

FROM 2020_Table;

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
  1. ScottGem 68,810 Reputation points Volunteer Moderator
    2020-06-09T12:44:31+00:00

    Hi bokis, I'm an independent adviser and will try to help.

    That is unusual, but I would suspect that its just scrolled off the screen. I've had that happen and its hard to find the table. I would suggest trying to recreate the query and see if it happens again.

    I have to say however, that having separate table for each year is not good design. You should have a single table with a field that identifies what year the records are for. You can then create queries that filter for the year.

    2 people found this answer helpful.
    0 comments
Answer accepted by question author
  1. George Hepworth 22,680 Reputation points Volunteer Moderator
    2020-06-09T14:47:24+00:00

    I concur with Scott's analysis of the likely source of this "problem". It's most likely related to the display only.

    I also STRONGLY support the assertion that this is not an appropriate design for a relational database application.

    You should invest some time in the study of Normalization, or table design for relational database applications. It'll help in a number of ways, including the elimination of duplicate "Year" based tables.

    1 person found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-06-27T20:42:36+00:00

    After posting my question, I discovered this solution:  open a new query with a different but similar name, the go to the SQL view of the original query and copy the entire query into the SQL view of the new query.  Open the new query design view and the tables will no longer be scrolled off the screen.

    5 people found this answer helpful.
    0 comments
  2. Anonymous
    2020-06-27T16:00:14+00:00

    Is there a remedy when a table(s) "scroll off the screen?"

    0 comments
  3. Anonymous
    2020-06-10T21:30:47+00:00

    Thank you both for replying.  I appreciate the advice.

    I understand your comment about it being a poor database design.  I am a retired engineer who is working on this from time to time, related to trying to get some improvements in property tax exemptions where I live.  I my former work, I usually worked with a database programmer.  I no longer have that luxury; so, I struggle as best I can.  I get a new data set from the tax assessor's office once or twice a year (a snapshot of their living database).  It allows me to answer with fairly good accuracy some "what if" questions without becoming a nuisance to them.  I take the data updates as I receive them -- sometimes with mysterious changes that would make it difficult (probably impossible for me) to incorporate into one single table.  The best I can manage to do is to treat each table separately.  Then I use query's to pull out the information or comparisons that are of interest to me at the time.  It can be tedious; and it certainly is not "elegant." -- But so far it has been a useful approach for me.

    Thanks again!

    0 comments