Count function to return unique records

[accessdummy]

Not sure if I worded my subject correctly. Here's my problem.. I've used query to combine 2 tables of data..

Here are my fields..

I've assignment_num field, project_num field, project_name field, emplyee_name field.

here are my data..

assignment_num field: 1001, 1002, 1003, 1004, 1005
project_num field: 20,20,15,16,15
employee_name field: tom peter, tom peter, tom peter, julie, sally

What I want is to display the employee name with 2 projects or more...

What i have done?
I've done a group by and then count project_num field.. I also added a condition whereby the countofproject_num > 1.. everything went well except that the countofproject_num display 3 instead 2..

pls help.

 

[Brianwarnock]

I presume that it tom peter who has 3. You need to run a query using Group By for both the name and project, then run your query with this as input.

Brian

 

[accessdummy]

I presume that it tom peter who has 3. You need to run a query using Group By for both the name and project, then run your query with this as input.

Brian

nono.. tom peters should have 2 not 3.. can help rectify this problem? i use a group by for assignment_num, employee_name..

 

[Brianwarnock]

I meant that it was tom peters who was giving 3 when you wanted 2, I don't see how you can include the assignment_num in this query, which one do you want to return for tom peter?

 

[accessdummy]

Hmm.. here is the complete story.. I've an assignment table, an employee table, a project table. Each employee is given an assignment (of various tasks) for the projects assigned.. which means to say.. that particular employee can have many assignments for the same project or can have 1 assignment for each project. therefore, in this case, tom peters has 3 assignments for 2 projects.. however, i need to know how many employees are doing more than 1 project regardless of the number of assignments given to them.

my assignment tbl includes:
assignment_number field
employee_num field
project_num field
assignment_hours field

my employee tbl includes
employee_num field
employee_firstname field
employee_lastname field

my project tbl includes
project_num field
project_name field

 

[Jon K]

You need a series of two queries. See the queries in the database.

Run the second query.
.

 

[Brianwarnock]

Agreed Jon I think that's where we where at post 2, I guess an example is better than tring to explain

Brian

PS why doesn't it show that i've looked at your db?

 

[accessdummy]

therefore, i should create a query to merge the relevant fields i want?

query one should include

employee_num from assignment table
employee_name from employee table
project_num from assignment table
project_name from project table

all fields to be group by

query two should count for project_num

am i right?

 

[accessdummy]

anyway, thanks alot.. just that i face a little problem when i wan to show off in the query2 the project name.. i can't.. sigh.

 

[Brianwarnock]

So what you actually want to do is find all the employees working on more than one project and list them with the projects that they are working on?

So take the output from query 2 and join it to the relevent tables and list the info regarding the employees.

Actually as employee number appears to be unique as it should be then

query1 group by on employee_num and Project_num
query2 to take in query 1 but group by employee_num count project_num criteria >1
query 3 join query2 to tables on employee_num and select all required fields


Brian

 

[accessdummy]

hmm.. sorry but how to join query 2 to query 3?

 

[Brianwarnock]

NO no , query 3 uses query2 and the tables in a join , query 2 will have all the employee numbers for employees with more than 1 project, use this to select the employees from the table

Brian

 

[accessdummy]

thank you so much. you guys rock!!!!!

 

[Brianwarnock]

Happy to help, glad we got there

Brian