473,322 Members | 1,379 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Crosstab as subreport data source

Environment: Access 2k, win2k

Business Problem: I have to display returns on investments made over a
period of years, and show the amount of return earned on each investment by
year. For example, if you placed some money in 2001, you'd want to see the
returns on that for 2002, 2003, 2004 etc. (These are long-term investments,
so it's quite reasonable to track themin this manner - I wouldn't take this
approach aon an active portfolio..)

So, this strikes me as an ideal use of a crosstab report, with the time of
investment as rows, and the return years as columns. No problem, build a
report, write some bits to check how many columns, sort the columns (thank
you, Mr. Lebans - awesome sort implementation!), bind the detail and footer
fields at runtime, make it pretty. Except that I can't use it as a
subreport, even though there is no master-child linking. I get the "cannot
use crosstab as a subquery error"

I looked through the archives, and found some good suggestions, like setting
the data source property to an SQL string on report open, or of pre-setting
the values in the columns, none of which worked.

Am I beating my head against a wall here? I would really like to make the
crosstab work, as all the other solutions I can think of require some fairly
convoluted code that makes me more than a little leery.
Nov 12 '05 #1
2 4368
Use the crosstab query to fill a temp table for the sub report.

"James Neumann" <jn******@dspd.mail.ipsoinc.net> wrote in message
news:i5*****************@newssvr32.news.prodigy.co m...
Environment: Access 2k, win2k

Business Problem: I have to display returns on investments made over a
period of years, and show the amount of return earned on each investment by year. For example, if you placed some money in 2001, you'd want to see the returns on that for 2002, 2003, 2004 etc. (These are long-term investments, so it's quite reasonable to track themin this manner - I wouldn't take this approach aon an active portfolio..)

So, this strikes me as an ideal use of a crosstab report, with the time of
investment as rows, and the return years as columns. No problem, build a
report, write some bits to check how many columns, sort the columns (thank
you, Mr. Lebans - awesome sort implementation!), bind the detail and footer fields at runtime, make it pretty. Except that I can't use it as a
subreport, even though there is no master-child linking. I get the "cannot use crosstab as a subquery error"

I looked through the archives, and found some good suggestions, like setting the data source property to an SQL string on report open, or of pre-setting the values in the columns, none of which worked.

Am I beating my head against a wall here? I would really like to make the
crosstab work, as all the other solutions I can think of require some fairly convoluted code that makes me more than a little leery.

Nov 12 '05 #2
raii,

Thank you - that did the trick. Can't believe I didn't think of that.
AAArrrrrrrggghhhhh....

I'd still like to make the crosstab work as a direct subreport source,
but that is going to be done on my own dime, not my customer's.

Please remove DAHT to get correct email. Please post any replies
directly to this newsgroup.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: dixie | last post by:
I have a report with a subreport. The source object for this subreport varies according to the value of a field in a table. I am trying to programmatically set the object source for the subreport...
1
by: Nathan Bloomfield | last post by:
Does anyone know if there is any documentation which relates to Access2k + ? or can anyone help adjust the code? I am having trouble converting the DAO references. TITLE :INF: How to...
1
by: krish_4u_only | last post by:
Hi, I have designed a subreport - record source is on crosstab query. crosstab query is designed on union query. The sub report is working fine when it is opened individually. but if i place it on...
2
by: Sherman H. | last post by:
I have a few questions for crosstab and popup form questions: 1. I created a crosstab as follows: IT Financial Operation John 21 22 ...
3
by: deejayquai | last post by:
Hi I've created a crosstab query and displayed it as a sub-report in my main report. This is fine until the data changes and the column names become incorrect. I know I have to create a...
2
by: bobh | last post by:
Hi All, I have a report which has several sub-reports on it, Each sub-report has its own record source and child/parent link. Sometimes a sub-report may not have data for an entered policy nbr and...
13
by: salad | last post by:
Operating in A97. I didn't receive much of a response conserning Pivot tables in Access. Pivot tables are nice, but a CrossTab will work for me too. Using a Pivot table, one is actually...
3
by: russellhq | last post by:
Hi, I'm fairly new to access and have a little trouble with a crosstab query I've setup. I have a main form where the user selects a project name and below in a subform, a crosstab query is...
3
by: Simon van Beek | last post by:
Dear reader, How to change the RecordSource for a subReport. For forms the syntaxes is:
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.