Query to list items each person is responsible for

G

Guest

I have a table of action items. Each action item could have up to 3 people
responsible for completing the task, I need a query that will list action
items assigned to each person.

Table: Issues
ID
Action Item
Action Taken
ME Resp
CE Resp
TR Resp
DueDate

Table: Employee
EmpID
EmpLName
EmpFName

Resulting query should have the following info:
Employee.EmpLName Issue.ID Issue.Action Item Issue.Action Taken Issue.DueDate

If there is a different person listed for the following fields (ME Resp, CE
Resp, TR Resp) the query should return the same action item 3 times.
 
A

Allen Browne

Solution 1: Relational design
======================
Since one Issue can require action by several staff, it would be better to
create a 3rd table to hold the various people who need to action the item.

This table would have fields:
IssueID which issue this record is about;
EmpID who should action this isuue;
RoleID whatever ME, CE, an TR distinguishes.
You can now solve the problem easily (and many more problems that will be
difficult with your current structure.)

Solution 2: UNION query
==================
If you don't want to create the correct data structure, it is possible to
work around the issue with a UNION query, but this is much more messy. The
results will be read-only, functionality is reduced, and you cannot design
this kind of query in the interface (must use SQL view.)

Presumably you have already set up 3 relationships betwen the Issues table
and the Employee table, by opening the Relationships window (Tools menu),
adding ghe Employee table 3 times (Access will alias the extra copies as
Employee_1 and Employee_2), and creating the 3 relationships.

1. Create a query that uses both tables.
This one will return the [ME Resp] person, so you want only the line from
[ME Resp] to [EmpID]. If you see the other lines as well, delete them.

2. Drag that field and the others you wish to see into the grid.
Leave this query open.

3. Create another query based on the [CD Resp] employee, i.e. with just that
join. Output the fields you want. There must the the same number of fields,
of the same data type, in the same order. Leave this query open.

4. Create another query based on the [TR Rsp] employee, again with the same
number of fields, in the same order, of the same data type.

5. Switch all 3 queries to SQL View (View menu.)
Copy the 3 SQL statements into one big statement--one below the other.
Replace the 2 semicolons in the middle of the statment with:
UNION ALL
You will end up with something like this:
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...;

6. Test this query. Save it, and you can discard the other 2.

For my money, the first solution is about 10 times better.
 
G

Guest

Is there a way to generate the 3rd table from the two I already have? The
person who originally started this did it in Excel and there are already
hundreds of entries in the issues table.

Allen Browne said:
Solution 1: Relational design
======================
Since one Issue can require action by several staff, it would be better to
create a 3rd table to hold the various people who need to action the item.

This table would have fields:
IssueID which issue this record is about;
EmpID who should action this isuue;
RoleID whatever ME, CE, an TR distinguishes.
You can now solve the problem easily (and many more problems that will be
difficult with your current structure.)

Solution 2: UNION query
==================
If you don't want to create the correct data structure, it is possible to
work around the issue with a UNION query, but this is much more messy. The
results will be read-only, functionality is reduced, and you cannot design
this kind of query in the interface (must use SQL view.)

Presumably you have already set up 3 relationships betwen the Issues table
and the Employee table, by opening the Relationships window (Tools menu),
adding ghe Employee table 3 times (Access will alias the extra copies as
Employee_1 and Employee_2), and creating the 3 relationships.

1. Create a query that uses both tables.
This one will return the [ME Resp] person, so you want only the line from
[ME Resp] to [EmpID]. If you see the other lines as well, delete them.

2. Drag that field and the others you wish to see into the grid.
Leave this query open.

3. Create another query based on the [CD Resp] employee, i.e. with just that
join. Output the fields you want. There must the the same number of fields,
of the same data type, in the same order. Leave this query open.

4. Create another query based on the [TR Rsp] employee, again with the same
number of fields, in the same order, of the same data type.

