dcsimg
Results 1 to 5 of 5

Thread: Query Tables Compare Results

  1. #1
    Join Date
    Nov 2009
    Posts
    4

    Query Tables Compare Results

    I can not figure out how to do this. I have 3 tables (blProject, upProject, TASKS). TASKS has all the information i want. The other two tables have are needed to narrow down the results. the tables look as follows.

    tbl blProject
    ----------
    proj_id

    tbl upProject
    ----------
    proj_id

    tbl TASKS
    ----------
    proj_id
    act_id
    proj_name
    start_date

    So tbl TASKS has over 100 proj_id I want to narrow it down to 2 using the blProject and upProject. Once I have that narrowed down there their will be some duplicate act_id. I want to show those that are not duplicated. So if the result is sam,sam,tom,jerry,sam for act_id the result will only be tom,jerry.

    Can anyone figure this out or do i need to create 2 querys then compare the 2?

  2. #2
    Join Date
    Apr 2009
    Posts
    86
    sdsocali, I don't use Access SQL syntax much but here is a starting point that should work:

    Code:
    SELECT ACT_ID
    FROM TASKS
    WHERE PROJ_ID IN(SELECT PROJ_ID
                     FROM BLPROJECT
                     )
      AND PROJ_ID IN(SELECT PROJ_ID
                     FROM UPPROJECT
                     )
    GROUP BY ACT_ID
    HAVING COUNT(*) = 1
    This just a basic starting point from what little I can get from your post. If may need modification depending on other requirements.

  3. #3
    Join Date
    Nov 2009
    Posts
    4
    thanx for the quick reply. I will give it a try in the morning.

  4. #4
    Join Date
    Nov 2009
    Posts
    4
    SDas the code you provided does not seem to work in access specifically the "IN" statement. Below is the code i came up with and it seems to work.

    SELECT OAUSER_TASK.task_name
    FROM OAUSER_TASK, q_z_blProject, q_z_upProject
    WHERE (((OAUSER_TASK.proj_id)=[q_z_blProject.proj_id] Or (OAUSER_TASK.proj_id)=[q_z_upProject.proj_id]))
    GROUP BY OAUSER_TASK.task_name
    HAVING (((Count(*))=1))
    ORDER BY OAUSER_TASK.task_name;

    The problem i am now running into is i want to show more columns from OAUSER_TASK in my results. I am not sure how to do that. If i add a column it just shows all results even the duplicates. Do you have any suggestions?

  5. #5
    Join Date
    Nov 2009
    Posts
    4
    Here is a new question. I have modified some front end Access stuff to only show a few options in the TASK table. I can get the query to just show non duplicate records but i want to be able to fine tune it more.

    Code:
    SELECT OAUSER_TASK.task_name, OAUSER_TASK.task_code
    FROM OAUSER_TASK
    GROUP BY OAUSER_TASK.task_name, OAUSER_TASK.task_code
    HAVING (Count(*)=1)
    ORDER BY OAUSER_TASK.task_name;
    Here is what the table looks like
    --------------------
    proj_id | task_name
    12 | A
    13 | A
    12 | G
    12 | B
    13 | B
    13 | C
    13 | D
    ---------------------
    Instead of the above query returning
    --------------------
    proj_id | task_name
    12 | G
    13 | C
    13 | D
    --------------------
    I want to be able to show only proj_id 13.
    I can not figure out how to get that into the query.

Tags for this Thread

Posting Permissions

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