However, you brought up a good point in regard to the join between Investigators and Login. As you can see in the screenshot, it’s the default join and if I understand you correctly, I need to change this correct? If so:
Not necessarily, if you ensured referential integrity. If every Investigator is ensured to link to a login then you will return all investigators. But imagine you had all inner joins and somehow the login table got wiped out. Then no investigators would be returned and then no data from your main table would be returned. So your arrow should flow outwards return all from Pictsdata and matches from investigators. Select all from investigators and matches from login.
What you are showing appears to be the representation of the join in the Relationship window and not in the query window. Is that correct? They are kind of different.
in the query window I should only have
A line with solid dots on the end (inner join)
Table1 o-------------o Table2
Only show records where there is a match in both tables
A line with a right arrow (outer join)
Table1 ---------------> Table2
Include all records from table1 and matching data from table 2
A line with a left arrow
Table1 <--------------- Table2
Include all records from table2 and matching data from table 1
So assume table 2 is Status and it has values
StatusID StatusDesc
P Pending
O on time
L Late
E Early
And table1 stores a StatusID foreign key, and they are related by statusID.
Assume table 1 has thousands of records and each is supposed to have a statusID foriegn key relating to the status.
If you do not force the user to put in a status ID then that field could be left blank. Or if you allow them to type it in then they may pick B, Z, or some other value. If you do an inner join then any record where the status field in table1 does not match a value in table2 will not be returned. If I want to to show all records from table1 event if there is not a match to status then I need an outer join.
The relationship window is a little different. With the query you can change your join type at any time and join whatever you want. The relationship window is about forcing data rules and establishing the default join type. If you pick a join type (inner choice 1 or outer choice 2,3) that will become the default when you make a query, but you can change it in the query.
If in the relationship window I establish a join between table1 and table2 where table1 has a StatusID foriegn key and table2 has a StatusID primary key, it knows I am creating a one to many relationship. There are many records in table1 (foriegn Keys) related to 1 record in table2(primary Key).
So you will see
oo------------------1 (infinity to 1) lots of records that are pending, lots are on time, etc
However you only see this if you select enforce referential integrity. Referential integrity means you cannot add a record to table1 unless you enter a statusID foriegn key.
There are two other choices cascade delete and cascade update. Cascade delete means if you delete a record from your primary key side then it will delete those related records with matching foriegn keys. You would not want that in this case. If you deleted Pending then it would delete all the records that were pending. But if I was maintaining a table of owners and a table of their cars. If I deleted an owner from the policy then I would want to delete their cars of the database.
pen, 2:closed, 3
ending….)