Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
Mar
Warning: strtotime() [function.strtotime]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 35
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 107
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 109
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 111
Warning: date() [function.date]: It is not safe to rely on the system's timezone settings. You are *required* to use the date.timezone setting or the date_default_timezone_set() function. In case you used any of those methods and you are still getting this warning, you most likely misspelled the timezone identifier. We selected 'America/New_York' for 'EST/-5.0/no DST' instead in /home/frodr/public_html/msofficetuneup.com/wp-includes/functions.php on line 112
13
The queries you saw in the previous example use what database nerds call an inner join. Inner joins show only linked recordsin other words, records that appear in both tables. If you perform a query on the Customers and Orders tables, then you don’t see customers that haven’t placed an order. You also don’t see orders that aren’t linked to any particular customer (the CustomerID value’s blank) or aren’t linked to a valid record (they contain a CustomerID value that doesn’t match up to any record in the Customers table).
Outer joins are more accommodating these joins include all the same results you’d see in an inner join, plus the leftover unlinked records from one of the two tables (it’s your choice). Obviously, these unlinked records show up in the query results with some blank values, which correspond to the missing information that the other table would supply.
Suppose you perform an outer join between the Orders and Customers tables, and then configure it so that all the order records are shown. Any orders that aren’t linked to a customer record appear at the bottom of the list, and have blank values in all the customer-related fields (like FirstName and LastName):
|
FirstName |
LastName |
ID |
DatePlaced |
StatusID |
|---|---|---|---|---|
|
Stanley |
Lem |
7 |
13-Jun-07 |
Cancelled |
|
Toby |
Grayson |
4 |
03-Nov-06 |
Returned |
|
Toby |
Grayson |
6 |
03-Nov-06 |
Shipped |
|
18 |
01-Jan-08 |
In Progress |
||
|
19 |
01-Jan-08 |
In Progress |
In this particular example, it doesn’t make sense for orders that aren’t linked to a customer to exist. (In fact, it probably indicates an order that was entered incorrectly.) However, if you suspect a problem, an outer join can help you track down the problem.
Tip: You can prevent orphaned order records altogether by making CustomerID a required value (Section 4.1) and enforcing referential integrity (Section 5.2.3).
You can also perform an outer join between the Orders and Customers tables that shows all the customer records. In this case, at the end of the query results, you’ll see every unlinked customer record, with the corresponding order fields left blank:
|
FirstName |
LastName |
ID |
DatePlaced |
StatusID |
|---|---|---|---|---|
|
Stanley |
Lem7 |
7 |
13-Jun-07 |
Cancelled |
|
Toby |
Grayson |
4 |
03-Nov-06 |
Returned |
|
Toby |
Grayson |
6 |
03-Nov-06 |
Shipped |
|
Ben |
Samatara |
|||
|
Goosey |
Mason |
|||
|
Tabasoum |
Khan |
In this case, the outer join query picks up three stragglers.
So how do you add an outer join to your query? Your start with an inner join (which Access usually adds automatically; see Section 6.3.1), and then convert it to an outer join. To do so, just right-click the join line that links the two tables in the design window, and then choose Join Properties (or just double-click the line). The Join Properties dialog box (Figure 6-17) appears, and lets you change the type of join you’re using.
Figure 6-17. The first option, “Only include rows where the joined fields from both tables are equal”, performs the standard inner join. The other two options let you create an outer join that incorporates all the unlinked rows from one of the two tables.
Popularity: 4% [?]
Recent Comments