5. Switch all 3 queries to SQL View (View menu.)
Copy the 3 SQL statements into one big statement--one below the other.
Replace the 2 semicolons in the middle of the statment with:
UNION ALL
You will end up with something like this:
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...;

6. Test this query. Save it, and you can discard the other 2.

For my money, the first solution is about 10 times better.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
LynneZ said:
I have a table of action items. Each action item could have up to 3 people
responsible for completing the task, I need a query that will list action
items assigned to each person.

Table: Issues
ID
Action Item
Action Taken
ME Resp
CE Resp
TR Resp
DueDate

Table: Employee
EmpID
EmpLName
EmpFName

Resulting query should have the following info:
Employee.EmpLName Issue.ID Issue.Action Item Issue.Action Taken
Issue.DueDate

If there is a different person listed for the following fields (ME Resp,
CE
Resp, TR Resp) the query should return the same action item 3 times.
Click to expand...
Click to expand...
 
A

Allen Browne

Sure.

Create the tables the right way, and then use a pair of Update queries to
populate them with the existing data.

In query design view, Update is on the Query menu.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

LynneZ said:
Is there a way to generate the 3rd table from the two I already have? The
person who originally started this did it in Excel and there are already
hundreds of entries in the issues table.

Allen Browne said:
Solution 1: Relational design
======================
Since one Issue can require action by several staff, it would be better
to
create a 3rd table to hold the various people who need to action the
item.

This table would have fields:
IssueID which issue this record is about;
EmpID who should action this isuue;
RoleID whatever ME, CE, an TR distinguishes.
You can now solve the problem easily (and many more problems that will be
difficult with your current structure.)

Solution 2: UNION query
==================
If you don't want to create the correct data structure, it is possible to
work around the issue with a UNION query, but this is much more messy.
The
results will be read-only, functionality is reduced, and you cannot
design
this kind of query in the interface (must use SQL view.)

Presumably you have already set up 3 relationships betwen the Issues
table
and the Employee table, by opening the Relationships window (Tools menu),
adding ghe Employee table 3 times (Access will alias the extra copies as
Employee_1 and Employee_2), and creating the 3 relationships.

1. Create a query that uses both tables.
This one will return the [ME Resp] person, so you want only the line from
[ME Resp] to [EmpID]. If you see the other lines as well, delete them.

2. Drag that field and the others you wish to see into the grid.
Leave this query open.

3. Create another query based on the [CD Resp] employee, i.e. with just
that
join. Output the fields you want. There must the the same number of
fields,
of the same data type, in the same order. Leave this query open.

4. Create another query based on the [TR Rsp] employee, again with the
same
number of fields, in the same order, of the same data type.

5. Switch all 3 queries to SQL View (View menu.)
Copy the 3 SQL statements into one big statement--one below the other.
Replace the 2 semicolons in the middle of the statment with:
UNION ALL
You will end up with something like this:
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...
UNION ALL
SELECT ...
FROM ...;

6. Test this query. Save it, and you can discard the other 2.

For my money, the first solution is about 10 times better.

LynneZ said:
I have a table of action items. Each action item could have up to 3
people
responsible for completing the task, I need a query that will list
action
items assigned to each person.

Table: Issues
ID
Action Item
Action Taken
ME Resp
CE Resp
TR Resp
DueDate

Table: Employee
EmpID
EmpLName
EmpFName

Resulting query should have the following info:
Employee.EmpLName Issue.ID Issue.Action Item Issue.Action Taken
Issue.DueDate

If there is a different person listed for the following fields (ME
Resp,
CE
Resp, TR Resp) the query should return the same action item 3 times.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query-What format do I use to obtain unspecified unique items? 2
double entries for each record when running a query 4
Multi-query issue 3
Query Calc. is Mult. # of Table rows--Y? 3
Append query won't insert into linked ( MS-Access ) table.... 2
query for values not in use 2
How to display a list box with select items in edit mode 1
SendObject (send an unique query to each of a list of recipients) 13

Top