Non-updateable query issue

[sjwopg]

I am creating an application which will have 20+ users. In order to minimize
errors or record locking issues, I have created three tables for each record.
The users will enter/edit their fields without another user being in the same
record.

Table1 has a primary key. Tables 2 & 3 have primary keys, and a foreign key
that relates to table1.

I created a query joining Table1 to Table2 using the T1 primary to the T2
foreign, and joining Table1 to Tabel2 using the T1 primary to the T2 foreign.

This makes the query unupdateable. If I join T1 to T2, or T1 to T3, each
table is updateable. It is when I join the three tables as described above,
that the tables become unupdateable. I've tried a number of different
scenarios, but it seems that whenever I try to join Table1 to the other two
via the Table1 primary key, the query is unupdateable.

Any suggestions?

Thanks in advance,

Steve

 

[Allen Browne]

Steve, either I have not understood you, or this is an exercise in
frustration.

You cannot prevent multi-user issues by giving separate copies of the data.
How will you ever sort them out?

Say Fred deletes record 79 from Table1, and then Betty changes the phone
number for record 79 in Table2 afterwards. You now have a deletion (which
you have presumably cached somewhere), with a more recent edit. Do you
resolve that by deleting the record in Table2? Or by restoring the record in
Table1 because there is a more recent edit? Or ...?

It seems to me that you are creating more problems than you could possibly
solve.

Anyway, here's a list of things that can cause a query to be read-only:
http://allenbrowne.com/ser-61.html
The most likely cause in your case is JOINs where neither end has a unique
index.

 

[Duane Hookom]

I agree with Allen. Also, when I hear about the possibility of multiple users
editing the same record at the same time, I expect the tables are not
normalized.

 

[sjwopg]

Based on your responses, I re-read what I had posted. It's either a grammar
and syntax issue, or a disconnect between the brain and the fingers. The
scenario I gave doesn't make sense to me either.

I'm converting a planning spreadsheet. Each record is 100+ columns. A number
of users may have to be in the same record at one time, although not in the
same field. In an effort to avoid conflicts, I split the record into several
tables, based on the fields a particular group of users require. The tables
all relate to a master record number. When I construct a query, all tables
joined to the master, showing all the records, the query is not updateable.

I looked at other queries that I have written. Seems like I have to re-think
the relationships, or rearrange field placement in applicable tables.

Hope this clarifies things a bit.

Thanks for your responses.

Steve

 

[Allen Browne]

Okay, that makes a bit more sense.

You have an unnormalized table (of course, as it came from a flat
spreadsheet instead of a relational source), and is it has 100 columns. If
it's worth bringing into Access, you will need to break it into related
tables, where:
- one row has many related rows in another column,
- the repeated columns (e.g. Week1, Week2 etc) become many *records* in a
related table instead of many columns in this table,
- values that repeat on many rows become a lookup table,
and so on.

One you do this, your 20 users will be editing the same record much less
often, and so you won't have to take the drastic measure we thought you were
originally suggesting.

If table design is new, here's a couple of basic examples:
http://allenbrowne.com/casu-06.html
http://allenbrowne.com/casu-23.html
a PDF tutorial (the 'Normalizing Data' link):
http://allenbrowne.com/casu-22.html
and a heap more links for further reading:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101

 

[sjwopg]

Thanks Allen.

Your response is the same as my approach. In the spreadsheet, each record is
a one to one relationship except for some differing dates. In other words,
there might be three rows wherein the only difference between them is the
dates. That is why I designed it the way you suggested. The query issue arose
because user 1 wants to see fields from user 2 and 3 when they work on their
records, in a datasheet view. When putting together that query, I discovered
that when joining two tables to a main table by using the main table's
primary key, the query becomes non-updateable.

I've been developing in Access for quite a while and had never encountered
this issue before. I am a big fan of normalization. My logic works great when
using a form for data entry because I use sub forms.

Taking into account that the user wants to see fields from other tables, I'm
thinking that a solution would be to create the datasheet view, which would
be non-updateable. When the user wants to edit a record, have them double
click on a field in the datasheet which would pop up a form for that record
that they could edit.

