TerenceHewett
asked on
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
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
ASKER
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
Our report currently contains 5 queries and 4 IF statements. Is it possible to email you a copy of the report?
Thank you.
Terry
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
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
ASKER
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
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
You're welcome, Terry.
I may not get to this until tonight, so any Experts lurking, please jump in :)
Patrick
I may not get to this until tonight, so any Experts lurking, please jump in :)
Patrick
ASKER
Thank you Patrick for your help. It is much appreciated.
Regards,
Terry
Regards,
Terry
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
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
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
Glad to help, Terry :)
Should be accomplished easily enough. Please post the SQL for the report as it is now.
Patrick