Solved

MS Access: Crosstab Query and Report

Posted on 2013-01-30
5
629 Views
Last Modified: 2013-02-09
Hello

I have a crosstab query in MS Access that is used to display a list of the company's assets that we have on hand along with their respective age groups.  Please see below:

Country (Row Heading)
Location (Row Heading)
Asset Number (Row Heading)
Age Group (Column Heading)
Capacity (Value)

Now I have added [Country Name:] as a criteria under the Country column (in the query) and also added it to the Parameters window with a Text datatype.

It works well!

However, now that I am designing the report, I am running into problems.

I added the name of the query (qryAssetList) to the recordsource of the report but when I click on Add Existing Fields, I get the following message:

"No fields available to be added to the current view.

The current recordsource may be invalid (for example, it may contain an invalid join expression), or there was some other problem gathering recordsource information for this object)"

Does anyone have any idea what this means and how it can be fixed?

Stacey
0
Comment
Question by:staceymoore
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 100 total points
ID: 38835314
You cannot design a report based on a crosstab, unless you have a known and  fixed set of columns in the crosstab.

 You can specify a fixed set of columns in the query properties, and that is often used for calendar reports (jan-Dec), for example.

But if you have variable columns then you cannot design a standard report based on a crosstab query.

In your case it looks like you might have a fixed set of Age groups and so you should specifiy these in the columns property of the query.  You must use EXACTLY the same spelling that occurs in the data.

Once you have done that you should be able to base your report on the crosstab.
0
 

Author Comment

by:staceymoore
ID: 38851079
hi peter57r

Thanks!  Your solution works perfectly.

One more question:

In some instances, I want the crosstab query/report to return all records if I leave the input field blank.

I tried using "[Forms]![popupTankCapacity]![cmbCountryName] Or Is Null" in the query but, when I test it, the query returns 0 records.  

Do I need to add the "Or Is Null" to the Parameters window as well?

I have increased the points to 150.

Stacey
0
 
LVL 48

Assisted Solution

by:Dale Fye
Dale Fye earned 50 total points
ID: 38851123
For the later issue, where your input field is blank, add a line to your WHERE clause:

WHERE [Country] =  [Country Name:]
OR [Country Name:] & "" = ""
0
 

Assisted Solution

by:staceymoore
staceymoore earned 0 total points
ID: 38851151
thanks fyed!
0
 

Author Closing Comment

by:staceymoore
ID: 38870796
thanks to both of you for your assistance
0

Featured Post

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

632 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