The design works great for reporting purposes.

Thanks again,

Steve

 

[Duane Hookom]

Your reply continues to suggest highly un-normalized tables. If you described
your tables and fields, someone might be able to make a suggestion.

 

[sjwopg]

Actually, the tables are too normalized. I believe I need to put all, not
some, of the common fields in one table. The rest of the fields are in tables
based on the fields each user/group needs to manipulate. The users want to be
able to manipulate their records in a datasheet view, which doesn't allow for
sub forms. The issue arises when I create the query for the datasheet view to
allow user to see a couple of fields that another user manipulates. To
accomplish this, I was joining the master table to two child tables via the
master table primary key. That is what causes the problem. Apparently, you
can't join one table to more than one other table using the original table's
primary key.

I don't know if this is any clearer, but I hope it makes sense.

Thanks,

Steve

 

[Duane Hookom]

I have been hinting at asking you to explain your actual tables and fields. I
don't understand why more than one user has the responsibility for editing a
single record possibly at the same time. I still assume (maybe wrongly) your
tables are not structured correctly.

 

[sjwopg]

I tried responding earlier, but I got a message the page was too busy.

I think I came up with the solution. Since the child tables only have one
record for each master table record, I made the primary key in the child
tables the foreign key from the master table. That allows for multiple joins
using the master table PK because the child PKs are the same.

This is essentially a merchandising tool. Planning, Sourcing, Merchandising
and Store Planning all make entries into the record. Each record consists of
three rows that are only differentiated by delivery dates and order
quantities. Therefore, the first row is the master record. The other two rows
are filled in by cell references except for the dates and quantities.

I've created tables for each group. The issue arose because each group wants
to work in a datasheet view that includes a few fields from other group
tables. Since the child tables had an autonumber PK, along with the master
PK, joining from the master PK caused the query to be non-updateable.
Changing the child PK fixed that.

My original logic works fine in form view because I could use sub forms.
Datasheet view does not allow for sub forms.

I don't know if this makes any more sense, but posting back and forth here
caused me to re-think the relationships.

Thanks for spending so much time on this.

Steve

 

[Duane Hookom]

My opinion remains that I think your tables aren't normalized. "Planning,
Sourcing, Merchandising and Store Planning" with one to one relationships
with the main table still sounds highly un-normalized. You haven't provide
any field names or descriptions so I can't be sure.

 

[sjwopg]

I just wrote a more detailed explanation. When I clicked post, it took me to
a page stating that the system was too busy to show the web page. Rather than
clicking on go back, I clicked on refresh, wiping out my explanation.

Bottom line is that changing the PK in the child tables to the FK from the
master solved the problem.

I'm doing this for a very large company that just bought a bunch of stores.
As usual in the corporate world, it has taken some time to learn the process.
It has also taken some time for the client to get me the information I need
to do the development. And, of course, they need this tool asap.

It's Saturday, it's my birthday, I have things to do, and I'm sitting here
working. So, I'm a little pissy right now. I need to get at it.

Thanks for all your prodding.

Steve

 

[David W. Fenton]

Since the child tables only have one
record for each master table record,

If that's the case, they aren't child records -- it's a 1:1
relationship. And what people are telling you is that you likely
still are denormalized and have not fixed the problem.

In most cases, you should be taking a group of fields from the
spreadsheet and making each column into a *new record* in the child
table. If you have this:

PersonID Name Phone1 Phone2 Phone3

Then you should take the phone number fields out of the main record
and create three records with these fields:

PersonID Phone

And that would be three *rows*, not columns.

That's going to make it much, much less likely that users will be
editing the same data at the same time (it also makes it easier to
search by phone number, for instance, since you only have to check
one column instead of three).

 

[David W. Fenton]

I'm doing this for a very large company that just bought a bunch
of stores. As usual in the corporate world, it has taken some time
to learn the process. It has also taken some time for the client
to get me the information I need to do the development. And, of
course, they need this tool asap.

