Solved

crosstab query asks for parameters when i save it

Posted on 2014-02-06
7
538 Views
Last Modified: 2014-02-13
hey guys, just a quick question about a peculiar behaviour of Access. when i edit my already working cross tab query and then i press ctrl + s, it asks me for the parameters again and runs itself, but doesn't display any results.

Question: why does a cross tab query run when i save it?
0
Comment
Question by:developingprogrammer
7 Comments
 
LVL 5

Assisted Solution

by:Lawrence Barnes
Lawrence Barnes earned 125 total points
ID: 39840981
Hello,
The field names in a cross tab query are dynamic (when they create the column heading)...so it runs each time you open, save, etc. to verify/write/display the field names.
LVBarnes
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 125 total points
ID: 39841066
The best way to stop queries/reports from prompting for parameters is to use form field references rather than prompts.  As long as the form remains open, the pesky query will get the values from the form.

Don't forget that Crosstabs also require that parameters be specifically defined.  Other query types aren't so rigid.
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39841537
No points please,

To piggyback on Pats comment about declaring crosstab parameters. Even if your crosstab query uses another query, which has parameters, those parameters must be defined in the crosstab as well as in the base query, upon which the crosstab is base.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:developingprogrammer
ID: 39843589
Hi Lawrence, thanks for that! i was thinking that after i defined Column Headers it won't prompt anymore but still wasn't the case ha.

Hi Pat, whao i didn't know that the crosstab queries need to have the parameters defined. thanks! = )

Hi Dale! Dale my crosstab query is based on other sub queries and sub-sub queries etc. in those sub / sub-sub queries there are parameters and they are already defined. however in my crosstab query i don't define them again. they seem to work fine.

why would i need to define the parameters in the cross tab query again even though i'm not using them in the cross tab query itself? (not talking about the sub / sub-sub queries)

thanks in advance! = )
0
 
LVL 47

Assisted Solution

by:Dale Fye (Access MVP)
Dale Fye (Access MVP) earned 250 total points
ID: 39843866
@dp,

In all honesty, I have not tested that lately (in 2007, or 2010).  In earlier versions of Access, I remember that declaring the parameter in the sub-query was not sufficient, you had to declare it in the cross-tab query as well.  That may no longer be the case.
0
 

Accepted Solution

by:
developingprogrammer earned 0 total points
ID: 39844821
hey Dale, i managed to find this article and also i faced the problem you mentioned about the crosstab parameters

http://support.microsoft.com/kb/209778

the strange thing is that i have 2 crosstab queries,

- one i didn't define the parameters and it works fine
- one i didn't define the parameters and i doesn't work (it works when i defined 1 particular parameter. this crosstab query used 2 of its sub queries to calculate the result of the 3rd sub query (and then all the results are joined into a union then the crosstab)

my conclusion is that - if it's just a normal select parameterised query, don't need to define parameters

if it's a sibling dependent query then we may need to define the parameter - depending on what Access says it needs (from the error dialog).

the last thing is that, if i define the Column Headings in the properties pane, i don't need to define the parameters anymore.

HOWEVER i MUST define ALL the column headers that i want showing. if there is a value for Column X but i didn't put column X in the "Column Headings" property, it won't show.

here's the link to Allen Browne's article that i read to understand crosstab queries better = ) http://allenbrowne.com/ser-67.html

thanks Dale!

addition: the query that i didn't define parameters for, i also didn't define column headings and it works fine. it's just a normal select parameterised query, doesn't use 2 sub queries to calculate a 3rd sub query
0
 

Author Closing Comment

by:developingprogrammer
ID: 39855707
i joined Pat, Lawrence and Dale's answer together with Allen Browne's article and specific testing for Access 2007 to arrive at this conclusion at the moment
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

705 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

19 Experts available now in Live!

Get 1:1 Help Now