Terrible time with a crosstab query

[homer70]

I am having no success getting this thing done. I have designed a little db I use to keep track of expenses and bank account transactions that I've been using for a year or so and it helps greatly, but I need a report that I can't seem to figure out how to do. I've searched several sites but can't find any examples of what I'm trying to do. I'm trying to get a report that shows my monthly bill payments, and the payment dates and amounts that were paid. Like this:

_____________Jan__________Feb___________Mar___________Apr

Payee

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

Lease..........1/25..1200.......2/26..1200.........3/21..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

As you can see I need to have the payment date and payment amount for each payment together under the appropriate month and also as you can see, some months have more than one payment and some have none at all.

This seems to be pretty complicated, at least it is to me, so I appreciate any help anyone can give me. Let me know if I should post more info than I have.
Thanks to all (in advance) for your help.

 

[dhookom]

I would create one subreport that has exactly the format you want to see under each month. Add two columns in your subreport's record source
[blue] Mth: Month([Transaction Date Field])
Yr: Year([Transaction Date Field])[/blue]

Then make sure you have a [Yr] field in your main report's record source. Add twelve text boxes across the detail section of your report:
[blue] Name: txt1
Control Source: =1
Visible: No

Name: txt2
Control Source: =2
Visible: No
--- etc ---
Name: txt12
Control Source: =12
Visible: No [/blue]

Then add 12 copies of your subreport across the detail section. Set the link Master properties to the Yr field from the main report's record source and one of the text boxes for example:
[blue] Link Master: [Yr], [txt1]
Link Master: [Yr], [txt2]
--- etc ---
Link Master: [Yr], [txt12][/blue]
Set the Link Child properties all the same:
[blue] Link Child: [Yr], [Mth][/blue]

This should then limit each subreport to the appropriate year and month.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

Thanks dhookom.
I may be mistaken but it it appears to my novice eye that the main goal of your suggestion is to organize transactions on a report based on the month and year of a transaction.
I may be mistaken, but I was under the impression from what I've read that the hard part of what I'm asking was getting a report where the data (individual payment dates and amounts) displays side-by-side as values in a report.

 

[dhookom]

You may need to include the [Payee] in the link master child properties. This allows [Payee] values on the left and all of the transactions by month to the right in subreports.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

I must confess I didn't know there were such things as subreports so unfortunately I'm well below your level of expertise. Let me look at them and see if I can become more familiar with what you are suggesting and after that I may come and ask for more guidance, if you don't mind. Thank you for your help.

 

[dhookom]

There are some examples of crosstab reports at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The zip should contain two mdbs. Check out the one that [red]isn't[/red] Crosstab.mdb.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

I downloaded "Cross Tab (69 KB) Access 97" in your section, but the zip only had crosstab.mdb in it.

 

[dhookom]

Sorry, I thought I had two files in that download. I might have another sample some place.

I have provided all the steps necessary. How far have you gotten with this?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

Hi dhookom,
Haha, I think part of what I've found is that reports can be more complicated than I thought they could be.
So far I have a report started (will be the monthly subreport x 12) that has a field for transaction date and a field for transaction amount (those are the only fields on it).
Following your directions I have a question. I'm sorry I have to be so basic, but I'm afraid I'm not sure what you mean by "Add two columns in your subreport's record source"
Mth: Month([Transaction Date Field])
Yr: Year([Transaction Date Field])
Do you mean I need to set up a query for the record source and get the month and year of transactions as columns in the query?

Thank you.

 

[dhookom]

Do you mean I need to set up a query for the record source and get the month and year of transactions as columns in the query?"
Yes, subreports and subforms are generally designed to be "linked" to a main report/form. You would need values/columns in your subreport to provide the link. In addition to the Mth and Yr fields, you might need to include a [Payee] column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

As I was putting in the text boxes for TransactionDate and TransactionAmount on the subreport, I couldn't think of how the payees would be coordinated with the date and amounts, so I figured I'd have to hard key the payees onto the main report. No?
Truthfully I'm pretty lost and trying to keep up with you.

 

[dhookom]

Your main report record source should be something like:
SELECT DISTINCT, Month([TransDate]) as Mth, Year([TransDate]) as Yr, [Payee]
FROM ........

You can then set the link master/child properties to Mth, Yr, Payee.

If you have trouble, come back with exactly what you have accomplished and what you have an issue with.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

Using your instructions I created this query to get the payments for the last 6 months and made it the Record Source for my main report:
SELECT Payee, amountdebit, datetransaction, Month([DateTransaction]) AS Mth, Year([DateTransaction]) AS Yr
FROM tbltransactions
WHERE DateTransaction>DateSerial(Year(Date()),Month(Date())-6,0);

After that I did everything you said from your first post above (except I only set up 3 months instead of 12), and also added [payee] to where you had originally said to put [Yr], [Mth], per your last post.

On print preview it gave this error:
The record source 'Mth: Month([DateTransaction]),Yr: Year([DateTransaction])' specified on this form or report does not exist.
To fix this I changed the Record Source for the subreports from
Mth: Month([Transaction Date Field]) Yr: Year([Transaction Date Field])
to the same query I set for the Record Source of the main report. Obviously I'm not sure if this is right although it did eliminate that error. Then on print preview it gave this error:
You must use the same number of fields when you set the LinkChildFields and LinkMasterFileds properties.
To fix this I addied [Mth] to Link Master Fields so it then looked like this:
[Yr];[Mth];[txt2]
Again, I don't know if that was the correct thing to do but it does print preview with no errors.
However the report is blank.

Additional info in case it matters:
Each subreport shows Report.rptMonthlySubreport as its Source Object

Thank you for your help.

 

[dhookom]

The main report record source should not contain any amounts or detail information.

SELECT DISTINCT Payee, Year([DateTransaction]) AS Yr
FROM tbltransactions
WHERE DateTransaction>DateSerial(Year(Date()),Month(Date())-6,0);

The subreports should have a record source that contains the Payee, Yr, and Mth fields as well as whatever you want to display in the subreport, something like:

SELECT Payee, amountdebit, datetransaction, Month([DateTransaction]) AS Mth, Year([DateTransaction]) AS Yr
FROM tbltransactions;

You then add 12 text boxes and 12 subreorts as noted in my first reply.
[blue]
Link Master: [Payee], [Yr], [txt1]
Link Master: [Payee], [Yr], [txt2]
--- etc ---
Link Master: [Payee], [Yr], [txt12]
[/blue]
Set the Link Child properties all the same:
[blue] Link Child: [Payee], [Yr], [Mth]
[/blue]



Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

It's alive! haha! Thanks for your help.
It's crude looking but the data is there except for the payees. How can I get the payees to be listed on the left like in my first post above?

 

[dhookom]

The payees should be a field in your main report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[homer70]

Yep. That did it. Thanks for all your help!

It needs heavy tweaking, but hopefully the hard part is done.

Thanks again.