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