ACCESS FEED: RE: [MS_AccessPros] Failed update query

Selasa, 30 April 2013

RE: [MS_AccessPros] Failed update query

Robin-

Are you able to define a Unique or Primary key on ClubName in ClubGmail?
That should fix it.

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 1:23 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: RE: [MS_AccessPros] Failed update query

Thanks John,

At 30/04/2013 05:19 PM, you wrote:

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

The query will still not run but the error message does not appear.
In the view mode it is not updateable.

>Why do you have rows in ClubGMail
>with no ClubName?

That is because I did not check the table after the data was imported from a
CSV file. I'm still learning! There are no empty rows now.

Regards,

Robin Chapple

>-----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
>
>
>
>
>
>
>------------------------------------
>
>Yahoo! Groups Links
>
>
>

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

Yahoo! Groups Links