[AccessD] Non-updateable Queries

Perhaps my skills at "Asking the right question" have diminished over the
years;) I really appreciate your feedback Marty, however my question must
have been worded poorly. I understand what a non-updatebale query is, and
the causes.
 What I was hoping for, was an idea on how others have approached this type
of situation in trying to make life easier for the data entry person. What I
was envisioning was a single form that contained one area dedicated to
showing the data from the combined non-updatebale query, and several other
areas (subforms or separate pop-up forms?) where updates "can" be made to
the data...with each update refreshing the "main" (non-updatebale) view.
  Mark

 On 10/27/05, MartyConnelly <martyconnelly at shaw.ca> wrote:
>
> It could be many things so here is a checklist
>
> How to troubleshoot errors that may occur when you update data in Access
> queries and in Access forms
>
> http://support.microsoft.com/default.aspx?scid=kb;en-us;328828
>
> Also Have a look at the sample mdb's here especially update and query ones
>
> http://www.rogersaccesslibrary.com/TableOfContents3.asp
>
> Some other things
>
> Basically, the query is non-updateable if there are many-to-many or
> many-to-one relationships represented in the output. Access (actually SQL
> in general) can't do updates in that case because it means that it would
> be
> trying to update multiple rows in one or more of the underlying tables for
> each of the individual rows that you are trying to update in your form.
>
> If your SQL includes some functions that modify the
> data that's in one or more of the tables (e.g., the Left$'s)
> that too would make the resulting recordset non-updateable since there's
> no
> way for Access (or SQL) to accept changes to a field that is the result
> of a
> function.
>
> To be updateable, all output from the query(ies) must be the original
> fields
> without modification, calculation, concatenation, etc.
>
> Trying to do a 1-to-many-to-1 join in the query, this produces unique
> records, but
> access seems to dislike this approach and makes the recordset non
> updateable.
>
> Admin Sparky wrote:
>
> >Based on what I saw, it appears there is nothing inherently wrong with
> how
> >I've normalized the data. However, my question seems to be...now that I
> have
> >this data so nicely normalized, how can I query it in a meaningful way so
> as
> >to be able to update the data? Ideally, the data entry person would like
> to
> >see a list of people along with their equipment and location, then be
> able
> >to change any portion of the information as reassignments occur.
> > Mark
> >
> >
> > On 10/27/05, MartyConnelly <martyconnelly at shaw.ca> wrote:
> >
> >
> >>Maybe have a look at this data model for ideas and it's associated
> >>business rules.
> >>
> >>
> http://www.databaseanswers.org/data_models/inventory_of_servers_in_wyoming/index.htm
> >>
> >>There are a couple hundred here too.
> >>http://www.databaseanswers.org/data_models/index.htm
> >>
> >>Admin Sparky wrote:
> >>
> >>
> >>
> >>><For best viewing, a mono-spaced font is recommended.>
> >>>Group,
> >>>Please comment on the table structure noted below. I chose this
> >>>
> >>>
> >>particular
> >>
> >>
> >>>scheme because locations are the least likely to change and are forming
> >>>
> >>>
> >>the
> >>
> >>
> >>>cornerstone of an Access-AutoCAD application. My problem concerning
> data
> >>>updates is that equipment is most easily identifiable with a person,
> not
> >>>
> >>>
> >>a
> >>
> >>
> >>>location. But, a non-updateable recordset occurs when these tables are
> >>>combined in a query. So...what is an acceptable method of approaching
> >>>
> >>>
> >>this
> >>
> >>
> >>>situation?
> >>>|--tblLocPers---tblPersonnel
> >>>|
> >>>|--tblLocPC-----tblPCs
> >>>tblLocations--|
> >>>|--tblLocMon----tblMonitors
> >>>|
> >>>|--tblLocPrntr--tblPrinters
> >>>Mark
> >>>
> >>>
> >>>
> >>>
> >>-
> >>
>
> --
> Marty Connelly
> Victoria, B.C.
> Canada
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>