By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,589 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,589 IT Pros & Developers. It's quick & easy.

Crosstab query help

P: 32
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___________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...
May 31 '07 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 344
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___________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, CombindedPayment.

Then create your crosstab against this query, using CombindedPayment as the middle field
May 31 '07 #2

P: 32
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, CombindedPayment.

Then create your crosstab against this query, using CombindedPayment 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)
Jun 1 '07 #3

Expert 100+
P: 344
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
Expand|Select|Wrap|Line Numbers
  1. TRANSFORM Min(tblInvoice.DateCleared) AS MinOfDateCleared
  2. SELECT tblInvoice.CompanyName
  3. FROM tblInvoice
  4. GROUP BY tblInvoice.CompanyName
  5. PIVOT Format([DateRaised],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
  6.  
Jun 1 '07 #4

P: 32
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.
Jun 1 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.