?
Solved

MS Access: Crosstab Query and Report

Posted on 2013-01-30
5
Medium Priority
?
633 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 400 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 200 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

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.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

718 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