MS Access: Crosstab Query and Report

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
staceymooreAsked:
Who is Participating?
 
peter57rCommented:
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
 
staceymooreAuthor Commented:
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
 
Dale FyeCommented:
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
 
staceymooreAuthor Commented:
thanks fyed!
0
 
staceymooreAuthor Commented:
thanks to both of you for your assistance
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.