sequencial Join option

[Yossi evenzur]

Hi
let's say i have 2 tables with identical structure, date and value for
example. These 2 tables contains overlapping data for example, the first
table ends at 30/04/2005 and the other one starts at 01/01/2005. there is no
option in the relationships to join 2 tables with the following : only
include rows where the join fields are not equal

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...3c9&dg=microsoft.public.access.tablesdbdesign

 

[Jeff Boyce]

Perhaps another approach would be to NOT use more than one table when the
data fields/structure is identical. You might do that if you were using a
spreadsheet, but it is not a good design when you're using a relational
database.

If the only difference between the two tables is the date range, use a
single table with the date field include (not range, date value).

Then use queries to "isolate" those date ranges of interest.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Jeff Boyce]

(and if this is data from an 'outside' source that you do not wish to import
into Access, take a look at a UNION query in Access HELP)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Clifford Bass]

Hi Yossi,

You are describing a form of a full outer join. However, if Jeff's
suggestion about date ranges do not apply due to records in the overlapping
date range including a mix of records, some in one table only, some in the
other table only and some in both tables, you can use a union of a couple of
queries that use left (or right) outer joins. Something like:

select a.*
from a left outer join b on b.key = a.key
where b.key is null
union
select b.*
from b left outer join a on a.key = b.key
where a.key is null

Hope that helps,

Clifford Bass

 

[Yossi evenzur]

Hi, first, i don't have any control over the data table as it comes from
"outside". i wanted to see if you can add another type of "join", i'm well
familiar with SQL options, i simply don't know SQL very well. all other
suggestions are rather painful because i have to take 12 tables, cut the
useless dates and than paste them into 6 tables, each table has thousands of
rows and over 100 fields, so you can imagine the scope of work.