ACCESS FEED: RE: [MS_AccessPros] Failed update query

Senin, 29 April 2013

RE: [MS_AccessPros] Failed update query

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