[AccessD] field cannot be updated...

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


More information about the AccessD mailing list