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
>
Tidak ada komentar:
Posting Komentar