Sorry to be blunt, but it really sounds like you're not competent
enough with databases (let alone Access) to be paid for such work.

 

[Lord Kelvan]

note this post is made on the first post not the other 10 because
after reading a few it seems they have found other problems which is
not the first problem so i haev decided to answer the initial
question you had

what david said is mean true maybe but mean

your query is not updateable because you are getting a Cartesian
product because table2 does not haev a forign key in table 3 or visa
versa because table 2 is not and most likly cannot be linked to table3
and so because of that you should never do a query one all three
tables because will will get a Cartesian product and that is a really
bad thing

i remember helping someone who was doing a query exatally the same as
you he wasnt trying to update mind you but because of they query which
is exatally the same two tables linking to a control table and not to
eachother

the problem was never the 20 users thing that creates other problems
but be very mindful of Cartesian products and i suggest you do some
research on them because they cause many problems if you dont
understand them and you start doing things and start getting them

Regards
Kelvan

 

[Lord Kelvan]

cough sorry 14 not 10

 

[sjwopg]

Gee Dave, thanks for that. It looks like all the efforts you have been making
to hone your people skills are really strating to pay off.

 

[sjwopg]

Congratulations Lord! May I call you Lord? You figured out the real problem
on your first try. Most importantly when one is attempting to help others
solve problems is to recognize the problem. I work with end users quite a bit
and have to determine their real problem, which is not necessarily what they
are asking. Now that David has gotten so good with his people skills, maybe
it's time to tackle reading comprehension.

I wrote the original question after a very long intense day of developing.
Therefore, I must not have been clear as to the actual problem. I'm in the
midst of a project that started out to be very simple, but because of my
incompetence, the client has asked for a solution that is considerably more
complicated. In addition, the project got changed from a late October target
date, to a mid-September go live, because of the timeliness and quality of my
work. I was frustrated by not being able to see the answer to a very simple
question. But, that's how it goes sometimes.

You are right, normalized tables had nothing to do with it. I probably
shouldn't have even brought the tables issue up, since all the others
responding focused on normalizing tables. Joining the tables was the issue.
You'll notice that in my last response to Duane, I stated that I had spent
time on a detailed explanation of the the issue, as well as the solution.
When I tried to post it, I got the page that said the system was too busy to
post. I inadvertantly clicked on refresh, which wiped out my explanation. It
was Saturday, my birthday, I had a bunch of stuff to take care of around the
house, and I was working. I was in no mood to re-type it.

I don't often work on projects where there is a need for several tables that
have one to one relationships. After Duane patiently prodded me to keep
defining the issue, it became clear to me that since the tables were all one
to one, I should make the child/secondary table's foreign key, from the
master table, the primary key in each of the secondary tables. Bingo, it
worked. That is the gist of the post that got lost.

By the way, you used a word in your response with which I was not familiar -
exatally. So, I Googled it and got 577 responses. At the top of the page,
Google asked if I meant exactly. So, does that make you incompetent, ignorant
or a product of the Los Angeles school system? I think not.

And while I'm at it, let's keep the Cartesians out of this. We have enough
problems with the Elbonians, Chinese, North Koreans, Russians, Iraqis,
Iranians, Mexicans....... we don't need no stinking Cartesians to cause more.

Had you been the first to respond, this thread would have been three posts.
My first, your first, and my second, which would have been, in the words of
Roseanne Roseannadanna - Oh......nevermind.

Thanks,

Steve

BTW - I just got that too busy page again. This time I clicked go back,
rather than refresh.

 

[Lord Kelvan]

i think you mis-understand

Cartesian product is a database term which is in relation to an outter
join and it is an important thing to know about in working with
databases as it can cause numerious problems.

sorry i did mean exactly as you said it was saturday and i was tired.
=P

i am glad to help

Regards
Kelvan

 

[Bob Barrows [MVP]]

posts. My first, your first, and my second, which would have been, in
the words of Roseanne Roseannadanna - Oh......nevermind.

You mean Emily Litella ;-)