Solved

crosstab query asks for parameters when i save it

Posted on 2014-02-06
7
542 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

863 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

21 Experts available now in Live!

Get 1:1 Help Now