Outer Join on Two Fields?

  • Thread starter Thread starter scottchampion
  • Start date Start date
S

scottchampion

I've got the following two queries:

Query1:
qsub1.PeriodStartDate
qsub1.EmployeeID

Query2:
qTotWkly.PeriodStartDate
qTotWkly.EmployeeID
qTotWkly.Response

I need to display all of the PeriodStartDate and EmployeeID
combinations contained in Query1. If the PeriodStartDate and
EmployeeID combination occurs in Query2 then I need to display the
respective Response. If the PeriodStartDate and Employee ID
combination does not occur in Query2 then I need to display Null or
zero.

The problem that I have is that I cannot come up with a way to join
Query1 and Query2 that will allow me to give the correct results.

Any ideas on how to handle this?
 
I'm not an expert but I think the easiest way would be to create two
other queries with an added field that is a result of
concatentinating EmployeeID and PeriodStartDate together, creating a
primary key that you can join the tables on.
I'm having trouble with the concatenation of the two fields. get sytax
error.
I'd like to know the answer to this also; If you get it, please let me
know.
thanks,
Jeri
 
In the query builder, you should be able to display both queries and then
click and drag fields from on query to the other to create you join. You
have to right-click on the join and choose Join Properties to create the
Outer Join. You have to do this with each Join independently.

In SQL View, it would look something like this:
SELECT Query1.PeriodStartDate, Query1.EmployeeID, Query2.Response
FROM Query1 LEFT JOIN Query2 ON (Query1.PeriodStartDate=
Query2.PeriodStartDate) AND (Query1.EmployeeID= Query2.EmployeeID)

--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
I'm sure my idea of creating the primary key is the way to go for you
to join two tables based on two fields...
I got the concatination part to work, so I will share that with you
here:
[ssn] & format([paydate],"yyyymmdd") AS matchkey;
of course substitute your fields.
hth,
Jess
 
Thanks,
better than the extra step of creating the primary keys.
btw, It wouldn't allow the join on 2 fields in design view.
Jess
Old Mainfraimer, trying to get current.
 
Back
Top