1-1 relationship ensuring data entry in both tables

Database has 2 tables in 1-1 a relationship because one table has
confidential information. The PK is the same for both tables. Problem: Person
enters data in table 1 (public) and forgets to enter in table 2 (non-public),
then Access cannot find the record when I search using query that includes
both tables. I would like to find a way to force entry of one field in table
2. I can't rely on data entry staff to remember to do this on their own.
Data entry is by form.

On Fri, 13 Feb 2009 13:33:53 -0800, Denise
wrote:

Database has 2 tables in 1-1 a relationship because one table has
confidential information. The PK is the same for both tables. Problem: Person
enters data in table 1 (public) and forgets to enter in table 2 (non-public),
then Access cannot find the record when I search using query that includes
both tables. I would like to find a way to force entry of one field in table
2. I can't rely on data entry staff to remember to do this on their own.
Data entry is by form.

If the non-public data only exists for some records, you can use a Left Join
in a query to avoid the problem: edit your query that's missing data, select
the Join line, and choose option 2 (or 3) - "Show all records in publictable
and matching records in nonpublictable".

If you're using a Form based on the public table and a Subform based on the
private table (which would be typical), this can be a bit tough, since the
mainform record must be saved to disk before the subform opens. You'll need to
run a query checking both tables in some appropriate form event (NOT the
mainform's BeforeUpdate, which fires the instant you set focus to the
subform); the mainform's Close event might be appropriate.
--

John W. Vinson [MVP]

Thanks for your help. I took another look at all the relationships and found
one that had the join turned around. After I corrected it, everything is
working as expected. I can now enter just the public information and come
back later and filter for the record to enter the non-public data. It shows
all in publictable and matching nonpublictable.

"John W. Vinson" wrote:

On Fri, 13 Feb 2009 13:33:53 -0800, Denise
wrote:

Database has 2 tables in 1-1 a relationship because one table has
confidential information. The PK is the same for both tables. Problem: Person
enters data in table 1 (public) and forgets to enter in table 2 (non-public),
then Access cannot find the record when I search using query that includes
both tables. I would like to find a way to force entry of one field in table
2. I can't rely on data entry staff to remember to do this on their own.
Data entry is by form.

If the non-public data only exists for some records, you can use a Left Join
in a query to avoid the problem: edit your query that's missing data, select
the Join line, and choose option 2 (or 3) - "Show all records in publictable
and matching records in nonpublictable".

If you're using a Form based on the public table and a Subform based on the
private table (which would be typical), this can be a bit tough, since the
mainform record must be saved to disk before the subform opens. You'll need to
run a query checking both tables in some appropriate form event (NOT the
mainform's BeforeUpdate, which fires the instant you set focus to the
subform); the mainform's Close event might be appropriate.
--

John W. Vinson [MVP]