Cross-Tab Query Questions

[cokeblue8]

Hi

-Is is possible to make a parameterized cross-tab query? I want only to display data between certain dates and for a specific crew.

-Also, for data in the query I have the Line number (of the machine) as the row headers and the Problem names as the column headers. I also want to display the problem CATEGORIES (of the problem names) above the names. How do I do this???

 

[CraigDolphin]

Is is possible to make a parameterized cross-tab query?

Yes, make a parameterized select query that limits the data to the correct date range. Then base your crosstab query on the select query results. You may need to encapsulate your parameter references in an Eval() function if you are referencing controls on a form.

I also want to display the problem CATEGORIES (of the problem names) above the names

I don't think you can do this exactly as you want it. A crosstab allows for multiple row header columns but only allows for one column header. (I wish to heaven they'd allow for more than one myself)

The best I can offer is to add Categories as your first row heading and set Problem name as your second row header, then change Line number to your column header. If you really need to transpose the results, then export it to excel and copy and paste-special the results making sure to check the transpose option in the paste-special dialogue.

 

[cokeblue8]

Thanks for your reply!

Yes, make a parameterized select query that limits the data to the correct date range. Then base your crosstab query on the select query results. You may need to encapsulate your parameter references in an Eval() function if you are referencing controls on a form.

When I do this, a window pops up saying "The Microsoft Access Jet database Engine does not recognize '[Input crew]' as a valid field name or expression."

What am I doing wrong?

 

[CraigDolphin]

I don't know since I have no idea of anything specific to do with your database.

Check your crosstab to make sure you're not referencing a non-existant Input Crew 'field' in your select query. Also check your select query to see if you're referencing [Input Crew] somewhere where you shouldn't.

If you can't find the solution, maybe post the sql for both queries, or better yet a zipped version of the db itself (with enough data that you don;t mind sharing to illustrate the problem).

 

[Rich]

You have to specifically set the Parameters of the crosstab in the Parameters box

 

[cokeblue8]

Here is my database.

I've named the cross-tab query 'c'.

It's based on qryRewLineSumm , which is based on qryDate which is based on qryCrew. (whew)

-qryCrew has "crew" as a parameter
-qryDate has the dates as parameters
-qryRewLineSumm has no parameters


what i have in mind is to get something like rewlinesumm (also a crosstab query), but to have it display only the information based on the crew and date parameters.

 

[CraigDolphin]

Rich was correct. You needed to specifically define the parameters in your source queries qryCrew and qryDates (as attached).

 

[cokeblue8]

wow! thank you all very much!!!