Crosstab Parameters (2000(SR3))

Topic

New Reply

I am now working on a variation of the crosstab report I was working on last week which presents a new challenge.

In this report, I have a set of 4 crosstabs all mounted as subreports on an unbound main report. Each of these is presenting a set of statistics for monthly reporting (one is male/Female client count, one is a count of new clients and their service areas, etc.) This is essentially the same report as before with the exception that it is now broken down by staff member (the other was totals for the whole department). I want each member to print on a separate sheet. I cn get it to work if I enter parameters by hand, but not if I try to feed them from a form.

I can get the crosstabs to accept parameters from a form where I select one worker at a time and then print the report but if I try to do any type of multiple select (either multiselect listbox or with MultiPik from Access Developer’s Handbook), the crosstabs won’t pick up the parameters.

I have tried a number of ways of compiling the syntax and feeding it to an unbound (hidden textbox on the Multipik) and none (so far) seem to work. If I type a parameter selection directly into the queries (by way or testing), e.g Like “WorkerA” and “WorkerB” then I can get my report to print properly. Yet, when I get my compiled list to put the identical data into the unbound textbox and have the parameter: Like [Forms]![frmMultiPik]![txtList], the query returns no data.

Hopefully, this is clear.

Reply | Quote

Replies

See post 470,621 – both the attachment and the link provided by MarkD.

Reply | Quote

I understand how to apply Mark’s Demo to a Select Query but I can’t figure out how to make it work with a crosstab. When I enter the InParamEx function into a Field name with the following syntax: InParamEx([tblISAPUSers]![UserID],GetControlVal(“frm_Demo”,”SelectedItems_txt”)) I get a parameter prompt asking for [tblISAPUSers]![UserID].

I have tried adding that field in as a text parameter in the query parameters since I already learned my lesson with crosstab parameters. This was also to no avail.

Do I list the function as an Expression, or Where statement? I tried fiddling around with the field argument and just using [UserID] as this crosstab is based on a query and UserID is the field name I want to filter on and none of that made a difference. So I’m a bit lost here.

Would this perhaps be more easily achieved with a querydef? If so how complicated is it to adapt the SQL from the crosstab to SQL the querydef would understand?

Reply | Quote

I don’t think I can answer this without seeing what you’re working with. Could you post a stripped down copy of your database? See post 401925 for instructions.

Reply | Quote

I seem to have solved the problem through no fault of my own. In preparing a stripped down database for you to look at, I actually got the thing to work. I suspect I achieved some clarity of the thinking while putting this stuff into a new database without the zillions of other forms, etc confusing my view of the world.

That said, I still have concerns about the speed at which these many crosstabs will calculate since there are thousands of records (right now about 10,000) and that will increase by about another 5,000 or so for a total of about 15,000 per fiscal year. So, back to my other question. Would feeding the parameters via a querydef be significantly faster than using Mark’s function?

Reply | Quote

I don’t understand what you mean by “feeding the parameters via a querydef”.

At least one of your crosstab queries has fixed column headings (Male/Female) as far as I can see. You can speed up crosstab queries considerably by specifying the column headings in design view, if possible. Click in an empty part of the upper half of the query design window, and activate the Properties window. Column Headings is one of the query properties.

Reply | Quote

All of my crosstabs are using column headings, advice you gave me in my post last week. This particular new report takes the whole process one step further in that the data is now sorted by User. The previous was just totals for the whole department.

There are four crosstabs as subreports: Male/Female; New Clients; Returning Clients; and Transfers from another jurisdiction. I have placed all of these on a main report that has only one field: UserID. UserID is used to link Child/Parent fields with the subreports. One user per page.

What I am finding is that each of the subreports calculates for each page. I have the multiselect listbox working properly now, but even with only 3 or 4 workers selected, the report generation takes quite a while.

So, the question, which I didn’t prhase well, is would it be quicker (in terms of processor time) to create the data for these reports in VBA? If so, is it worth looking at?

At this stage in the game, it would be more of a vanity/learning issue since the things works and the network is so slow at this agency they won’t even notice the time issue. It just seems that with 5 queries per report (a preparatory select query to filter unwanted records and get unique values + the four crosstabs), it might be more efficient another way.

Reply | Quote

In some situations, it is efficient to create an intermediary table instead of piling queries on queries, but it’s hard to judge without seeing the exact setup.

Reply | Quote

I’ll post later today the stripped down database I was working on yesterday when I solved the multi-select problem. Ironically, I turned my original select query into a table to save space since it drew on 4 or 5 tables. perhaps this is what I want to do: create a table first then have the crosstabs draw on that table.

Reply | Quote