Combining Tables

<< Click to Display Table of Contents >>

Navigation:  Other Topics > Smart Access 1996-2006 > Feb-2003 >

Combining Tables

Peter Vogel

 

This month, Peter Vogel looks at reporting from two tables

where one table overrides entries in the other table.

I have a master table of data that I want to report on.

However, I also have a table of daily data. Where there’s a

matching record in the daily table for a record in the master

table, I want to use the daily table’s record.

 

I have a solution that uses a SQL statement, but let me

work up to it. I’ll start with a query that will show all of

your master records that don’t have a corresponding

record in the daily table:

 

Select tblMaster.*

From tblMaster Left Outer Join tblDaily

On tblMaster.PrimaryKey = tblDaily.PrimaryKey

Where tblDaily.someField Is Null

 

This query joins tblMaster to tblDaily using an

outer join—an outer join that forces all of the records from

one table into the query, even if there isn’t a matching

record in the joined table. In this case, because I’ve used a

LEFT outer join, all the records in the table on the left

(tblMaster) are forced in. For those tblMaster records

where there’s no matching tblDaily record, the fields in

tblDaily will be set to Null. The Where clause checks for

the Null in a tblDaily field and selects only those records

where there’s no matching tblDaily record.

The query, on the other hand, selects those tblDaily

records where there’s a matching tblMaster record:

 

Select tblDaily.*

From tblDaily Inner Join tblMaster

On tblMaster.PrimaryKey = tblDaily.PrimaryKey

 

An inner join only finds records where there’s a

match, so this query pulls out all the daily records where

there’s a matching history record.

Together these two queries give you the records

that you want. To put them together, you use the

Union operator:

 

Select tblMaster.*

From tblMaster Left Outer Join tblDaily

On tblMaster.PrimaryKey = tblDaily.PrimaryKey

Where tblDaily.someField Is Null

Union

Select tblDaily.*

 

Read more in the pdf article   Combining Tables