Selasa, 24 Mei 2011

Re: [MS_AccessPros] Referential Integrity and blank rows

 

Andy,

The separation was done to allow for one of the tables to be locked down to limit access to that information.

Was that the right decisions,...we will know soon I hope.

Thanks, Steven

--- In MS_Access_Professionals@yahoogroups.com, "Andrew Mills" <amills@...> wrote:
>
> I have to ask: if this is a one-to-one relationship, wouldn't it be more
> productive to have all the information in one table?
>
>
>
> Thanks
>
> Andy
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of S and M
> Sent: Tuesday, May 24, 2011 3: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
> <mailto:MS_Access_Professionals%40yahoogroups.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%40yahoogroups.com>
> > [mailto:MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.com> ] On Behalf Of S and M
> > Sent: Tuesday, May 24, 2011 9:07 PM
> > To: MS_Access_Professionals@yahoogroups.com
> <mailto:MS_Access_Professionals%40yahoogroups.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
> >
>
>
>
>
>
> [Non-text portions of this message have been removed]
>

__._,_.___
Recent Activity:
MARKETPLACE

Find useful articles and helpful tips on living with Fibromyalgia. Visit the Fibromyalgia Zone today!


Stay on top of your group activity without leaving the page you're on - Get the Yahoo! Toolbar now.

.

__,_._,___

Tidak ada komentar:

Posting Komentar