Crosstab query help

[ringer]

I need help with a crosstab query. I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work . Could someone show me how this should be written?

Something like this is what I need:

_____________Ja n_____________F eb___________Ma r__________Apr

Power.......... ...1/3....200....... .......2/4...250........ .....3/7....225....... ..4/5.....250

Lease.......... ...1/15...1200...... ....2/15...1200...... ....3/16..1200....... 4/20...1200

Insurance...... .1/16...175....... ......2/20...175....... ............0.. ............... ....0
............... ............... ............... ..... 2/26...350

Water/Gas.......1/6...150........ .....2/10...175....... ....3/3...150........ ..4/10...175

Thanks a lot...

[Lysander]

I need help with a crosstab query. I would like the column headings to be the last 6 months, the row headings to be billers, and the data in the middle to be both the date that a payment was made (falling within the month headings) and also the amount paid in that payment. Sometimes there might be more than one payment to a biller in a month or there might be no payments to that biller in a month. All of the raw data needed is in one table. I have read about crosstab queries, tried to use the wizard, and looked at examples but I can't get it to work . Could someone show me how this should be written?

Something like this is what I need:

_____________Ja n_____________F eb___________Ma r__________Apr

Power.......... ...1/3....200....... .......2/4...250........ .....3/7....225....... ..4/5.....250

Lease.......... ...1/15...1200...... ....2/15...1200...... ....3/16..1200....... 4/20...1200

Insurance...... .1/16...175....... ......2/20...175....... ............0.. ............... ....0
............... ............... ............... ..... 2/26...350

Water/Gas.......1/6...150........ .....2/10...175....... ....3/3...150........ ..4/10...175

Thanks a lot...

A crosstab query can only have one field in the middle, not multiple fields. What you can do is first write a select query that will combine your date and amounts into one field, say, CombindedPaymen t.

Then create your crosstab against this query, using CombindedPaymen t as the middle field

[ringer]

A crosstab query can only have one field in the middle, not multiple fields. What you can do is first write a select query that will combine your date and amounts into one field, say, CombindedPaymen t.

Then create your crosstab against this query, using CombindedPaymen t as the middle field

Gotcha. Would you mind showing me how to write the crosstab query with only the dates in the middle? (With months -like Jan, Feb, Mar-- as column headers and the dates falling under the appropriate months)

[Lysander]

Gotcha. Would you mind showing me how to write the crosstab query with only the dates in the middle? (With months -like Jan, Feb, Mar-- as column headers and the dates falling under the appropriate months)

I just realised that in a crosstab query, the value in the middle has to be grouped, i.e. sum of payment, count of records, min of date etc. It can't be just the value.

This query gives min of date, using the crosstab wizard
[code=sql]
TRANSFORM Min(tblInvoice. DateCleared) AS MinOfDateCleare d
SELECT tblInvoice.Comp anyName
FROM tblInvoice
GROUP BY tblInvoice.Comp anyName
PIVOT Format([DateRaised],"mmm") In ("Jan","Feb","M ar","Apr","May" ,"Jun","Jul","A ug","Sep","Oct" ,"Nov","Dec" );
[/code]

[ringer]

Thanks. After more attempts with the wizard I got the results you posted above.
Do you think there's some way to use the crosstab query to pull in the results of another query for the middle data? My thought is that maybe the other query could be used to show the actual data without being summed, min'd, max'd, avg'd, or whatever.