How to pass parameter between parameter queries

How to pass parameter between parameter queries

Post by Carl Colij » Fri, 18 Aug 2006 04:05:13


Hi all,

In my Access 2003 DB I have a query (named, say, 'Base') that accepts a
parameter (named 'ParamBase'). Now I want to use this query as the source
for another query (named 'Derived'), which also has a parameter (named
'ParamDerived'). I want to use the value supplied in 'ParamDerived' as the
value for the parameter 'ParamBase' in the 'Base' query.

I know you can store the value of the parameter to pass to the 'Base' query
somewhere else in e.g. a field in an unbound (possibly hidden) form, or
supply it via a VBA function that knows what's going on. But I want to use
pure SQL to keep the database somewhat manageable :)

I already looked at the SQL keywords EXECUTE and PROCEDURE, but they didn't
really lead me to a working solution...

So, what I want to derive at is something like this:
Query 'Base':
PARAMETER nParamBase Long;
SELECT * FROM Table_Base WHERE Column=nParamBase;
Query 'Derived':
PARAMETER nParamDerived Long;
SELECT * FROM Base(nParamDerived) <== invalid syntax, I know...

The real-world example is a bit more complicated; the 'Derived' query
performs a SELECT on a table based on criteria of it's own parameters, and
the result from this needs to be used as a parameter to the 'Base' query. I
don't want to use VBA and/or forms because, in my opinion, it tends to get
messy quite easy and I like to separate the SQL from the actual client side
VBA code and forms as well.

Do I have any alternatives to VBA and/or forms?

Kind regards,
Carl Colijn
 
 
 

How to pass parameter between parameter queries

Post by Jeff Boyc » Fri, 18 Aug 2006 06:40:17

Carl

Are you saying you want the same value for the parameter in both queries?

If so, why are you repeating it in the second query, when the first query
already has it?

If you base your second query on the first query, and the first query has
the parameter, when you run the second one, it runs the first one and
prompts for the parameter.

What am I missing?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 
 
 

How to pass parameter between parameter queries

Post by Carl Colij » Fri, 18 Aug 2006 18:41:03

eff Boyce wrote:

Hi Jeff,

Thanks for the reply! The effect that the parameters of the 'Base' query
become parameters of the 'Derived' query as well was something I didn't
realize until now; thanks for pointing that out! But then I guess I have
chosen the wrong (simplified) example.

