ResyncCommand and UniqueTable Properties Example Scenario
There are two SQL Server tables involved in these scenarios, Patients and Doctors, with a one-to-many relationship from Doctors to Patients. Both tables have an identity column as a primary key.
Patients table
Column | Datatype | Attributes |
---|---|---|
ID | int | identity, Primary Key |
LastName | varchar(30) | not null |
FirstName | varchar(20) | not null |
PrimaryDoc | int | null Foreign Key references Doctors(DocID) |
Doctors table
Column | Datatype | Attributes |
---|---|---|
DocID | int | identity, Primary Key |
DocName | varchar(30) | not null Unique |
DocPhone | char(15) | not null |
The user wants to create an updatable datasheet view of data on patients and their doctors. The user wants the grid to show the following:
ID | LastName | FirstName | PrimaryDoc | DocName | DocPhone |
---|---|---|---|---|---|
1 | Jones | Peter | 10 | Welby | (206) 882-8080 |
2 | Williams | Jay | 20 | McCoy | (206) 936-4321 |
3 | Roberts | George | 20 | McCoy | (206) 936-4321 |
4 | Crary | Dave | 10 | Welby | (206) 882-8080 |
The actual query underlying this form is logically:
SELECT P.ID, P.LastName, P.FirstName, P.PrimaryDoc, D.DocName, _
D.DocPhone FROM Patients P JOIN Doctors D ON _
( P.PrimaryDoc = D.DocID)
User interface actions
Note Normally the Microsoft Access user would want a Lookup column defined on PrimaryDoc so the user could selecta name from a dropdown list and wouldn't have to know the ID for Dr. GUI. In this case there wouldn't likely be a separate column for the DocName-- it would show up in the place of the Primary Doc column. But the user would still expect Dr. Gui's correct phone to appear after the update was made.