Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

crosstab query asks for parameters when i save it

Posted on 2014-02-06
7
Medium Priority
?
595 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 500 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 40

Assisted Solution

by:PatHartman
PatHartman earned 500 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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 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
Independent Software Vendors: 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!

 

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 49

Assisted Solution

by:Dale Fye
Dale Fye earned 1000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

877 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