What I want to do is having the Derived query look up some value in a
certain table based on a parameter to this Derived query (one record will be
returned). The resulting value from this lookup will then be fed into the
Base query as it's parameter. The Derived query thus does need an
externally specifyable parameter, but it's not the parameter the Base query
needs. I therefore do not want the Base query to ask for the value of the
parameter, since it needs to be retrieved from a different table by the
Derived query. In other circumstances (other Derived queries, say Derived2)
the Base parameter can be found without even needing an externally
specifyable parameter (the Derived2 query can in this case find the value on
it's own). These latter queries will be used to base reports on, so I am
hesitant to use unbound forms and/or VBA to glue the queries together, since
that will mean a more complicated design.

A more detailed example of what I had in mind is:

Table Conf_History:
Period: date
SequenceNr: Long
Table Conf_Personnel:
Name: text
Period: date
Query Get_Personnel:
PARAMETER dPeriod DateTime;
SELECT Name
FROM Conf_Personnel
WHERE Period = dPeriod;
Query Get_CurrentPersonnel (incorrect syntax, but for illustrative purposes
only):
SELECT *
FROM Get_Personnel (
SELECT Period
FROM Conf_History
WHERE SequenceNr IS NULL
);
Query Get_HistoricalPersonnel (incorrect syntax, but for illustrative
purposes only):
PARAMETER nSequenceNr Long;
SELECT *
FROM Get_Personnel (
SELECT Period
FROM Conf_History
WHERE SequenceNr = nSequenceNr
);
In the above example I could just as well in-line the content of the
Get_Personnel query into the Get_CurrentPersonnel and
Get_HistoricalPersonnel queries, but my real-world queries are a lot more
complicated and I'm hesitant to duplicate their content in multiple places
(I'd rather re-use than copy/paste).

Any ideas?

Kind regards,
Carl Colijn


 
 
 

How to pass parameter between parameter queries

Post by Jeff Boyc » Sat, 19 Aug 2006 07:49:33

Carl

I'm not clear on how you are using Derived and Base. Which one comes first?

I'm not clear on why you believe you need to use queries to do what you are
doing.

I'm still not very clear on what you hope to accomplish.

Could you rephrase what you want to end up with, without using tables or
queries or anything database-related? Can you just describe it, as you
might to an 80 year-old grandmother?

Thanks

Jeff Boyce
Microsoft Office/Access MVP
 
 
 

How to pass parameter between parameter queries

Post by Carl Colij » Sat, 19 Aug 2006 17:12:02


Hi Jeff,

Well, I do need queries, since I'm updating an existing database and now
need to make the existing queries a bit more intelligent.

Basically what I'm trying to ask is wether it is possible to have a
parametrized 'QueryBase' query be called from another 'QueryDerived' query,
with 'QueryDerived' specifying the parameter value for the 'QueryBase' on
it's own (without using unbound forms and/or VBA to store the actual value
for the parameter in between). Sort of like making 'QueryBase' a procedural
query that can be called from other queries like you would call a regular
procedure in procedural programming, also specifying the vakues for the
parameters of that query.

With the above explanation and query names in mind, I'll re-phrase my
example:

Query 'QueryBase' has a parameter, called 'ParamBase'.

Query 'QueryDerived' uses 'QueryBase' as it's data source (SELECT ... FROM
QueryBase WHERE ...), further processing the recordset that 'QueryBase'
returns. But in order to call 'QueryBase', you need to specify the value
for the 'ParamBase' parameter... In this case, the value to use for
'ParamBase' cannot be specified by the user, but has to be retrieved from
another table in the database. 'QueryDerived' can do that just fine on it's
own (SELECT ParamValueToUse FROM TableConfig), but how do I pass the
retrieved value to the 'QueryBase' query? With procedural programming in
mind, compare it to the following QueryDerived example (whose syntax is
incorrect in Access):
SELECT *
FROM QueryBase ( <= parameter passing
SELECT ParamValue
FROM TableConfig
)
WHERE SomeColumn = SomeValue

Maybe it's my C++/VB background shining through here and I just need to flip
the switch to see the equivalent SQL manner of doing things differently with
the same end result, but so far I haven't found that switch yet...

I can come up with SELECTing the parameter value to use INTO a special
'parameter' table, which in turn will be read by the QueryBase query, but
this makes me shiver a bit (what about concurrency with other users, when to
clean the parameter table, etc.)

Hope I explained it the right way,
Kind regards,
Carl Colijn
 
 
 

How to pass parameter between parameter queries

Post by Jeff Boyc » Sat, 19 Aug 2006 23:12:44

arl

Does your 80 year-old grandmother understand C++? Does she understand
queries?

You've explain more about "how" you're trying to do something. I still
don't understand the "what".

If you had no database, and were doing this with paper and pencil, when you
were finished, what would you have?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Carl Colijn" < XXXX@XXXXX.COM > wrote in message
news:nDeFg.4910$YI3.4353@amstwist00...


 
 
 

How to pass parameter between parameter queries

Post by Jeff Boyc » Sun, 20 Aug 2006 03:13:33

arl

I, too, don't want to be dense on this. It's just that I'm having trouble
imagining how you can run your Base query (the first one to run), when it
requires a criterion that can only be determined by running your second
query (?Derived), which depends on the first one to run?!

How is it that the second-in-line knows what to do before the first even
runs?

If you are saying that the first (Base) query requires a value only
obtainable by running the second, maybe you need to go ahead with your idea
of re-creating a new Base query that takes this all into account.

Or am I still totally baffled? (if so, consider re-posting, to get some new
eyes/brains on this)

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Carl Colijn" < XXXX@XXXXX.COM > wrote in message
news:sOmFg.4923$YI3.1671@amstwist00...


 
 
 

How to pass parameter between parameter queries

Post by Carl Colij » Sun, 20 Aug 2006 05:32:56

eff Boyce wrote:

Hi Jeff,

Thanks for sticking on to this subject so far!

I think I can really explain it best by giving you another example for a
fictious store inventory application (a nice simple environment). Never
mind the simplicity of the queries, in real life they're (much) more
complex.

