problems in generating a report based on crosstab query with drop down list form

[lonely]

Hi there,

I am working on a financial report access database and try to automate the reporting procedure. I have some problems and hope you can help.
So I have 4 tables: Data, Cost Center Structure, Cost Element Structure and Period.

In Table Data, I have CostCenter, CostElement, Data, Month, Year, Type. They are from SAP accounting system, they can have data of 2016 actual expense and budget for every month; data in 2015. The table looks like:


In Table Cost Center Structure, we have the name of the costcenter, the group for different cost centers, and they have different level of grouping.




In Table Cost Element Structure, we have cost element grouped. Sometimes they only need to be sum up as revenue or expenses, sometimes they are in more detailed group.

In Table Period, we have Apr as the 1^st month of our fiscal year, and it helps to sorting.

What I want, is a form with two combo boxes, so the user would choose which cost center, and which month. And generate a report of this cost center on chosen month, like:




I used a query, say query 1, to connect financial data with element, and sum up the data. Another crosstab query on query 1 to show data for 2016 Act and 2016 Bud. I have also built a form with two combo boxes, let the user chose month and cost center.

But when I run the query, it wont work. It says the query is bigger than 2G or do not recognize the Criteria I embedded in the query to connect with combo box.

I want to know if my methodology is correct. Is there any more direct way to do that?

I am new to Access, and I really appreciate your help!

Thanks,

Luna

[pbaldy]

For starters:

Crosstab Query with a Parameter

[June7]

Paul, that link doesn't open a page related to crosstab, it's a MS Support search page.

Here is another reference about crosstabs http://allenbrowne.com/ser-67.html

[lonely]

Thank you guys.

The thing is, my crosstab query works fine. When I add a combo box, say select cost center, it wont work.

like what showed on June7's link, mine return a message box says: can not find "[Forms].[Form1].[StartDate]"

Handle parameters

A query can ask you to supply a value at runtime. It pops up a parameter dialog if you enter something like this:
[What order date]
Or, it can read a value from a control on a form:
[Forms].[Form1].[StartDate]
But, parameters do not work with crosstab queries, unless you:
a) Declare the parameter, or
b) Specify the column headings.
To declare the parameter, choose Parameters on the Query menu. Access opens a dialog. Enter the name and specify the data type. For the examples above, use the Query Parameters dialog like this:

Parameter	Data Type
[What order date]	Date/Time
[Forms].[Form1].[StartDate]	Date/Time
[ OK ]	[ Cancel ]

Declaring your parameters is always a good idea (except for an Access bug in handling parameters of type Text), but it is not essential if you specify your column headings.

[June7]

Did you follow the article instructions? Did you declare parameter?

Post your attempts for analysis.

[lonely]

Hi June7,

I followed the instruction and fixed most of the problems. However, I failed to connect the parameter with combo box, it pops up message box, and if I manually input data, it works, but I failed to apply the Filter from a form.

Any thought on that?

Thanks,

Luna

[June7]

I've never needed CROSSTAB queries. So no idea.

If you want to provide db for analysis, follow instructions at bottom of my post.

[lonely]

Hi June7,

After working on that for several days, I find a solution. Actually it is a dumb one but it works. So I have use a query, say qry1, to get the data, and a crosstab query based on qry1, so, a combo box was built based on qry1, and it works!

I have just learned how to give you "stars" and already add reputation to your name, I am very appreciative!

Thanks,

Luna