Steven-
Getting the "direction" of the relationship correct will help immensely. To
update both tables at the same time, put them both in a query with a Join. If
the MemberID of MemberRegistrationInfo is an AutoNumber, leave that field out of
the query, but include all the other fields from both tables.
John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)
-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of S and M
Sent: Tuesday, May 24, 2011 10:19 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Referential Integrity and blank rows
John,
The reason we have two table is so that we can limit access to some of the
information.
I checked the relationship and found I had them backwards!!!!! Now it allows me
to enter the data in the MemberRegistrationInfo table without having to create a
record in the MembershipApplicationInfo Table.
However, I still need to solve the issue of combining all the data in the
membership form so I can do all the updates in one place.
Am I right to think I can do this with a union query? If so I will try to build
one.
Thanks, Steven
--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Steven-
>
> Why do you have two tables? One-to-one relationships are difficult to manage.
> Which of the two tables is the "master" table? By "master" I mean the table
> listed on the left side of the relationship. When you create a 1-1
> relationship, one of the two tables is the "parent" or "master" table. A row
> must exist in the Parent or Master before you can put a related row in the
Child
> or Slave table.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of S and M
> Sent: Tuesday, May 24, 2011 9:51 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Referential Integrity and blank rows
>
> John,
>
> Tables:
>
> MemberRegistrationInfo: Table
>
> MemberID Auto PK
> SceneName Text
> EmailAddress Text
> DateOfBirth Date/time
> MemberGuest Yes/no
> ReciprocalGuest Yes/no
> EducationalGuest Yes/no
> GuestSponsor Text
> ReciprocalOrganization Text
>
>
> MembershipApplicationInfo: Table
>
> MemberID Number
> FirstName Text
> LastName text
> Phone Text
> EmrgcyContactName Text
> EmrgcyContactPhone Text
> EmrgcyContactRelationship Text
> EmrgcyContactEmail Text
> DateAssociateMembership Date/time
> DateRegularMembershipApplication Date/time
> DateRegularMembership Date/time
> DateAnnualDuesPaid Date/time
> UnderAdministrativeAction yes/no
>
> These two tables have a one to one of MemberID.
>
> The query I being used in the Membership form is:
>
> SELECT MemberRegistrationInfo.MemberID, MemberRegistrationInfo.SceneName,
> MemberRegistrationInfo.EmailAddress, MemberRegistrationInfo.DateOfBirth,
> MembershipApplicationInfo.FirstName, MembershipApplicationInfo.LastName,
> MembershipApplicationInfo.Phone, MembershipApplicationInfo.EmrgcyContactName,
> MembershipApplicationInfo.EmrgcyContactPhone,
> MembershipApplicationInfo.EmrgcyContactRelationship,
> MembershipApplicationInfo.EmrgcyContactEmail,
> MembershipApplicationInfo.DateAssociateMembership,
> MembershipApplicationInfo.DateRegularMemberApplication,
> MembershipApplicationInfo.DateRegularMembership,
> MembershipApplicationInfo.DateAnnualDuesPaid,
> MembershipApplicationInfo.UnderAdministrativeAction
> FROM MemberRegistrationInfo RIGHT JOIN MembershipApplicationInfo ON
> MemberRegistrationInfo.MemberID = MembershipApplicationInfo.MemberID
> ORDER BY MemberRegistrationInfo.SceneName;
>
>
> The form frmGuestRegistrationInfo Record source is the MemberRegistrationInfo
> Table.
>
> The idea here (what I want to do) is to show all the MemberID's (and other
data)
> in the MemberRegistrationInfo table in the Member form even though there is no
> member data,....this way, when they become members there is not a duplicate
> entry.
>
> Does this help?
>
> Steven
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@> wrote:
> >
> > Steven-
> >
> > 1) What's the design of the three tables?
> >
> > 2) How are the tables related?
> >
> > 3) What is the SQL of any query you're using?
> >
> > John Viescas, author
> > Microsoft Office Access 2010 Inside Out
> > Microsoft Office Access 2007 Inside Out
> > Building Microsoft Access Applications
> > Microsoft Office Access 2003 Inside Out
> > SQL Queries for Mere Mortals
> > http://www.viescas.com/
> > (Paris, France)
> >
> >
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com
> > [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of S and M
> > Sent: Tuesday, May 24, 2011 9:07 PM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Referential Integrity and blank rows
> >
> > Hey everyone,
> >
> > Somewhere along the way I have boxed myself into a corner. I am not even
sure
> I
> > know how to explain the problem correctly.
> >
> > I have two tables that are related by MemberID. I have a form that uses a
> query
> > to create entries into each table as appropriate.
> >
> > HOWEVER, A sub form in another part of the DB only creates a record in one
of
> > the tables.
> >
> > The right info gets into the right tables as long as I have referential
> > integrity off.
> >
> > The problem is that when I edit the info in the form only the entries that
> have
> > rows in both columns show. I need to be able to see and edit all the info
in
> > both tables.
> >
> > SO, I am not sure as to the approach to fix this. Am I using the wrong
> > query,...maybe I should be using a UNION query?
> >
> > OR, do I need to force an entry into both tables when the sub form creates
an
> > entry?
> >
> > No doubt I am not providing enough information so please just let me know
what
> > is needed and I will post it.
> >
> > Just when I thought I was done!!!
> >
> > Thanks, Steven
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
------------------------------------
Yahoo! Groups Links
Tidak ada komentar:
Posting Komentar