ACCESS FEED: RE: [MS_AccessPros] Failed update query

Selasa, 30 April 2013

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
>
>
>