Parameters as Column Headings

[lstantliff]

I am using Access 2000. I have created a crosstab query to run monthly reports based on a fiscal calendar year. My problem is that my column heading is a parameter [Fiscal Period] that returns the fiscal week #s as column headings and they change for each fiscal period. I cannot find a way to list these in the properties box as column headings and therefore they will not display as field names and I cannot create forms or reports from them. Can anyone help?

 

[pbaldy]

Here's one method:

http://support.microsoft.com/kb/328320/en-us

 

[lstantliff]

Paul thanks for the info, I should have provided the info that I am using Access 2000. Anymore ideas would be appreciated. I have been using Access for a while, but nothing this complicated. Thanks again!

 

[namliam]

Go into the SQL and add as the 'last line'
In(1,2,3,....,50,51,52,53)
Then they should list....

Your SQL should look something like
TRANSFORM .........
SELECT ......
FROM ....
GROUP BY ......
PIVOT .....;

and before the ; enter the in, ending up with....
TRANSFORM .........
SELECT ......
FROM ....
GROUP BY ......
PIVOT .....
In(1,2,3,....,50,51,52,53) ;

 

[pbaldy]

I don't think there's anything in that process unique to Access 2002, so it should work in A2k as well. I use Access 2000 and use something similar (and simpler I think) from the Access Developer's Handbook for dynamic crosstab reports. It looks more intimidating than it is, if you just follow it through step by step.

 

[lstantliff]

Please Help - No Column Fields to Create a Report!!

Let me start over. My overall goal is to create monthly, quarterly and annual reports. I have a Fiscal Calendar Table and a Production Table set up and there are approximately 20 crosstab queries pulling info that make up the reports. These twenty queries are then grouped together in 3 union queries and then these 3 are grouped together again into one union query. I used the statement SELECT * to pick up my columns in the union queries. When I try to create a report based on the crosstab or union queries, it will not allow me to do so. My column heading is [Fiscal Period],[Fiscal Quarter],[Fiscal Year] which are also parameters and return the week, period or quarter as column headings that change for each report. When I try to create a report, it will not give me a box to choose fields from, instead it wants to run the query. The database was originally set up to only run weekly reports by a consultant who is no longer here and I have just taken his work and modified it to reflect the new reports. I fall short on writing code or understanding all that is involved. I only know the basics of Access. I did pick up enough to write a few expressions and the union queries but that's about it. There should be a way to do this but my experience is limited. Any suggestions would be appreciated!!

 

[pbaldy]

If you're saying that today when you run the report, the fields might be "January", "February" and "March", but later when you run it they'll be "April", "May" and "June" or "Monday", "Tuesday" and "Wednesday", then I've already pointed you to one solution.

If not, then I guess I'm dense today and don't understand the issue. Maybe you can upload a picture of what you have going on.

 

[lstantliff]

Paul - I will look at the link again. The first time it went right over my head. I will put more thought into it. I do appreciate your help.

 

[namliam]

Before you can create the report access must run the Query to gather the information needed to create the report.

So if that is your problem, just run the query, as access wants and then start creating your report....

Atleast this is what I get from your story....

 

[pbaldy]

Mailman,

I read this as one of those situations with a crosstab query that pivots on a date field, so if I run the report for April I have field names of "April 1", "April 2", etc. If I run it for May my field names become "May 1", "May 2"... Therefore you can't design the report with controls bound to a specific field, because that field name can change when you run the report. Thus the code to dynamically set control sources, etc.

I have to say, the more I look at the M$ code, the more I like the method from the ADH better.

 

[lstantliff]

Your right Paul, if I run the report for Period 3, then it displays the column headings as Week 10, Week 11, Week 12, Week 13. What was the ADH's solution for this?

 

[pbaldy]

I don't like posting code solutions from books for fear of violating copyright laws. If you have access to the book (perhaps a library if you can't buy it), the code and explanation/instructions are very simple and clear. In the A2k version, it begins on page 727.