ACCESS FEED: RE: [MS_AccessPros] Failed update query

Selasa, 30 April 2013

RE: [MS_AccessPros] Failed update query

Robin-

Sorry, I answered before my first cup of morning coffee!

Can you delete rows that have no ClubName in ClubGmail, define a Primary
Index on ClubName, then run the query? Why do you have rows in ClubGMail
with no ClubName?

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:58 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: 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
>
>
>

------------------------------------

Yahoo! Groups Links