Crosstab query

[home70]

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:

_______________Jan______________Feb_____________Mar__________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...

 

[Anchoress]

Can you say a little bit about your data?

 

[home70]

Can you say a little bit about your data?

I don't know what you mean. What would you like to know?

 

[Tiro Cupidus]

I think Anchoress would like to see the relevant table structures.

 

[home70]

There are more fields in the table, but these are the ones I need in the query. Let me know if I'm leaving out anything important.

All data is in one table of transactions, named tblTransactions

Fields:

TransDate (date)
Payee (text)
Amount (currency-dollars)

In the query (as shown in my original post above) I need the column headings to be the months of the year and have the payment amounts and date fall under the appropriate month.

 

[Tiro Cupidus]

I think your best option is to create a form with a PivotTable view. The Record Source of the form should be those 3 fields from your table, pulling only records from the last 6 months. The fields also need to be added to the form in design view. When you first look at the PivotTable view, you'll need to drag Payee to the Row Fields, TransDate: By Month to the Column Fields, and then TransDate and Amount to the Detail Fields. Expand the Year and Quarter to show months, then you can right click on the Year and Quarter to remove them.

 

[home70]

I think your best option is to create a form with a PivotTable view. The Record Source of the form should be those 3 fields from your table, pulling only records from the last 6 months. The fields also need to be added to the form in design view. When you first look at the PivotTable view, you'll need to drag Payee to the Row Fields, TransDate: By Month to the Column Fields, and then TransDate and Amount to the Detail Fields. Expand the Year and Quarter to show months, then you can right click on the Year and Quarter to remove them.

I'm not familiar at all with pivot*, would this allow me to achieve my ultimate goal which is a report showing what my original post shows? My thought on asking about how to get the query done was that I would then translate that data to a report.

 

[Tiro Cupidus]

I actually made my first-ever PivotTable form view to test this. The way the data is arranged looks exactly like the example in your OP.

Create a new form with this Record Source:

SELECT TransDate, Payee, Amount FROM tblTransactions WHERE TransDate > Date() - 180 ORDER BY TransDate;

Date() - 180 gets you every record in the last 180 days. I'm unaware of a simple function to show each of the last 6 months in their entirety.

Once you set the RecordSource, your field list should pop up automatically. Drag all 3 fields to the form. On the Format tab of the form properties, change the Default View to PivotTable, then view the form in PivotTable view.

As far as accomplishing the exact configuration in a report, I'm not sure off the top of my head. You can certainly group the data by Payee, Month, but it would look more like a list than a table.

 

[Anchoress]

I think Anchoress would like to see the relevant table structures.

Yeah, that's what I meant. Looks like it's all figured out now, I may try it out myself!

 

[home70]

I actually made my first-ever PivotTable form view to test this. The way the data is arranged looks exactly like the example in your OP.

Create a new form with this Record Source:

SELECT TransDate, Payee, Amount FROM tblTransactions WHERE TransDate > Date() - 180 ORDER BY TransDate;

Date() - 180 gets you every record in the last 180 days. I'm unaware of a simple function to show each of the last 6 months in their entirety.

Once you set the RecordSource, your field list should pop up automatically. Drag all 3 fields to the form. On the Format tab of the form properties, change the Default View to PivotTable, then view the form in PivotTable view.

As far as accomplishing the exact configuration in a report, I'm not sure off the top of my head. You can certainly group the data by Payee, Month, but it would look more like a list than a table.

I followed your instructions and they worked for me. I got a form (strangest form I've ever seen, haha) and though it has the info and arrangement I'm looking for, I don't think that it is exactly what I have been trying for. From what I've read I think a crosstab query (then a report based on that crosstab query) is what I'm looking for.

 

[home70]

After more experimentation with the query wizard I got a result that looks similar to what I need. This is the code:
TRANSFORM Sum(qCheckbookRegisterReport.AmountDebit) AS SumOfAmountDebit
SELECT qCheckbookRegisterReport.Payee, Sum(qCheckbookRegisterReport.AmountDebit) AS [Total Of AmountDebit]
FROM qCheckbookRegisterReport
WHERE DateTransaction>Date()-180
GROUP BY qCheckbookRegisterReport.Payee
PIVOT Format([DateTransaction],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

Now I need help tweaking it. The way it displays the amounts in the middle is a total of a month's transactions (like if I made 2 payments in a month it shows the total of the 2 instead of listing them individually) and another problem is that I need the dates of the transactions to be listed next to the amounts.

Thank you...