ACCESS FEED: RE: [MS_AccessPros] Failed update query

Senin, 29 April 2013

RE: [MS_AccessPros] Failed update query

John,

As I see it the join, matching, is on 'ClubName'.

JOIN tClubs ON ClubGmail.ClubName = tClubs.ClubName

The recordset 'ClubGmail' is a list of club names with an email address.

I need to take the data from the 'email' field in the recordset
'ClubGmail' and add it to the field 'email' in "tClubs"

I aplogise that I am not good at describing this,

Regards,

Robin

At 30/04/2013 04:30 PM, you wrote:
>Robin-
>
>Here's what your original query is doing:
>
>1) All rows from ClubGMail are returned
>
>2) Any rows in tClubs that have a matching email value are returned
>
>3) The matching value email value from tClubs is copied to ClubGMail
>
>Because you're already matching on email, nothing happens.
>
>Is the ID field in ClubGMail the same as ClubID in tClubs? If so, you
>should join on that. If not, then please explain what you're trying to do.
>
>John Viescas, Author
>Microsoft Access 2010 Inside Out
>Microsoft Access 2007 Inside Out
>Microsoft Access 2003 Inside Out
>Building Microsoft Access Applications
>SQL Queries for Mere Mortals
>http://www.viescas.com/
>(Paris, France)
>
>
>
>-----Original Message-----
>From: MS_Access_Professionals@yahoogroups.com
>[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Robin Chapple
>Sent: Tuesday, April 30, 2013 8:06 AM
>To: MS_Access_Professionals@yahoogroups.com
>Subject: RE: [MS_AccessPros] Failed update query
>
>Thanks Duane,
>
>At 30/04/2013 02:03 PM, you wrote:
> >Your query is not updateable since the join doesn't involve a primary
> >key/foreign key relationship.
> >
> >Can you create a UNIQUE index on ClubGmail.ClubName?
>
>Error message says " Index or primary key cannot contain a null value".
>
>Does this refer to the space in some names?
>
>Regards,
>
>Robin
>
> > > To: MS_Access_Professionals@yahoogroups.com
> > > From: robinski@mymail.net.au
> > > Date: Tue, 30 Apr 2013 13:49:54 +1000
> > > Subject: RE: [MS_AccessPros] Failed update query
> > >
> > > Thanks Duane,
> > >
> > > At 30/04/2013 12:41 PM, you wrote:
> > > >What are the primary and foreign keys?
> > >
> > > I have not used the term foreign key before.
> > >
> > > Primary key in ClubsEmail is 'ID"
> > >
> > > Primary key in tClubs is "ClubID"
> > >
> > > >If you change the query to a regular select query, can you update
> > > >the tClubs.Email column?
> > >
> > > No!
> > >
> > > Robin Chapple
> > >
> > >
> > > > > To: MS_Access_Professionals@yahoogroups.com
> > > > > From: robinski@mymail.net.au
> > > > > Date: Tue, 30 Apr 2013 10:08:35 +1000
> > > > > Subject: [MS_AccessPros] Failed update query
> > > > >
> > > > > I have an update query that does not execute.
> > > > >
> > > > > Just one field is to updated from another table. it does not
> > > > > give an error message and it foes not update!
> > > > >
> > > > > UPDATE ClubGmail LEFT JOIN tClubs ON ClubGmail.ClubName =
> > > > > tClubs.ClubName SET tClubs.Email = [ClubGmail]![email];
> > > > >
> > > > > What have I missed?
> > > > >
> > > > > Many thanks,
> > > > >
> > > > > Robin Chapple
> > > >
> > > >------------------------------------
> > > >
> > > >Yahoo! Groups Links
> > > >
> > > >
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> >
> >
> >------------------------------------
> >
> >Yahoo! Groups Links
> >
> >
> >
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>