Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Reporting information from multiple records in one row

Posted on 2006-03-29
9
Medium Priority
?
233 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
0
Comment
Question by:TerenceHewett
  • 5
  • 4
9 Comments
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16321293
Terry,

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

Patrick
0
 

Author Comment

by:TerenceHewett
ID: 16322111
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16322134
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Author Comment

by:TerenceHewett
ID: 16322310
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16323197
You're welcome, Terry.

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

Patrick
0
 

Author Comment

by:TerenceHewett
ID: 16323257
Thank you Patrick for your help.  It is much appreciated.

Regards,
Terry
0
 
LVL 93

Accepted Solution

by:
Patrick Matthews earned 2000 total points
ID: 16328474
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
 

Author Comment

by:TerenceHewett
ID: 16330711
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
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 16331400
Glad to help, Terry :)
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question