Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2002
    Location
    Belgium
    Posts
    243

    Unanswered: Wanted : Query-specialist

    I have 2 tables :
    - tblDepartmentPerEmployee (159 records)
    --> contains 2 fields :
    - aov_wn_nr wich is employeeID
    - ao2_afd which is the departmentcode the employee belongs to

    - tblAccessRightsCurrentUser (3 records)
    --> contains 1 field :
    - kp wich represents de departments the CurrentUser have access to.

    My question is quite simple, however I don't get it working.
    What I want is filtering from the first table all the employees who belong to a department which are specified in the second table.

    That means (in this example) all aov_wn_nr where ao2_afd = 92353 or 92354 or 92355.
    The result-table should contain 36 records.

    I tried this :

    Code:
    SELECT aov_wn_nr 
    FROM tblDepartmentPerEmployee 
    WHERE val(tblDepartmentPerEmployee.ao2_afd) IN (SELECT * FROM tblAccessRightsCurrentUser);
    It looks good to me but the result only shows me the employees who belong to department 92353.

    Is there anyone who can help me out?

    Be aware that depending on the currentUser these numbers vary, so I can not use these values in the where-statement.
    Also notice that ao2_afd is a string and kp is nummeric. [/SIZE][/QUOTE]
    Attached Files Attached Files
    Last edited by artemide; 08-12-03 at 04:54.

  2. #2
    Join Date
    Aug 2003
    Location
    Delft, The Netherlands (EU)
    Posts
    447

    Re: Wanted : Query-specialist

    Originally posted by artemide
    I have 2 tables :
    - tblDepartmentPerEmployee (159 records)
    --> contains 2 fields :
    - aov_wn_nr wich is employeeID
    - ao2_afd which is the departmentcode the employee belongs to

    - tblAccessRightsCurrentUser (3 records)
    --> contains 1 field :
    - kp wich represents de departments the CurrentUser have access to.

    My question is quite simple, however I don't get it working.
    What I want is filtering from the first table all the employees who belong to a department which are specified in the second table.

    That means (in this example) all aov_wn_nr where ao2_afd = 92353 or 92354 or 92355.
    The result-table should contain 36 records.

    I tried this :

    Code:
    SELECT aov_wn_nr 
    FROM tblDepartmentPerEmployee 
    WHERE val(tblDepartmentPerEmployee.ao2_afd) IN (SELECT * FROM tblAccessRightsCurrentUser);
    It looks good to me but the result only shows me the employees who belong to department 92353.

    Is there anyone who can help me out?

    Be aware that depending on the currentUser these numbers vary, so I can not use these values in the where-statement.
    Also notice that ao2_afd is a string and kp is nummeric.
    [/SIZE][/QUOTE]


    try:

    SELECT aov_wn_nr
    FROM tblDepartmentPerEmployee, tblAccessRightsCurrentUser
    WHERE val(tblDepartmentPerEmployee.ao2_afd) = tblAccessRightsCurrentUser.kp;

    It's a kind of INNER JOIN, but since the data types differ, you have to do it this way.

    Hope this helps,
    Make everything as simple as possible, but not simpler! - A. Einstein
    DB Problems? DB Explorer, BTrieve Re-engineering, DB Conversions & ETL? Conversion Tool

Posting Permissions

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