Query Dependency

[madrav72]

Hi All,

I have a report that is driven off a query which in turn is driven by something like 20 odd queries

I have been asked now to produce the same report by a categorical area

Now my understadning is that if there is 3 categories for instance then i am looking at soething like 3 X 24 queries effort to get this working

Let me know if this is common practice among access applications because i had a feeling that this is quite long winded and was hoping for a quicker way of producing these reports

thanks

 

[Pat Hartman]

You would need to add parameters to your queries. I would create a form with a combo or text box to capture the category. A button on the form would run the report. You may be able to use the where argument of the OpenReport method by modifying the code generated by the button wizard but most likely, you'll need to change the queries to get their parameter from the form. In order to make the form work for 1 or all, you need to use something like the following:

Where SomeField = Forms!yourform!SomeField or Forms!yourform!SomeField Is Null;

 

[madrav72]

I think this is a tricky issue since the one query is actually driven off 20 sub queries so to speak. in this sort of situation i presume that i would have to make all 20 of these subqueries into parameter queries since they would be impacted. Can this be done in qieries or does this have to be done using VBA??

thanks

 

[Catherine B]

It sounds like we are doing something similar- I am trying to run one query that links to a chain of about 4 or 5 queries (don't ask, it seems to be the only way I could get the data ordered and grouped correctly)

I tried creating a form as described above and also using <a href="http://www.fontstuff.com/access/acctut08.htm">this rather good tutorial</a>. But when I try running it the Jet engine has problems

Even if I try to put a parameter into the design of the final query eg [enter month] under criteria, I get the same message :
"The Microsoft Jet database engine does not recognise '[enter month]' as a valid field name or expression"

I've also tried putting the [enter month] parameter earlier in the chain of queries, it will work fine for running that particular query, but when I run the 'final' one, once again the same message.

so I'm afraid I'm a bit stuck on this one as well...
-------------------
EDIT

ok it looks like the solution is here:
"http://www.access-programmers.co.uk/forums/showthread.php?t=65401&highlight=passing+parameters+query

(I really must find out how to get html links working on this...)

 

[madrav72]

Catherine I am a bit confused did you specify the parameteres in the chained queries or in the master query?

Apologies for sounding a bit dim but I m not sure how placing the same parameter in all the chained queries would be connected to the form input.

 

[Catherine B]

Hi there. Sorry I am new to this so not great at explaining.

I specified it in the master/final query4:

the break down is this

tables (various) ---> query1 ---> query2 ---> query3 (crosstab)-->query 4(crosstab)

the form orginally had a combo box but now just a text box, into which I can put month number - I am trying to use this as a parameter. The form has a button which runs query4

Now, in the final query4 design screen, I have put in the criteria for 'month' field : [Forms]![Form_1]![txtPeriod]

running it like this didn't work because of the "jet engine" error mentioned above. What I needed to do to get it to work is define the parameter in the query. Go up to the Query menu and select Parameters .... so in the first column I put [Forms]![Form_1]![txtPeriod] and in the second column 'Value'.

after saving this is now works.

so this is just building on from what the other guy said about using parameters. I only needed to put them in the last/final query but had to make sure it was defined correctly

 

[madrav72]

is it possible to have input from one master query feed through to a bunch of seperate sub queries .....

e.g.

master query ---> sub query 1
---> sub query 2
---> sub query 3
---> sub query x etc

thanks

edit: I think the main difference between my query and yours is that i am not chaining single queries instead i have 20 sub queries underneatch the one master query hence the above question.

 

[Catherine B]

ah I see what you're saying. hmm.. not sure. I've found if I stick a parameter on (query 1) then it WILL feed through the others, because they are all linked to that first one. So if the master one is filtered by date (or whatever) then so will the others?

As I said I'm still experimenting myself - I'm finding some of the ideas on this page quite helpful
http://www.fontstuff.com/access/acctut08.htm

good luck!

 

[Pat Hartman]

Crosstabs are rather picky when it comes to parameters. Whenever your crosstab or any query subordinate to it takes parameters, the parameters MUST be specifically declared. Right-click on the gray background and choose the parameters property.

 

[madrav72]

ok ive now fed my 20 odd subqeries into my master query and ive found that although each one asks for a parameter albeit the same one the master query only asks me for it once which is absolutely great as i presumed it would ask me for the parameter about 20 times