Paul Hartland
paul.hartland at googlemail.com
Fri Mar 14 08:03:23 CDT 2014
ok, I may be missing something here but going by your table descriptions: 1. Why all the primary keys ?, I would have thought putting a primary key on people.ID_People and peoplefilms.aa_peoplefilms would be enough and maybe index peoplefilms.ID_person. 2. In the people table the field called person, is this a name ?, if so why have it set to unique ? 3. You mentioned having a 'not in list' on peoplefilms.IDPerson which I assume you meant peoplefilms.ID_Person, what's the reason behind that ? Paul On 14 March 2014 12:41, Kostas Konstantinidis <kost36 at otenet.gr> wrote: > yes Paul, > I just renamed the fields because in their first version were in greeklish > I think there is no difference to the result of the query > thank's > /kostas > > > > > > -----Αρχικό μήνυμα----- From: Paul Hartland > Sent: Friday, March 14, 2014 1:40 PM > > To: Access Developers discussion and problem solving > Subject: Re: [AccessD] field cannot be updated... > > Your select in your first email doesn't match the fields in you last one, > the underscores are missing and the id field is a different name in the > people table, going by your fields your query below: > > SELECT peoplefilms.aa, peoplefilms.IDperson, people.sex > FROM people INNER JOIN peoplefilms ON people.IDperson = > peoplefilms.IDperson; > > should be: > > SELECT peoplefilms.aa_peoplefilms, peoplefilms.ID_person, people.sex > FROM people INNER JOIN peoplefilms ON people.ID_people = > peoplefilmsID_person; > > Paul > > > On 14 March 2014 09:39, Kostas Konstantinidis <kost36 at otenet.gr> wrote: > > Hi Bill, >> no, it's unique only in people and not in peoplefilms because the most >> usual for a person is to appear more than one time in the same movie (e.g. >> as director and as an actor too) >> >> the weird is that it works for another similar database (for short films) >> in which the relationships are designed exactly the same field by field >> >> people >> ID_people --> Auto increment, Primary, Not Null, Unique >> person --> Primary, Not Null, Unique >> sex --> a simple checkbox >> >> peoplefilms >> aa_peoplefilms --> Auto increment, Primary, Not Null >> ID_films --> Primary, Not Null >> ID_person --> Primary, Not Null >> >> thank's for your response >> >> /kostas >> >> >> -----Αρχικό μήνυμα----- From: Bill Benson >> Sent: Friday, March 14, 2014 1:54 AM >> To: Access Developers discussion and problem solving >> Subject: Re: [AccessD] field cannot be updated... >> >> Can you make changes BEFORE you add a record? It smells like one of those >> not update able query issue that happens when Access can't prove to itself >> there could be one and only one record that reults from the join. Which >> would be, if you joined on a key that is unique in both tables. Is >> IDPerson >> unique in both tables? >> >> If not, I would be surprised you could update existing records either, >> whether the field be aa or sex. >> On Mar 13, 2014 7:26 PM, "Kostas Konstantinidis" <kost36 at otenet.gr> >> wrote: >> >> Hi, >> >>> I use a subform with a record source: >>> >>> SELECT peoplefilms.aa, peoplefilms.IDperson, people.sex >>> FROM people INNER JOIN peoplefilms ON people.IDperson = >>> peoplefilms.IDperson; >>> >>> on peoplefilms.IDperson there is also a "not in list" event which works >>> adding any new record on people >>> >>> the problem is that after a "not in list"... I can't make any change on >>> people.sex >>> have tried refresh or requery but nothing changes >>> >>> >>> thank's a lot >>> >>> /kostas >>> -- >>> AccessD mailing list >>> AccessD at databaseadvisors.com >>> http://databaseadvisors.com/mailman/listinfo/accessd >>> Website: http://www.databaseadvisors.com >>> >>> -- >>> >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> -- >> AccessD mailing list >> AccessD at databaseadvisors.com >> http://databaseadvisors.com/mailman/listinfo/accessd >> Website: http://www.databaseadvisors.com >> >> > > > -- > Paul Hartland > paul.hartland at googlemail.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- > AccessD mailing list > AccessD at databaseadvisors.com > http://databaseadvisors.com/mailman/listinfo/accessd > Website: http://www.databaseadvisors.com > -- Paul Hartland paul.hartland at googlemail.com