Reporting information from multiple records in one row

Hi experts,

I am having trouble reporting information from multiple records into one row of a report.  I have tried to simplify my problem and my table can be represented as follows:


Task     TaskMemo                  Task         Person          Person
MemoID                                  Priority     Assigned       Assigned ID
                                                             to Task          
     
1          Assess emergency             1        John                 2
2          Call emergency services     2        George             1
3          Evacuate                           3        George             1
4          Evacuate                           3        Bella                3
5          Evacuate                           3        Susan              4
6          Evacuate                           3        John                2


The report I am trying to produce should look as follows:

Task Memo                         TaskPriority     Tasks assigned   Tasks assigned    Tasks assigned   Tasks assigned      
                                                                to John              to George            to Susan            to Bella
               
Assess emergency                      1                 X

Call emergency services              2                                            X

Evacuate                                    3                 X                         X                        X                       X


The best I have managed to achieve so far is:

Task Memo                         TaskPriority     Tasks assigned   Tasks assigned    Tasks assigned   Tasks assigned      
                                                                to John              to George            to Susan            to Bella
               
Assess emergency                      1                 X

Call emergency services              2                                            X

Evacuate                                    3                 X                        

                                                                                               X                                                

                                                                                                                         X                      
   
                                                                                                                                                  X

The above is representative of my problem and I am working with quite a large database.  Unfortunately it is important that I manage to put all four persons allocated to a particular task in one row.   I am using MS Access 2000.  Can anybody help?

With many thanks,

Terry
TerenceHewettAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Terry,

Should be accomplished easily enough.  Please post the SQL for the report as it is now.

Patrick
0
TerenceHewettAuthor Commented:
Hi Patrick,

Our report currently contains 5 queries and 4 IF statements.  Is it possible to email you a copy of the report?

Thank you.

Terry
0
Patrick MatthewsCommented:
Terry,

Please do not email it to me, as that is against the rules.

Can you explain some more about how the report is structured, and what SQL is involved for each part?
Or, provide a slimmed down example database with the report in it?  (Whenever I need to provide a
sample, I upload a file to the free web site I got at www.geocities.com, and post a link to the file in
the question.)

Patrick
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

TerenceHewettAuthor Commented:
Hi Patrick,

I have posted the file containing the table and the report to the following link.

www.geocities.com/terencehewett/database.mdb

Many thanks for all your help.

Terry
0
Patrick MatthewsCommented:
You're welcome, Terry.

I may not get to this until tonight, so any Experts lurking, please jump in :)

Patrick
0
TerenceHewettAuthor Commented:
Thank you Patrick for your help.  It is much appreciated.

Regards,
Terry
0
Patrick MatthewsCommented:
Terry,

To be honest, I am not at all sure what those subreports are supposed to be doing--they do not appear to be
doing anything now.

Anyway, try adding a new query with the following SQL:

SELECT TestTable.TaskMemo, TestTable.TaskPriority,
IIf((SELECT Count(*) FROM TestTable AS T1 WHERE T1.PersonAssignedToTask = "John" AND T1.TaskMemo = TestTable.TaskMemo) > 0, "X", "") AS John,
IIf((SELECT Count(*) FROM TestTable AS T2 WHERE T2.PersonAssignedToTask = "George" AND T2.TaskMemo = TestTable.TaskMemo) > 0, "X", "") AS George,
IIf((SELECT Count(*) FROM TestTable AS T3 WHERE T3.PersonAssignedToTask = "Susan" AND T3.TaskMemo = TestTable.TaskMemo) > 0, "X", "") AS Susan,
IIf((SELECT Count(*) FROM TestTable AS T4 WHERE T4.PersonAssignedToTask = "Bella" AND T4.TaskMemo = TestTable.TaskMemo) > 0, "X", "") AS Bella
FROM TestTable
GROUP BY TestTable.TaskMemo, TestTable.TaskPriority
ORDER BY TestTable.TaskPriority, TestTable.TaskMemo;

Now, use the report wizard to make a new report based off of that query.

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TerenceHewettAuthor Commented:
Patrick,

Thank you very much for the help and effort you have put into this question.  I did not know how to code a query as you have done above, so I have now learnt a massive amount from you.

Many thanks,

Terry
0
Patrick MatthewsCommented:
Glad to help, Terry :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.