Populate One to One table

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am creating reports from a read only linked database (members table) and I
need to add some additional fields for my reports. I created a database
(myMembers) in my application with memberID (the same field as the linked
table) and the additional fields I need. Using an append query I populated
the MemberID field in myMembers table. Linked the two tables: Members,
mymembers. To this point everything is OK.

When someone is deleted or added to the members table, how do I up date the
mymembers table.

Thanks for any help,
John
 
Open the Relationships window (Tools menu.) Presumably you already created
the relationship, so right-click the line joining the 2 tables. In the
dialog, check the box for cascading deletes. (Note: The direction of the
relation is really important here: otherwise you will have records deleting
the wrong way.)

To automatically insert a new record into the related table when you add one
to the main table, use the AfterInsert event procedure of the form where you
add records. Execute an append query statement to add the record to the
other table.

A couple of comments, John, FWIW:

1. One-to-one relations are unusual. If you are doing this just because you
have run out of fields in the main table, it may indicate that you do not
have a relational design.

2. If you need the related record just because your queries are not working,
you might be able to solve the problem by using outer joins. More info:
http://allenbrowne.com/casu-02.html

3. John Viescas has a book that includes a membership database on the CD, so
you effectively get the application for free when you buy the book. You can
read all about it here:
http://msdn2.microsoft.com/en-us/library/aa662176(office.11).aspx
 
Allan,

Thank you very much for the response.

The reason I am using a one-to-one table is I don't have permissions to add
fields to the main table and I thought this would be one solution.

Again, main thanks,

John
 
That's a valid reason.
I Don't think it's possible to make a Cascade Update / Delete Join between a
local & a foreign table (but I've been Wrong Before)
If I'm Right you could Run these queries in a form's open event (or startup
code)

INSERT INTO MembersLocal (MemberID)
FROM Members M
WHERE NOT EXISTS (SELECT 'X' FROM MembersLocal L
WHERE L.MemberID = M.MemberID)

and

DELETE FROM MembersLocal
WHERE NOT Exists (SELECT 'X' FROM Members M
WHERE M.MemberID=MembersLocal.MemberID)

Note That this Syntax will work for MSSQL, Jet & Oracle

HTH

Pieter
 
Back
Top