Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-28-2009, 12:32 PM   #1
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Query Error - Can View Data But Not Enter

I am missing something incredibly basic regarding query construction and would appreciate any help that can be provided.

I have constructed a number of queries in my database, which has to do with recruiting and hiring. I can make simple queries fine and well, but once I start a chain of more than two or three tables, I get in serious trouble.

To whit: the query I've put together for our hiring process calls on eight tables, one basic personnel table, one application/consideration table, and six others representing pieces of the hiring process (in six pieces because the application could end in any one of them and I want to normalize the data). The personnel and application/consideration tables have a basic inner join between them, while the six hiring tables are connected to the application table by left outer joins wherein the application/consideration table is the left in all joins. (In case I haven't explained this properly, I've included a picture of the query.)

The problem: The query can display the data with no trouble, but when I view it, it is in some way read-only. That is, it bars entering any new data, and when I attempt to type or click on yes/no fields, I get only the Windows Exclamation sound and nothing else.

I presume I am overlooking something very basic about the structure of queries-does anyone have any idea what it is, and if so, is it fixable?
Attached Images
File Type: jpg hiring query.jpg (95.4 KB, 65 views)

Jared is offline   Reply With Quote
Old 07-28-2009, 01:04 PM   #2
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query Error - Can View Data But Not Enter

Well, first thing up is that ApplicationID should be the PRIMARY KEY in the tblInterviewConsideration table but NOT in the others. It should be a FOREIGN KEY in the other tables. The other tables should have their own primary keys.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-28-2009, 01:05 PM   #3
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query Error - Can View Data But Not Enter

Also, do not use special characters in the field names (do not use ? in them).

__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-28-2009, 01:39 PM   #4
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

I have made those changes, but the query still blocks data entry, as does the form based on the query.

I understand that I have very limited expertise with Access, and there are undoubtedly a number of ways in which my database can be cleaned up, but I have under two days to get it ready for presentation, so I would appreciate targeted advice.

