i have 2 tables, one with employees and the other with 10 locations (branch
1, branch 2, branch3 etc.). Each employee must select a branch, but cant use
the same branch twice, so if user1 used branch 5, I want the query to ONLY
show 1,2,3,4,6,7,8,9,10 for user 1. (The ones he hasnt used yet!)
Sorry if it sounds confusing.
Thanks
Duane Hookom - 15 Aug 2006 04:18 GMT
Normalize, normalize, normalize.
http://www.datamodel.org/NormalizationRules.html

Signature
Duane Hookom
MS Access MVP
>i have 2 tables, one with employees and the other with 10 locations (branch
> 1, branch 2, branch3 etc.). Each employee must select a branch, but cant
[quoted text clipped - 3 lines]
> Sorry if it sounds confusing.
> Thanks
LLLLL - 15 Aug 2006 15:21 GMT
I understand your point, but I cant redesign teh database at this point.
Does anyone have another suggestion?
> Normalize, normalize, normalize.
> http://www.datamodel.org/NormalizationRules.html
[quoted text clipped - 6 lines]
> > Sorry if it sounds confusing.
> > Thanks
John Spencer - 15 Aug 2006 16:09 GMT
Divide and conquer with three queries
QueryA:
SELECT UserID, BranchId
FROM Employees, Branches
QueryB:
SELECT UserID, BranchID
FROM Employees
QueryC:
SELECT QueryA.UserID, QueryA.BranchID
FROM QueryA LEFT JOIN QueryB
ON QueryA.UserID = QueryB.UserID AND
QueryA.BranchID = QueryB.BranchID
WHERE QueryB.UserID is Null and QueryB.BranchID is Null
You can try to do this all in one query as follows
SELECT QueryA.UserID, QueryA.BranchID
FROM (
SELECT UserID, BranchId
FROM Employees, Branches
) as QueryA
LEFT JOIN (
SELECT UserID, BranchID
FROM Employees
) as QueryB
ON QueryA.UserID = QueryB.UserID AND
QueryA.BranchID = QueryB.BranchID
WHERE QueryB.UserID is Null and QueryB.BranchID is Null
>I understand your point, but I cant redesign teh database at this point.
> Does anyone have another suggestion?
[quoted text clipped - 12 lines]
>> > Sorry if it sounds confusing.
>> > Thanks