filtering records in a report

[crescent-centre]

I have a docuemnt control database which registers documents in with number, title, date. Then in a sperate form records each department review, date, reference number, etc. All this works entering the date. Have now been asked to produce a spreadsheet type report. I need to filter the data so column 1 shows department A review, column 2 - department B review, etc. I need to put a filter in each column something like
if department = A then show only reference associated with A, next column B.
Have been trying all day with no luck. Any help - please?

 

[x0reset]

Try using a crosstab query.

Setup with "DepartmentId" as the column heading, "DocumentTitle" as the Row Heading, and "Date" as the Value.

I can't really tell exactly what information you want to display in the query, but this should point you in the right direction.

 

[crescent-centre]

A simplified version of what I have is
TblDocuments
counter [autonumber, pk]
docnumber [number]
title [text]

TblActions
ActionCounter [number]
refnumber [text]
date [date]
originator [text]

relationship is each doc can have many actions.
example: doc-1, Ref-a, 1 Jan, john
doc-1, ref-b, 1 feb, fred
doc-1, ref-c, 10 feb, bill
doc-2, ref-d, 1 march, john

I want the report to look like
doc-1 1 jan Ref-a john 1 feb ref-b fred 10 feb ref-c bill
doc-2 1 march ref-d john

At the moment I get
doc-1 1 jan ref-a john
doc-1 1 feb ref-b fred
doc- 1 10feb ref-c bill
doc-2 1 mar ref-d john

Hope this help explain things clearer.