Imagine we initially have the following table:
Quantity: Long
ItemID: Long
and call this table 'Inventory'. Consider the following query:
SELECT *
FROM Inventory
WHERE Quantity < 10
and let's call this query 'LowSupplyItems'. I also have another query for
you:
SELECT ItemID
FROM LowSupplyItems
WHERE Quantity = 0
we'll call this query 'OutOfStockItems'. On this last query we have based a
report called 'WhatsOutOfStock' to show which items are out of stock.

Version 1.0 of the application worked like a charm for the stores it was
deployed at. Version 2.0 of the application comes along, and the situation
has now changed; the database now resides on a central server and will be
used by all stores simultaneously. The 'Inventory' table now thus requires
an extra column;
StoreID: Long
For ease of use we add a new table to the database that holds the correct
StoreID for each store manager (CurrentUser() );
User: Text
StoreID: Long
and call this table 'StorePersonnel'.

Since each store can have the same items (ItemID), we effectively generate
sets of items per store where there once was only one set of items. The
queries of course need to know which set of items to select. The
'LowSupplyItems' query is used quite a lot in VBA code, and we'd like to
keep it as it is, so we just add the store ID selection as a parameter to it
so we do not have to re-design our whole application:
PARAMETERS pStoreID Long;
SELECT *
FROM Inventory
WHERE Quantity < 10
AND StoreID = pStoreID
Now the query 'OutOfStockItems' has a problem; since when it runs it now
also has to specify the value for the pStoreID parameter. The user doesn't
know which one it is, and frankly just expects the 'WhatsOutOfStock' report
to open without intervention. But we already know the store ID the current
user belongs to; we can easily select it out of the 'StorePersonnel' table;
SELECT StoreID
FROM StorePersonnel
WHERE User = CurrentUser()
We do not want to modify the 'LowSupplyItems' query any further (it needs to
keep it's flexibility as it is now), but it needs the value in the above
SELECT statement as it's value for the pStoreID parameter in order for the
'WhatsOutOfStock' report - and thus the 'OutOfStockItems' query - to work
properly. So we decide to do the lookup in the 'OutOfStockItems' query
itself. (never mind using a filter on the report here; in my case it's not
applicable since I not only base reports on the 'OutOfStockItems' query but
also other queries.)

And now the fun begins: how to accomplish this task? My first thought was
to let 'OutOfStockItems' do the correct StoreID lookup, and let it then pass
the StoreID to the 'LowSupplyItems' query. Something like this (illegal
syntax, but I hope I made my point now):
SELECT ItemID
FROM LowSupplyItems (
SELECT StoreID
FROM StorePersonnel
WHERE User = CurrentUser()
)
WHERE Quantity = 0
but this obviously not correct (MS Access) SQL... The workaround would be
to either make a new copy of 'Lo
 
 
 

How to pass parameter between parameter queries

Post by Jeff Boyc » Sun, 20 Aug 2006 07:24:43

arl

I don't know if you might have already looked into this, so take it with a
grain of salt...

It sounds like you need a way to identify which store (ID) is running the
queries and/or reports. If there was a way the query could "know" which
store (ID) to use, would that solve the issue?

One way to do this is to add a global variable to the application and set a
StoreID value as the application loads up. Also add a function that returns
that variable's value. Thereafter, when the app boots, the variable gets
set, and any query/report that needs storeID gets it by including the
function that returns it.

Is this a viable option?

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Carl Colijn" < XXXX@XXXXX.COM > wrote in message
news:ZtpFg.4926$YI3.4149@amstwist00...


 
 
 

How to pass parameter between parameter queries

Post by Carl Colij » Mon, 21 Aug 2006 01:33:00


Hi Jeff,

It is, to some degree. In this simple example it would work wonders, but in
my case the value to use is sometimes not static (but it can be found
dynamically by the query from some table). I can implement this 'determine
the value' functionality into a VBA function, that sets a global VBA
variable, and then consecutively use that global variable from within the
queries. I would of course need to run this 'determine the value' function
before I actually opened e.g. the report, but that can easily be done as
well. But this construct would add complexity to the queries; I'd rather
keep it SQL-only.

I've also just talked with an Oracle expert, and she thinks it's also not a
possibility in plain SQL (at least in Oracle). So it seems I was barking up
the wrong tree after all.

I guess I'll just have to stick with VBA, or copy the SQL functionality all
over the place...

Anyway, thanks for your effort, and have a nice weekend!
Kind regards,
Carl Colijn