[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Crosstab query problem

Posted on 2008-10-14
4
Medium Priority
?
230 Views
Last Modified: 2013-11-29
I get an error when I try to open a report which is based on a crosstab query.  The error is due a missing field the crosstab after it is run on a certian set of data.  The report has fixed fields which display the count of records which contain a certain value in a table.  Is there some way that I can make the crosstab query display certain value feilds even if the table/query that the data comes from is not present.  Also I have tried IIF statements in the report to ignore the missing feild and display 0 but this did not work.  

Any ideas?  

The report works fine if the feild is present in the crosstab query.

0
Comment
Question by:randyone
  • 2
4 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 22718343
You can set fixed field names in the Columns property of the query - you must match exactly the spelling in the data.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22719752
randyone,


Try Pete's suggestion first.


Some common things you can try, depednding on how your data is structured.

- Do not use fixed Fields in the report, simply insert the crosstab query as a sub report.
- Create a table with all of the fields and use a Join to display all the fields (even if they are missing)

JeffCoachman
0
 

Accepted Solution

by:
randyone earned 0 total points
ID: 22725353
Thanks guys for the suggestions, but I after digging that that I can add a IN option after the PIVOT statement in SQL which fixes the feilds.  I ended up with something like this:

PIVOT queNetRecordsAll.block_code In(0,4,5);

Worked perfectly!

Thanks again,

Randy
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 22727075
Congratulations!
;-)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

612 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question