• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 508
  • Last Modified:

Building a Report off of a Crosstab Query

Hi, I have searched long and hard to see if there is any way possible to create a report that uses a Crosstab query as its record source without having to manually insert or edit the Column Headings property (ya know, to fill it in to say what it needs to say for all of the labels).

I just have a report I would like to create that could dynamically report on the month's being reported. For example, if the query parameter for From and Through Dates is for 1 year's worth of time, the labels should say Jan '11, Feb '11, etc., etc..... You get it?

I am not too good at VBA but I am open to using examples.

Let me know alright,
Thank you
0
IEHP1
Asked:
IEHP1
  • 25
  • 16
  • 4
  • +1
1 Solution
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>if there is any way possible to create a report that uses a Crosstab query as its record source without having to manually insert or edit the Column Headings property
I declare dibs on 'No' as the answer.

>For example, if the query parameter for From and Through Dates is for 1 year's worth of time, the labels should say Jan '11, Feb '11, etc., etc..... You get it?
Will there always be 12 months / colums?  If so, it would be vastly easier to have a table that contains 12 columns, say month_0, month_1, month_2, etc., and delete * then populate it with the 12 months from the query, using parametes for the months.

Then the report will always have 12 months, and the only extra thing you'll need to to is create VBA code to populate the column labels, so month_0 shows 'Jan 12', month_1 shows 'Feb 12', etc.

Good luck.  
Jim
0
 
IEHP1Author Commented:
Thank you Jim, ok I will create a table with Date/Time columns called Month_1, Month_2, etc. now.

Actually, the number of months could be less, I don't expect anything over 12 months (but possible). If the answer is no, you have to go with 12 months the way you are saying or nothing, then I will set it up that way.

It sounds like you are leading me on to VBA code that will delete the table first and then populate it with the 12 months from the query. FYI, I don't know VBA (only little things about it). Would you be able to provide the code\syntax please?
0
 
hnasrCommented:
"...without having to manually insert or edit the Column ..."
Yes
" ...report on the month's being reported.  ..."
Yes

Upload a sample copy of your database, to save me time reproducing.
Include one possible sample data, and expected output.
0
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
IEHP1Author Commented:
OK I will try to make time to create a sample DB so that it's easier to conceptualize. I don't know when I can do that just yet, but I will when I get a chance. Thank you in advance for your help.
0
 
Jeffrey CoachmanCommented:
You can investigate the links in the mean time.

Again, the very nature of a Crosstab query makes tit difficult to create reports form it.
(A report "HardCodes" all the fields.)

So when you ask:
"is any way possible to create a report that uses a Crosstab query as its record source without having to manually insert or edit the Column Headings property (ya know, to fill it in to say what it needs to say for all of the labels)."
...The immediate answer is No. (Jim's first post)

That being said, there are techniques to do this,(albeit, not "Manually"), but using automation.
So the fields still need to be created on the fly, only the code does it.

;-)

JeffCoachman
0
 
IEHP1Author Commented:
Thank you Jeff Coachman,

I was thinking along the same lines as you, but I don't know the VBA code nearly well enough to "program" the automation process. I will look at the links when I get a chance (yes, busy guy) and will cooperate with you guys to get this automation process functional (and learn some VBA maybe, cool).
0
 
Jeffrey CoachmanCommented:
Remember that hnasr requested a sample database.

So he may very well be willing to provide an exact solution...
0
 
hnasrCommented:
See this sample database, with two colum headings.
Run the report, it is based on a crosstab query producing 3 columns. The controls are unbound and filled dynamically.

You can improve it by adding more columns. At form load, number of fields in recordset can be checked and extra controls visibility are set to No.

More advanced vba is to add controls as required.

Enjoy your VBA adventure and good luck!
crossTab-columns-inhead.accdb
0
 
IEHP1Author Commented:
I put together the same concept so that you guys can see what I am trying to do.

If you'll notice in the first query, I have manually inserted the Column Headings in the query properties. This is what I am trying to avoid and trying to have it create a dynamic report.

Thank you for all of your help.
Crosstab-Reports-Assistance.accdb
0
 
Jeffrey CoachmanCommented:
did you investigate hnasr's sample?
0
 
hnasrCommented:
Did you try my comment and the attached database?

My comment, http:#38297294, was to my understanding.
0
 
IEHP1Author Commented:
Yes, I am not an expert so I really don't understand what's going on in the report? Why do "d" and "u" labels show up as the Project Year? And unbound text boxes show up as Project3, 2, and 1? What is going on with that? I am confused.....?
0
 
hnasrCommented:
"Yes, I am not an expert"
After sometime, I am sure, you will be an expert. Remember this, but just wait and do a little homework!

"Why do "d" and "u" labels"
Sometimes you need to print column headings in two rows. d means down, and u means up. This way I'll understand which controls I am referring to. The contents can be anything different from the example given, including Jan '11, Feb '11.

If still confused, help us in  listing few records and draw an image of the expected report with these few records.
0
 
