I've got a SQL nut I can't crack. A

[JeremyNYC]

I've got a SQL nut I can't crack. Any help would be greatly appreciated.

Here are the tables:
tblAccount
==========
AccountID
Other Stuff

TblUnit
=======
UnitID
UnitName

tblUnitEmployeeLU
=================
UnitID
EmployeeID
MemoNumber
CC (true/false)

tblEmployee
===========
EmployeeID
StuffIConcatenateToFullName

Here's what I've got now:
SELECT tblAccount.AccountName, tblUnit.UnitName, tblUnitEmployeeLU.MemoNumber, tblEmployee.FullName, tblUnitEmployeeLU.CC
FROM tblEmployee INNER JOIN ((tblAccount INNER JOIN tblUnit ON tblAccount.UnitID = tblUnit.UnitID) INNER JOIN tblUnitEmployeeLU ON tblUnit.UnitID = tblUnitEmployeeLU.UnitID) ON tblEmployee.EmployeeID = tblUnitEmployeeLU.EmployeeID
WHERE (((tblAccount.Status)="open&quot AND ((tblAccount.AccountID)>1) AND ((tblUnitEmployeeLU.MemoNumber)=1))
ORDER BY tblAccount.AccountName;

What I need to do is get another set of the last three displayed fields here, the stuff describing the employee. The second set of three fields would be for the folks associated with MemoNumber = 2. I don't want to end up with every possible match between the folks for memo 1 and the folks for memo 2, which is what I keep running into.

Here's the results from the above sql (with some field names changed so they fit here):
AcctNm UnitName MemoNo FullName CC
ACH PA Admin 1 pacheco, luis False
ACH PA Admin 1 Hamer, Lynn True

If there is only one person getting Memo 2 (assuming I add another set tblUnitEmployeeLU and tblEmployee), their name will show up on every line here. If there are two people getting memo 2, I'l get one set of recrds for each combination of memo 1 people and memo 2 people. I want to end up with people listed independently in the two sets of columns. If there are more people for one memo than for the other there should be blanks.

I'm not sure it's possible, but I'd sure love it if anyone could get me started on this.

Jeremy
Let me know if you have any thoughts. =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[mp9]

Not sure if I've got the gist of what you're after. I would keep your existing query (I've called it qryMemo1 in my SQL later), then copy it to qryMemo2 and change the WHERE clause to end with

((tblUnitEmployeeLU.MemoNumber)=

2

))

Then I'd create a third query something like:

SELECT qryMemo1.AccountName, qryMemo1.UnitName, qryMemo1.MemoNumber, qryMemo1.FullName, qryMemo1.CC, qryMemo2.MemoNumber, qryMemo2.FullName, qryMemo2.CC
FROM qryMemo1 LEFT JOIN qryMemo2 ON (qryMemo1.UnitName = qryMemo2.UnitName) AND (qryMemo1.AccountName = qryMemo2.AccountName);

Does this help? Or have I got the wrong idea about what you're after?

http://www22.brinkster.com/accessory/