Solved

MS Access: Crosstab Query and Report

Posted on 2013-01-30
5
604 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
  • 3
5 Comments
 
LVL 77

Accepted Solution

by:
peter57r earned 100 total points
Comment Utility
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
Comment Utility
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 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 50 total points
Comment Utility
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
Comment Utility
thanks fyed!
0
 

Author Closing Comment

by:staceymoore
Comment Utility
thanks to both of you for your assistance
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now