<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>Message</TITLE>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1141" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=528543616-01042003><FONT face=Arial color=#0000ff
size=2>Susan,</FONT></SPAN></DIV>
<DIV><SPAN class=528543616-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=528543616-01042003><FONT face=Arial color=#0000ff size=2>That
is a nice little synopsis.</FONT></SPAN></DIV>
<DIV><SPAN class=528543616-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=528543616-01042003><FONT face=Arial color=#0000ff
size=2>Thanks,</FONT></SPAN></DIV>
<DIV> </DIV>
<P><FONT size=2>John W. Colby<BR>Colby
Consulting<BR>www.ColbyConsulting.com</FONT> </P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B>
accessd-bounces@databaseadvisors.com
[mailto:accessd-bounces@databaseadvisors.com]<B>On Behalf Of </B>Susan
Harkins<BR><B>Sent:</B> Tuesday, April 01, 2003 11:16 AM<BR><B>To:</B>
accessd@databaseadvisors.com<BR><B>Subject:</B> Re: [AccessD] Updating queries
with linked tables<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Roz, not all queries are updateable. Here are
some general rules:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A query based on a single table is updateable
if:</FONT></DIV>
<DIV><FONT face=Arial size=2> The query contains no aggregate functions
or refers to a query that contains an aggregate function. </FONT></DIV>
<DIV><FONT face=Arial size=2> The query's Unique Values property is set
to No.</FONT></DIV>
<DIV><FONT face=Arial size=2> The query doesn't include a GROUP BY
clause.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A multi-table query with a one to one
relationship is updateable provided all of the above conditions are true.
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>A multi-table query with a one to many
relationship is updateable on both sides as long as you don't violate
referential integrity rules, and provided the above conditions are true. Try
a</FONT><FONT face=Arial size=2>dding the foreign key value to the query --
did I read where you'd tried that? If that's the case, check referential
integrity -- if it's enabled, also enable the cascade updates between the two
tables. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Keep in mind that the following aren't
updateable, regardless: Crosstab, pass-through, and UNION (that's why I asked
for the clarification about the word combine), multi-table queries based on
three or more tabls in a many to one to many relationship, Totals view.
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Hope something there helps. </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><FONT face=Arial size=2> </FONT><FONT face=Arial
size=2> </FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV style="FONT: 10pt arial">----- Original Message ----- </DIV>
<DIV
style="BACKGROUND: #e4e4e4; FONT: 10pt arial; font-color: black"><B>From:</B>
<A title=roz.clarke@donnslaw.co.uk
href="mailto:roz.clarke@donnslaw.co.uk">Roz Clarke</A> </DIV>
<DIV style="FONT: 10pt arial"><B>To:</B> <A
title=accessd@databaseadvisors.com
href="mailto:'accessd@databaseadvisors.com'">'accessd@databaseadvisors.com'</A>
</DIV>
<DIV style="FONT: 10pt arial"><B>Sent:</B> Tuesday, April 01, 2003 10:52
AM</DIV>
<DIV style="FONT: 10pt arial"><B>Subject:</B> RE: [AccessD] Updating queries
with linked tables</DIV>
<DIV><BR></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff size=2>No
blank record, all fields locked. </FONT></SPAN></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff size=2>It
wouldn't surprise me if this is just a 'feature'.</FONT></SPAN></DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=731115215-01042003><FONT face=Arial color=#0000ff
size=2>R.</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B> Susan
Harkins [mailto:harkins@iglou.com] <BR><B>Sent:</B> 01 April 2003
16:25<BR><B>To:</B> <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR><B>Subject:</B>
Re: [AccessD] Updating queries with linked tables<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Well, when you say "combine" -- I think of
append or UNION. Relationships "join" records -- nothing wrong with the
way you siad it -- I just thought I'd check to make sure. So here's the
next question -- how do you know it's not updateable? Are you getting an
error message? Is there a blank (new) record at the bottom of the query in
Datasheet View?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV><BR> </DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV><SPAN class=133000109-01042003><FONT face=Arial color=#0000ff
size=2>Standard left outer join on staff ID - is there another way?!
</FONT></SPAN></DIV>
<DIV></DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN class=133000109-01042003><FONT
face=Arial color=#0000ff>*bangs head on
desk*</FONT></SPAN></FONT></FONT></DIV>
<DIV><FONT face=Tahoma><FONT face=Arial color=#0000ff size=2><SPAN
class=133000109-01042003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Tahoma><FONT face=Arial color=#0000ff size=2><SPAN
class=133000109-01042003>R.</SPAN></FONT></FONT></DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN
class=133000109-01042003></SPAN></FONT></FONT> </DIV>
<DIV><FONT face=Tahoma><FONT size=2><SPAN
class=133000109-01042003> </SPAN>-----Original
Message-----<BR><B>From:</B> Susan Harkins [mailto:harkins@iglou.com]
<BR><B>Sent:</B> 31-Mar-2003 17:20<BR><B>To:</B> <A
href="mailto:accessd@databaseadvisors.com">accessd@databaseadvisors.com</A><BR><B>Subject:</B>
Re: [AccessD] Updating queries with linked
tables<BR><BR></DIV></FONT></FONT>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT face=Arial size=2>How are you combining your tables?
</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Susan H. </FONT></DIV>
<DIV> </DIV>
<DIV><FONT face=Arial size=2>What I'm finding (and I can't believe I
never hit this before) is that although the SQL tables can be updated
through the Access FE, and the local tables can be updated, combine
them and wallop, not updateable. Even if all the key fields are
included in the query.</FONT></DIV>
<BLOCKQUOTE
style="PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px"><FONT
face=Arial size=2></FONT> </BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD
mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<P>
<HR>
<P></P>_______________________________________________<BR>AccessD mailing
list<BR>AccessD@databaseadvisors.com<BR>http://databaseadvisors.com/mailman/listinfo/accessd<BR>Website:
http://www.databaseadvisors.com<BR></BLOCKQUOTE></BLOCKQUOTE>
<DIV>
<HR>
</DIV>
<DIV>Is email taking over your day? Manage your time with eMailBoss. Try it
free! <A
href="http://www.eMailBoss.com">http://www.eMailBoss.com</A></DIV></BODY></HTML>