Solved

MS Access: Crosstab Query and Report

Posted on 2013-01-30
5
627 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 (Access MVP)
Dale Fye (Access MVP) 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

751 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