(I appreciate other advice, too, don't get me wrong. I have no desire to look like I'm irked at somebody trying to help me. I just don't have time to implement it and make it perfect right now.)
Jared is offline   Reply With Quote
Old 07-28-2009, 01:43 PM   #5
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query Error - Can View Data But Not Enter

Quote:
Originally Posted by Jared View Post
I just don't have time to implement it and make it perfect right now.)
Not trying to get it "perfect" but trying to get it at least close to correct. Your current structure is causing you your problems. Your problem will not be fixed until you fix the underlying structure problem (get it?).

Perhaps you can clear the data out of it and post the database here.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-28-2009, 01:46 PM   #6
Access Hero
Registered User
 
Join Date: Oct 2008
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Access Hero is on a distinguished road
Re: Query Error - Can View Data But Not Enter

If you are doing data entry on multiple joined tables, you should be using sub-forms for related tables. When queries become complex, they become read-only. See http://allenbrowne.com/ser-61.html for more details.
Access Hero is offline   Reply With Quote
Old 07-28-2009, 01:55 PM   #7
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

Access Hero - It looks like mine falls under "It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables."

I can build subforms to allow the data entry. It's just going to take a lot more work. I had thought that the structure error was entirely on my part, because I thought that this would be a relatively common kind of query needed by end-users, but this appears to simply be something that Access can't do. (Which fairly baffles me, since I assume there must be a tremendous number of complex processes that might be captured in a database...)

Jared is offline   Reply With Quote
Old 07-28-2009, 01:57 PM   #8
Kryst51
Singin' in the Hou. Rain
 
Kryst51's Avatar
 
Join Date: Jun 2009
Location: Texas
Posts: 1,898
Thanks: 0
Thanked 1 Time in 1 Post
Kryst51 will become famous soon enough Kryst51 will become famous soon enough
Re: Query Error - Can View Data But Not Enter

Quote:
Perhaps you can clear the data out of it and post the database here.
This is still probably a good idea, even if you think you know what your problem is Bob knows a lot as does Access Hero, they would probably be able to make more specific suggestions to help you reach your goal quicker if they could see it.
Kryst51 is offline   Reply With Quote
Old 07-28-2009, 01:58 PM   #9
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

I will try to post a stripped copy within the next half-hour.
Jared is offline   Reply With Quote
Old 07-28-2009, 02:03 PM   #10
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query Error - Can View Data But Not Enter

If you want a quick way to "clear the database" of data. You might want to use my free reset tool.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-28-2009, 02:33 PM   #11
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

Larson - Thanks, but I was trying to clear all the data except a few records that might provide relevant examples.

Thanks for your patience. I am really hoping there is another way of doing this than a half-dozen more subforms. I can do it, but that'll be...inelegant, let's say.

Last edited by Jared; 07-29-2009 at 10:57 AM. Reason: Wanted to remove db copy.
Jared is offline   Reply With Quote
Old 07-28-2009, 04:47 PM   #12
boblarson
Smeghead
 
boblarson's Avatar
 
Join Date: Jan 2001
Location: Oregon, USA
Posts: 32,068
Thanks: 97
Thanked 1,841 Times in 1,587 Posts
boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold boblarson is a splendid one to behold
Re: Query Error - Can View Data But Not Enter

Quote:
Originally Posted by Jared View Post
Larson - Thanks, but I was trying to clear all the data except a few records that might provide relevant examples.

Thanks for your patience. I am really hoping there is another way of doing this than a half-dozen more subforms. I can do it, but that'll be...inelegant, let's say.
Subforms are not inelegant. They are a backbone of a one-to-many or many-to-many relationship. And I'm afraid that's what you are going to need to do; for at least a part of it.

Also for your updateable queries don't try to put in the person's name as a concatenated field. That will also render it non updateable. For your main form you can get by with a combo box that has the ID as the bound field but then in the combo's row source you can do the concatenated name. So keep the tblParticipants out.
__________________

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
boblarson is offline   Reply With Quote
Old 07-29-2009, 03:26 AM   #13
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

I was referring to them as inelegant because I thought using several of them was a brute-force method of doing what could be done more simply with a single form built on a properly constructed query.

(In the training courses I've taken, I was told that most forms are built on queries and warned heavily against overreliance on subforms. Perhaps that was the personal taste of the instructor?)
Jared is offline   Reply With Quote
Old 07-29-2009, 08:06 AM   #14
Access Hero
Registered User
 
Join Date: Oct 2008
Posts: 96
Thanks: 0
Thanked 0 Times in 0 Posts
Access Hero is on a distinguished road
Re: Query Error - Can View Data But Not Enter

There are a lot of people with tons of Access experience who tell you to do the wrong things. I think if you'll follow the writings of Access MVPs, and Access forum VIPs, you'll find that they all agree to normalize and use sub-forms as appropriate. These guys (including Bob) are well respected by the Microsoft and forum communities for a reason.
Access Hero is offline   Reply With Quote
Old 07-29-2009, 10:56 AM   #15
Jared
Newly Registered User
 
Join Date: Sep 2008
Posts: 21
Thanks: 1
Thanked 1 Time in 1 Post
Jared is on a distinguished road
Re: Query Error - Can View Data But Not Enter

To not let this be one of those threads that peters off into nonexistence with no clear resolution: I simplified the query (extremely) and built the necessary subforms, and the new form works, to the great delight of all in my office who wouldn't like to watch me hyperventilate myself to death.

Bob - I don't understand your reference to concatenation causing queries to no longer be updateable. My new query includes the concatenated field and does allow updating. Flip side: the last heartstopping moment I had building the new form was when it didn't work due to a glitch caused by those stupidly-named fields with the question marks, so you definitely pointed me in the right direction there. I had known I should fix that, but was putting it off as a minor housekeeping issue.

Thanks to everybody for helping me out here.

Jared is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Adding a Auto increment Text/Number elliotth123 Tables 13 05-17-2013 08:30 AM
Update data on a query based Form kzmbj0 Forms 3 12-14-2012 03:01 PM
MS Access data in Excel: Divide a single query but keep data link rbrady Excel 13 02-28-2008 11:14 AM
Pulling data from several identical tables and returning the results to one big query morlan Queries 5 01-23-2007 05:17 AM




All times are GMT -8. The time now is 07:53 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World