Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Queries / August 2006

Tip: Looking for answers? Try searching our database.

Need help with dynamic query

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
LLLLL - 15 Aug 2006 02:13 GMT
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
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.