IEHP1Author Commented:
Yes, I checked out the links.
http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm is cool. I will go ahead and try that one.
The one comment that I don't understand is using the same method to create dynamic forms (in my view, I see the form being used for just selecting what parameters you want from the crosstab query?)

I checked out the other link and followed the instructions to the "T", but "no dice"(it gave me an error when I clicked Tools -- References -- Microsoft DAO 3.6 ~~~~~ checkbox saying something about it conflicting with another something). Then when I clicked the button on the form, it gave me a VBA run time error saying it couldn't find the field [Beginning Date]. I believe this is because Microsoft switched to ADO instead of DAO since MS Access 2007 (I was using MS Access 2007).
0
 
hnasrCommented:
If still unresolved, help us in  listing few records and draw an image of the expected report with these few records.
0
 
IEHP1Author Commented:
So I have attached the images so that you can see what I need to accomplish with your help. As you can see from the Report's Record Source Query Design View (this query is just one of many crosstabs needed to be dynamically reported on), the parameter I enter in upon running the query is a date range (my example shows 6/1/2012 through 8/1/2012 for brevity purposes).

I tried to set it up the way that http://www.blueclaw-db.com/report_dynamic_crosstab_field.htm instructed me to set it up, but only get blank text boxes when running the report?

I changed the query name and row heading field name in the VBA OnFormat Event Procedure (don't think I would need to change anything else).

I really need this to work. I didn't know whether or not I actually needed to create that Union query as a Step 1 and then building my crosstab query (Report's Record Source) off of that Step 1 Union query. What I need to show already shows from my Crosstab query. Simply put, I just need the report to match the Crosstab query results (dynamically, of course for the date range changes all the time).

Please I need your assistance (I don't know the VBA).
Thank you in advance.
0
 
IEHP1Author Commented:
Okay, sorry forgot to attach the image files.

I see that they are all named the same but the files aren't really named the same on my desktop? Not sure how this website handles attachment names (don't think it matters, though).
Dynamic-XTab-Report-Design-View-.PNG
Dynamic-XTab-Report-Design-View.PNG
Dynamic-XTab-Report-Record-Sourc.PNG
Dynamic-XTab-Report-Record-Sourc.PNG
Dynamic-XTab-Report-Report-Heade.PNG
0
 
hnasrCommented:
IEHP1,

Please look at the sample database I uploaded in previous comment.
Check the table and the report output.

Add a new table to reflect your data.
Show the respective output.
0
 
IEHP1Author Commented:
hnasr, I looked at that database awhile back and revisited it just now.

I am trying to create some dummy data manually, but for my membid field, I can't input the same membid twice even though it is a text field. I get the message "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."

What can I do to show you the data I am trying to show you? My screenshots didn't help?
0
 
IEHP1Author Commented:
Oh nevermind that hnasr, I just created a Composite Key
0
 
IEHP1Author Commented:
So I put together something real quick that maybe you can see what I am trying to get at?
crossTab-columns-inhead.accdb
0
 
IEHP1Author Commented:
Sorry for the error you will get if you run my query, change the active_member.active_month to Date/Time field. The query will work

Problem is when I try to run that report you created. It says "Microsoft Access engine doesn't recognize " as a valid field name or expression, blah, blah, blah....."

I think it has to do with the VBA Event Procedure you put in there??

Let me know where we are going with this please...
0
 
IEHP1Author Commented:
Hello,

Last post I received was on 10-02-12 from hnasr. I replied the same day and haven't heard anything back yet? Can someone please help me with this or hnasr, can you give me a reply back?

Thanks
0
 
hnasrCommented:
Sorry IEHP1,

Please check my sample database.
Use my table and enter some data.
If you need extra field, just edit the table and enter sample data.
Draw the expected output.
0
 
IEHP1Author Commented:
hnasr,

I have checked out your sample database, the report doesn't populate any values even though your query does. Not sure why, but thinking it has something to do with the OnLoad VBA Event Procedure?

