Two tables joint...

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

Hi anyone,
I have two queries.
2 fields in each. Date, SomeInfo;

I need to make a third query that will return:
All the info. If there is missing info in one query for
the date for which other query has some number, i would
like to have 0 for the corresponding date in that query
column.


Date SomeInfo(from Query1) SomeInfo(from Query2)

09/08/ 100 200
10/08 0 300
11/08 400 0

I would like to have information displayed for any date
that those two queries returned.

Right join, Left join on date does not work. I Need All
the Dates returned. if i use no join at all it gives me
too many rows with unappropriate data in columns..

Can it be done? Thanks for any suggestions.

alex
 
It can be done. Append both the queries dates to a temp
table that has only one field "myDate" that is indexed to
be unique. Then create a new query that has the temp table
and left join to the first two queries, via the date
fields and bingo your result.
 
Right join, Left join on date does not work. I Need All
the Dates returned.
Click to expand...

What you need is a "Full Outer Join" which unfortunately is not
supported in Access.

A getaround is to use a UNION query:

SELECT Query1.[Date], [Query1].[SomeInfo] AS SomeInfo1,
NZ([Query2].[SomeInfo], 0) AS SomeInfo2
FROM Query1 LEFT JOIN Query2
ON Query1.[Date] = Query2.[Date]
UNION ALL
SELECT Query1.[Date], NZ([Query1].[SomeInfo]), 0), [Query2].[SomeInfo]
FROM Query1 RIGHT JOIN Query2
ON Query1.[Date] = Query2.[Date];
 
Back
Top