Creating a pivot table like query

[razkowski]

Hello all...new to the board. I have a list of transactions (format below) and would like to create a query (or set of queries if necessary) to generate a result that is like an excel pivot table. The data in my table looks like this:

Transaction Date Net $ Product Number
7/2/2012 $50 1
6/15/2012 $35 2
5/10/2012 $40 3

Obviously this is an overly simplified version of my data. I'd like the query result to look like this:

Transaction Date (just year and month) Product 1 (Product Number - with net $ summed) Product 2 (Product Number - with net $ summed) Product 3 (Product Number - with net $ summed)

2012/05 $0 $0 $40
2012/06 $0 $35 $0


2012/07 $50 $0 $0

Each product can have many sales in a given month, so it's important that the net be totaled by product and month.

Thank you in advance for any help that can be provided.

[June7]

A crosstab should be able to do this.

Create field in query that calculates the date as year/month then the crosstab will use that value as GroupBy RowHeader. The ProductNumber would be ColumnHeader and Net$ would be the Value field.

YrMo: Year([Date]) & "/" & Format(Month([Date]),"00")

[razkowski]

That's the ticket, June7! Thank you very much for your help.