Re: Non-updateable query issue

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: How to add to a new field based on information in existing field?
    ... a query to join the two tables. ... For example, Table1 includes fields TestID ... Table2 includes fields Description and DescriptionFr. ...
    (microsoft.public.access.modulesdaovba)
  • Re: Non-updateable query issue
    ... Microsoft Access MVP ... When putting together that query, ... "Duane Hookom" wrote: ... Say Fred deletes record 79 from Table1, ...
    (microsoft.public.access.queries)
  • doubt
    ... The Microsoft Jet database engine cannot find the input table or query ... Exception Details: System.Data.OleDb.OleDbException: The Microsoft Jet ... database engine cannot find the input table or query 'Table1'. ... dbParams, Object& executeResult) +255 ...
    (comp.databases.ms-access)
  • The Microsoft Jet database engine cannot find the input table or query Table1. Make sure it exists
    ... The Microsoft Jet database engine cannot find the input table or query ... Exception Details: System.Data.OleDb.OleDbException: The Microsoft Jet ... database engine cannot find the input table or query 'Table1'. ... dbParams, Object& executeResult) +255 ...
    (comp.databases.ms-access)
  • Re: Displaying row no/Record Counting
    ... EnteredOn Date/Time when the record was added. ... You create a query that contains Table1. ... On each row of your query, you need to count the number of records in Table1 ... In> order to generate an alphabetical listing of these people,> I have to analyze the report in Excel and sort the> spreadsheet based on the client's name. ...
    (microsoft.public.access.queries)