We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Reporting information from multiple records in one row

TerenceHewett
on
Medium Priority
245 Views
Last Modified: 2006-11-18
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
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2010

Commented:
Terry,

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

Patrick

Author

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
CERTIFIED EXPERT
Top Expert 2010

Commented:
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

Author

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
CERTIFIED EXPERT
Top Expert 2010

Commented:
You're welcome, Terry.

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

Patrick

Author

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

Regards,
Terry
CERTIFIED EXPERT
Top Expert 2010
Commented:
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

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Author

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
CERTIFIED EXPERT
Top Expert 2010

Commented:
Glad to help, Terry :)
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.