Query Error - Can View Data But Not Enter (1 Viewer)

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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?
 

Attachments

  • hiring query.jpg
    hiring query.jpg
    95.4 KB · Views: 103

boblarson

Smeghead
Local time
Today, 04:52
Joined
Jan 12, 2001
Messages
32,059
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.
 

boblarson

Smeghead
Local time
Today, 04:52
Joined
Jan 12, 2001
Messages
32,059
Also, do not use special characters in the field names (do not use ? in them).
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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.)
 

boblarson

Smeghead
Local time
Today, 04:52
Joined
Jan 12, 2001
Messages
32,059
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.
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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...)
 

Kryst51

Singin' in the Hou. Rain
Local time
Today, 06:52
Joined
Jun 29, 2009
Messages
1,898
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. :)
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
I will try to post a stripped copy within the next half-hour.
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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:

boblarson

Smeghead
Local time
Today, 04:52
Joined
Jan 12, 2001
Messages
32,059
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.
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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?)
 

Access Hero

Registered User.
Local time
Today, 06:52
Joined
Oct 29, 2008
Messages
96
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.
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
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

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
Yep. Again, thanks to everyone for the help.
 

Jared

Registered User.
Local time
Today, 07:52
Joined
Sep 10, 2008
Messages
21
Oh, I'm a not-IT person sitting in my cube constructing a massive Access database. I'll be back with more questions, no doubt.
 

Users who are viewing this thread

Top Bottom