Re: Cascade update not working

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Hi Craig

Thanks for your reply and explanations. Yes, I have set up the
relationships and set cascade update related fields. I can now enter details
into my subform and the related data is cascaded down to the referencing
table (child table). I have more experience designing forms and queries than
establishing relationships between 4/5 tables. I realised it is essential to
get the relationships right from the start, so I am working on it and
appreciate all your feedback today.

Have a great day further.

"Craig Alexander Morrison" wrote:

> > In your example you use the ClientID as a primary 3 times, can I reference
> > to 3 tables using the same primary key? To clarify, could you please show
> > me
> > how you would set up the relationships?
>
> If you do not have this then you do not have 1 to 1 relationships. The only
> way to uniquely identify any record in any table in the 1 to 1 relationship
> is via the ClientID.
>
> Just create your three tables with the same primary key and link them via
> the relationships window. Have you actually established these relationships
> via the Relationships Window and set the cascade options?
>
> > Thank you for your reply and suggestions. The SpouseID is for the
> > spouse's
> > own national identity number, the same with the clientID. I have
> > considered
> > adding the spouse details to the client table, but thought it would be
> > neater
> > if the details were in a seperate table as I could add a command button to
> > my
> > client details form to add a spouse if the client has one.
>
> You need to design the correct relational database first and then worry
> about the forms. I would consider it unwise to have a separate table, but
> wish you luck.
>
> > I thought a 1 to 1 relationship is the only way to work here, as a client
> > should only have 1 spouse.... but thinking about it now, can I have a 1 to
> > 1
> > relationship and not have a corresponding record in the other table?
>
> Of course! A 1 to 1 or 1 to Many relationship also implies that a related
> record is optional. The record in the first table is mandatory, of course as
> you can't have one or many related "detail/child" records if there isn't a
> "master/parent" record.
>
> I would seriously consider learning a bit more about relational database
> design before you proceed if the use you are going to put this database to
> is non-trivial.
>
> Access is not like Excel or Word you can't just do it! You need to carefully
> design the structure of the data so that you can take full advantage of the
> way Access is designed to work. With a solid and correct data structure the
> applications (form/reports etc) become so much easier to create and develop.
>
> If you provide more details of your problem domain others here may be
> willing to suggest possible designs, take such advice with care as it can
> only be based on what you tell them and if you miss something out that could
> be important any solutions will range from slightly wrong to wildly wrong.
>
> You may be better starting a new thread to get others to discuss the
> database design as others here may well skip over this one thinking it has
> been answered.
>
>
> Craig Alexander Morrison
> Crawbridge Data (Scotland) Limited
> "mix" <mix@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:A2D19C3B-373C-454C-9D16-DD5E80F80FE4@xxxxxxxxxxxxxxxx
> > Hello Craig
> >
> comments above
>
>
>
>
.

Relevant Pages

  • Re: adding fields
    ... design, Access tips/tricks, graphical user interface design and application ... Each patient has at least three visits, ... Null), or because the underlying field has a "zero-length string", ... relational database, not a spreadsheet. ...
    (microsoft.public.access.forms)
  • Re: design question
    ... Storing a check box in your table for each possible type of entry is not the right way to design a relational database. ... whereclause to filter records having a -1 in the particular field in my table. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Table Design Question
    ... not add meaning to the data that is being store in the column and table. ... In absolutely no relational design contexts, ... multiple applications enforcing integrity can be seen in F. Brooks classic ... This is one problem though trivial, good relational database design is ...
    (microsoft.public.sqlserver.programming)
  • Re: Access 2002 Database Newbie
    ... >design and reconsidering these, using that old standard ... >Customer/Site might have multiple applicable ... You DON'T want to put the photos in ... >fields in a relational database need to have a single ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Variation of Classic Self-Join Problem
    ... teach introductions to SQL and relational database design. ... database game, have zero experience with flat file designs, but learned a ... such terminology even though we've never done a flat file design. ...
    (microsoft.public.sqlserver.programming)