Selasa, 24 Mei 2011

RE: [MS_AccessPros] Referential Integrity and blank rows

 

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

__._,_.___
Recent Activity:
.

__,_._,___

Tidak ada komentar:

Posting Komentar