Help with query to show row data in columnar format

[rafpac]

Hi All,

I hope everyone is doing well. I am sorry and truly apologetic if this topic has already been shared. I have not been able to locate one maybe due to language or articulation disparity.

However I hope you all can help me.

I have two tables which join together in a query to give me the following data set:

Commonkey: reference number, Individual's Name, Status of a case, date of transaction and amount for the transaction.

Now I am trying to create a query to be able to summarise this data in the following format:

	Open Cases				Closed Cases
Individual	Most Recent Week: Wk38	Most Recent Week: Wk37	Most Recent Week: Wk36	Most Recent Week: Wk35	Most Recent Week: Wk38	Most Recent Week: Wk37	Most Recent Week: Wk36	Most Recent Week: Wk35
Harry	$100		$50	$1	$2		$15	$1
Susan		$20	$5,000		$100		$2,000
Gary		$20	$10	$30	$0	$0	$11	$12
Steve	$0	$0	$0	$0	$100	$5,000	$0	$80,000
Maya	$10,000	$200	$100	$50	$0	$0	$0	$0

I can format the date into weeks and summarize the data. But then everything is at a row level. The challenge I am facing is having the status data as a column and then having the weeks numbers which are calculated from the date field as a second column level and then summarizing the amount field.

Is there a way I can customize the query to do so? A cross tab only allows on level of column and will take all the data and create columns. I can't limit or restrict the data. Thanks in advance.

I am using Microsoft Office 365 suite - MS Acess

Raf

[Micron]

I don't think you can reproduce that with a query. Perhaps a report with 2 sub reports, one for Open and one for Closed cases side by side.
If you could post a pic of your table relationships I think that would preempt some questions. See "how to attach files" at the top of the page if you decide to do that.

[June7]

You are showing a structure that can be designed on a spreadsheet with merged cells. Queries don't work that way. The "Open" and "Closed" text would have to show above every column. A UNION query could probably do that. Or be included in the field used to generate column headers in a CROSSTAB.

I wonder if these two tables should really be one.