Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2007
    Posts
    5

    Unanswered: Design Problem - Filtering Combo box items based on related table field

    Hi im stuck on filtering a combo box (i am using an sql query to populate a combo box with a filtered selection, this is in Access but im after some design sanity checking). I have users who can be assigned a category. Jobs can be assigned a category and also have a list of people working on that job (in the JobDetails table). I have the following tables:

    Categories Table:
    CategoryID (PK)
    Description


    Users Table:
    UserID (PK)
    Username
    CategoryID (FK on Categories.CategoryID)


    Jobs Table:
    JobID (PK)
    CategoryID (FK on Categories.CategoryID)


    JobDetails Table:
    JobID (FK on Jobs.JobID)
    UserID (FK on Users.UserID)

    Then in the job details when listing users for a job (many users can be for one job) i would like to only show the users which have the same category as the jobs category. Is this possible? i tried the SQL below for the lookup column field JobDetails.UserID but it doesnt work:

    SELECT Users.ID, Users.Username, Users.CategoryID
    FROM Users, Jobs
    WHERE (((Users.CategoryID)=[Jobs].[CategoryID]));

    All the tables are linked with relationships but my SQL isnt so hot!
    Any ideas as to how i would do this and get it working?

    Even if it can be done, is this even recommended? I can see funny conditions happening if the job details category changes or the users category changes then even if they are existing in the job details list they will not be shown? Even so, i would be interested in the above to know how it is done (if possible).

    Thanks in advance,

    Chris

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Couple of things ...

    Don't put the Cat ID in the user table. It doesn't belong there ... What if the user changes categories? Or what if they belong to multiple categories???? Make another table to hold that relationship.


    What is the order of your selection? Category, user, job? User, category, job? Job, Category, User?

    This order will help determine the best way to query for what you want and when to do it ...
    Back to Access ... ADO is not the way to go for speed ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •