Re: Cascade Update using only queries

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Yes, I'm working directly in the tables and all of my foreign keys are long
integers.
Jim

"Jeff Boyce" wrote:
Are you working directly in the tables?
It is quite common to have a parent-child relationship between tables. As
it is quite common to use a main form/subform construction to help with data
entry.

It sounds like your "child" tables already have a foreign key to point back
to the parent table's primary key.

By the way, you didn't explicitly state this, so I need to ask ... you are
NOT using an Autonumber data type as your "foreign key" field, right? To
make the parent-child relationship work, you'd need to have a primary key in
the child table and a foreign key field of type LongInt to point back to the
parent record that "owns" it.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Jim Murray" <JimMurray@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E4DB4A39-D47C-4A50-8AD0-4FE74B52FA2B@xxxxxxxxxxxxxxxx
I did not setup my Access / SQL to allow cascade updates. I'm trying to
accomplish this by query. My main table, tblPersons, has an autonumber
primary key. The target tables (11 of them) use this as a foreign key. I
can
update through the query if each table has an entry with the PersonsID.
The
query does not work if one table does not have an entry matching that
PersonsID. I could write a complex VB script checking each table for an
entry
and dropping those that don't from the SQL script but this seems extreme.
Is
there a simple way of doing this?
Sample of Query (only 3 tables)
UPDATE ((tblPersons LEFT JOIN tblActions ON tblPersons.PersonsID =
tblActions.PersonsID) LEFT JOIN tblAddresses ON tblPersons.PersonsID =
tblAddresses.PersonsID) LEFT JOIN tblAlias ON tblPersons.PersonsID =
tblAlias.PersonsID SET tblActions.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAddresses.PersonsID =
[Forms]![adminForm]![cboCombineToPersons], tblAlias.PersonsID =
[Forms]![adminForm]![cboCombineToPersons];




.

Relevant Pages

  • Re: Cascade Update using only queries
    ... "Jeff Boyce" wrote: ... NOT using an Autonumber data type as your "foreign key" field, ... update through the query if each table has an entry with the PersonsID. ...
    (microsoft.public.access.queries)
  • Re: Cascade Update using only queries
    ... Are you suggesting that I set up a form, populate it from a select query ... It is quite common to have a parent-child relationship between tables. ... It sounds like your "child" tables already have a foreign key to point ... update through the query if each table has an entry with the PersonsID. ...
    (microsoft.public.access.queries)
  • Re: Cascade Update using only queries
    ... It is quite common to have a parent-child relationship between tables. ... NOT using an Autonumber data type as your "foreign key" field, ... update through the query if each table has an entry with the PersonsID. ...
    (microsoft.public.access.queries)
  • Please advise on table structure
    ... I have two tables that represent a parent-child relationship. ... B I have a foreign key to table A. In otherwise table B could be ... table B could also be associated with multiple records in table C. ... Each record in table D will comprise of the primary key ...
    (microsoft.public.sqlserver.programming)
  • RE: Query Problem, Please Help!!
    ... >> If you did these things, the query would be super easy, such as the following: ... >> If you used a foreign key to store the months, then the query could join the ... >> for spammers are Ripley@xxxxxxxxxxxxxxx and scott@xxxxxxxxxxxxxxxxxx ... >> When you see correct answers to your question posted in Microsoft's Online ...
    (microsoft.public.access.queries)