Access 2003 - latest patches. Windows XP
Problem:
Table 1 is linked to Table2 by two fields. A Query is made to include all records from Table1 and only those from Table2 where the values match.
When entering data into one of the Table2 fields that does not have a related record to Table1, the Table1.EmployeeID is being changed to zero (as opposed to propagating the Table1 keys into Table2 for the current record)
If I go into Table1 Table, expand the subdatasheet, and add a record into Table2 - it works fine - the keys are correctly propegated.
Design:
I have a two local Tables. One table is a data dump from a query against a SQL Server. The second table records additional information about the first one. It records the primary key information, plus the auxillary information. The design is set up in such a way that Table1 can be pruned and purged and the Auxillary information remains intact. This way we are not storing huge amounts of data in the local access file - only the linking information and the Auxillary data.
Sample DB Attached, or look below:
Implementation: Table 1: EmployeeHours
EmployeeID - Long Integer
WorkDate - Date
HoursWorked - Single
Table 2: AuxInfo
EmployeeID - Long Integer
WorkDate - Date
Approved - Yes/No
Comments - Text(50)
Relationship:
(
Table1.EmployeeID=Table2.EmployeeID(+)
AND
Table1.WorkDate=Table2.WorkDate(+)
)
I must be doing something wrong.
