Okay, been struggling with this for a while. To preface, I am not a VBA developer, and I don't want anyone writing the code for me. But need some help. Any given is very appreciated.
I have a situation where I have a range of data that I need to be displayed, but the number of columns can change.
For instance, the Query(table) will look like this.
Company# Report_year Value
1 2000 2500
1 2001 1976
1 2002 2010
2 2000 800
2 2001 856
2 2002 857
And so on. But, the number of years in Report year can vary. So I can't do a static Join but need to figure out a way to display the data so that it looks like this:
Company# Year 2000 Year 2001 Year 2002
1 2500 1976 2010
2 800 856 857
Now, I can get it to display this way with a Cross Tab Query. But, that isn't usable for the End Users in this case.
Now I was pointed to
http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset
And that seems to be very similiar to what I need. And I was able to get it to work, a little bit. But it was giving too much data, and not displaying under the headers. (so for 10 years, I was getting 15 pieces of data. Not sure why).
I am lost as to where to go next. And again, I do not want anyone just writing something up.
I scrapped what I was using, or I would post that.
Any nudge in the right direction (like what I should be trying to use, etc) would be very much appreciated.
(the problem with the pivot table, is they want to be able to export this to Excel as well, and when i do that, needs to do aggregate function which messes with the data.)
I have a situation where I have a range of data that I need to be displayed, but the number of columns can change.
For instance, the Query(table) will look like this.
Company# Report_year Value
1 2000 2500
1 2001 1976
1 2002 2010
2 2000 800
2 2001 856
2 2002 857
And so on. But, the number of years in Report year can vary. So I can't do a static Join but need to figure out a way to display the data so that it looks like this:
Company# Year 2000 Year 2001 Year 2002
1 2500 1976 2010
2 800 856 857
Now, I can get it to display this way with a Cross Tab Query. But, that isn't usable for the End Users in this case.
Now I was pointed to
http://www.access-programmers.co.uk/forums/showthread.php?t=106426&highlight=wayne+recordset
And that seems to be very similiar to what I need. And I was able to get it to work, a little bit. But it was giving too much data, and not displaying under the headers. (so for 10 years, I was getting 15 pieces of data. Not sure why).
I am lost as to where to go next. And again, I do not want anyone just writing something up.
I scrapped what I was using, or I would post that.
Any nudge in the right direction (like what I should be trying to use, etc) would be very much appreciated.
(the problem with the pivot table, is they want to be able to export this to Excel as well, and when i do that, needs to do aggregate function which messes with the data.)
Last edited:
