If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
1-1 relationship ensuring data entry in both tables
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] |
#3
|
|||
|
|||
1-1 relationship ensuring data entry in both tables
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] |
Thread Tools | |
Display Modes | |
|
|