I really want to learn VBA here, not just input my data to see if it will show up in your report (besides, your report shows the Row Heading and Column headings, but no values??

I tried putting in the name of my query and column name and got an error. Can you explain a little what the VBA code is doing please. I am attaching what I see so you can see it from my shoes. Sorry if you feel like you gotta hold my hand, but I guess that's what it takes to learn sometimes (I tried myself to get it to work with my query and tables I put in there, please believe me).

What's going on hnasr/teacher?
crossTab-columns-inhead.accdb
0
 
hnasrCommented:
Open report in Print Preview mode.

Why I ask about adding to my table a and expected output, so I can move from what I understand to what you need.

I am happy to go with you until you until you are satisfied. But please modify table a in my sample database and add any column to be included in the report, then draw the anticipated output. Once we get an acceptable result, modifying to your data will be simpler.
0
 
hnasrCommented:
Ok! it took longer than expected .

Try this sample database.

Column heads and fields are set to visible=no
We make visible the ones that are used in the year.
Modify the criteria in the query to ask for date start and date end.
EE38456191-crossTab-dybamic.accdb
0
 
IEHP1Author Commented:
I saw that the numbers were matching the query this time for the crosstabbed months, but I receive a bunch of errors (I am attaching so you can see).

The VBA code couldn't find my query because it had 's (apostrophe  s as in Luke's Query~~~~ so I took out the apostrophe s).

We are close (I am excited to have this working without errors if you can fix the errors and explain what was wrong, that would be awesome!!)

Thank you so much hnasr!!
crossTab-columns-in-head-Error--.PNG
crossTab-columns-in-head-Error--.PNG
Luke-Query-Example-Error--1.PNG
Luke-Query-Example-Error--2.PNG
Luke-Query-Example-Error--3.PNG
0
 
hnasrCommented:
I assume, the sample is working, then you added more info.

When you get the Error too few parameters, click debug and capture the screen.

Attach the new database.
0
 
IEHP1Author Commented:
Here you go, not sure why it is having a problem with CurrentDb.OpenRecordset  ???
VBA-error-Debug.PNG
0
 
hnasrCommented:
Is this the same sample database I uploaded without modification?
If not, upload the new version.
0
 
IEHP1Author Commented:
Here you go, it says Backup because it created a Backup after I couldn't open the Event Procedure in the Report.
EE38456191-crossTab-dybamic-Back.accdb
0
 
hnasrCommented:
Try this new approach, to deal with criteria in crosstab query.

Run Form1, enter dates and click button to run report.
Good luck!
Q-27827469.html-a38478872.accdb
0
 
IEHP1Author Commented:
Thank you very much hnasr. Got a quick moment to glance at it and looked good, will follow up with you.
0
 
IEHP1Author Commented:
So it looks like I would have to build a temporary table for every crosstab report I need, right?
0
 
IEHP1Author Commented:
I am attaching a couple of screen captures that happen only if I enter a 12 or more month date range on Form1.

My question is where is the expression in the error coming from? What can I do to extend it so I can have the option of running the report for a longer date range?

Thank you again hnasr!! You are the man!!
12-Months-or-More-VBA-error.PNG
VBA-Run-time-error-2465--txtCol1.PNG
0
 
hnasrCommented:
You need to specify the maximum number of months to include in one report, since you are limited with report width.

If you need to add one or two extra fields, edit the report.
Copy last column including label and text field and paste. Change names and caption and text as required and shown in the copied controls.
Example. lblCol13, txtCol13 and so on. And all should works without any change in code.

The txtCol12 was a typing error in the name it was 12 modified to txtCol12.
Add lblCol13 and txtCol13.

You may add a warning if number of columns exceeds a preset number.

Try this sample database.
Q-27827469-crosstab.accdb
0
 
IEHP1Author Commented:
I totally follow you on the adding more columns\labels and unbound text boxes and have done so with the name and caption property fields for the labels and the name property for the unbound text boxes. All works fine there.

"You may add a warning if number of columns exceeds a preset number"

I don't exactly know how to go about setting that up in VBA or what you are referring to here?

I am pleased with the functionality of this database and we are definitely going to deploy this methodology with my manager's approval.

I am interested in what you are referring to above where I put the question mark because it will probably help me learn VBA a little better (I can kind of see what's going on in your VBA code and I will try to understand it better by looking into the libraries of commands inside of VBA or whatever it is called).
0
 
hnasrCommented:
Here you are!
Added check for 13 columns. It is in the button click event.
Q-27827469-crosstab.accdb
0
 
IEHP1Author Commented:
Thank you hnasr! I can't tell you how much I appreciate this!

I think I need to buy a VBA book and get "crackin" so I can follow your legacy of helping others in need, too. Are there any you recommend\1 you highly recommend?

So I have another open question if you want to take a look at it, too.
It is called Automate Parameterized Reports.

hnasr is a true genius!!! I highly recommend hnasr to anyone!! hnasr was patient and kind to me. I supplied a little sample DB and he worked magic!! I can't tell anyone how long I had been trying to find out a way to make a report off a crosstab query-_-
0
 
IEHP1Author Commented:
hnasr is a true genius!!! I highly recommend hnasr to anyone!! hnasr was patient and kind to me. I supplied a little sample DB and he worked magic!! I can't tell anyone how long I had been trying to find out a way to make a report off a crosstab query-_-
0
 
IEHP1Author Commented:
hnasr, please take a look at the Automate Parameterized Reports open question I have pending. No disrespect to any one of the experts, but I don't know if Jeff is able to figure that one out? I haven't received a response from him in quite a while. It just seems like maybe you guys can double-team it since I now know that your VBA skills are quite good....

Let me know please.
0
 
hnasrCommented:
IEHP1,

You are welcome and thanks for your appreciated comments.
I am glad my contribution was of good help to you.

For a good book to learn VBA, that is relative. Visit the library and check what is available. Look for something like you don't understand, or something that you know well. Read through and check if it does make sense to you, even if not fully understood.

The help system, as well, offers a good source of helpful information.
0
 
IEHP1Author Commented:
So are you willing to look at the other open ongoing question I have posted called Automate Parameterized Reports?
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 25
  • 16
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now