Re: Non-updateable query issue

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


"sjwopg" wrote:

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


"Allen Browne" wrote:

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sjwopg" <sjwopg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ABE960A3-3B65-483C-8A5D-3F46192EE8D8@xxxxxxxxxxxxxxxx
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


"Duane Hookom" wrote:

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.

--
Duane Hookom
Microsoft Access MVP


"Allen Browne" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"sjwopg" <sjwopg@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:705B9AD0-41AE-41B3-AD82-71A3BEFF20D1@xxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • RE: runtime error record is deleted
    ... Microsoft Access MVP ... Should I run an update query to update all of the tables or should I load ... "Duane Hookom" wrote: ... Public Function ParseText(pstrText As String, intElement As Integer, _ ...
    (microsoft.public.access.queries)
  • RE: Concatenate fields from multiple tables - repost
    ... Join all the tables into a single query excluding the tbl_Project. ... Duane Hookom ... Microsoft Access MVP ... I would like to know if Duane’s function can concatenate such a scenario ...
    (microsoft.public.access.queries)
  • RE: Calendar Error (Duanes Calendar)
    ... If there would be no records then why report this? ... Microsoft Access MVP ... "Duane Hookom" wrote: ... When I run the query with the Criteria listed from Col1 and Col ...
    (microsoft.public.access.reports)
  • Re: Order by Alias Field Name?
    ... "Duane Hookom" wrote: ... where table2.tbl1$id = T1.ID) as NumberOfChildRecords ... FROM Table1 as T1 ... Or you could save the subquery as a separate query, ...
    (microsoft.public.access.queries)
  • RE: Using combo box in a form -- how to make drop-down work?
    ... Duane Hookom ... Microsoft Access MVP ... select the key field from a combo box in order to find the record they ... I went into the "Properties" of the column in Query ...
    (microsoft.public.access.queries)