Query Tables Compare Results

[sdsocali]

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?

[SDas]

sdsocali, I don't use Access SQL syntax much but here is a starting point that should work:

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.

[sdsocali]

thanx for the quick reply. I will give it a try in the morning.

[sdsocali]

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?

[sdsocali]

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.

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.