How to pass parameter between parameter queries

[Carl Colijn]

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

 

[Jeff Boyce]

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

 

[Carl Colijn]

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

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

 

[Jeff Boyce]

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

 

[Carl Colijn]

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

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

 

[Jeff Boyce]

Carl

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]

Carl

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

Hi Jeff,

I'm not trying to be dense here, but I already have the database, which is a
fact I cannot ignore; I have to use the existing queries and work from that.
The problem is I haven't got enough time on this project to do a redesign of
the application in order not to have to use these queries (I have all sorts
of forms, reports and other queries based on them). And I guessed your
SQL/Access/Programming vocabulary was richer than your 80-years old
grandmother's...

What it comes down to is that I already have some 'base' queries. I use the
result of these 'base' queries as the source for existing 'derived' queries,
and on these 'derived' queries I have based forms, reports and other
queries.

Due to a new feature being added to the application, the 'base' queries must
now accept a parameter to do their job (effectively selecting between
different sets of data in the same table where there first was no notion of
data sets at all, so all records formed a single set). The 'derived'
queries can easily find out the right value for the parameter of these
'base' queries (from tables and/or other queries). Because I do not want
any input boxes to pop up in the user's face when using these 'derived'
queries (they do not know the correct value anyway), I want to somehow pass
this value through from the 'derived' query (that has already looked it up
in a table e.g.) to the 'base' query, preferably using only SQL.

I realize I can solve this problem by making a dedicated duplicate of the
'base' query for each 'derived' query, and then let this copy of the 'base'
query do the lookup of the correct value on it's own (what I had originally
planned as a job for the 'derived' queries). Then the new version of the
'base' query doesn't need the parameter anymore, and I can safely base my
'derived' query on it. Or, if I'm at it, I could just as well completely
in-line the 'base' query in the derived query so I do not have to pass
anything around either.

The problem I have with the above approach is that I'm then duplicating the
functionality of the 'base' queries all over the place, effectively
exploding the chance of maintainance nightmares, which I'd rather avoid.

I hope I managed to make my situation clear this time,
Kind regards,
Carl Colijn

 

[Jeff Boyce]

Carl

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]

Carl

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

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 'LowSupplyItems' that does the store ID lookup
on it's own;
SELECT Inventory.*
FROM Inventory
INNER JOIN StorePersonnel
ON Inventory.StoreID = StorePersonnel.StoreID
WHERE Inventory.Quantity < 10
AND StorePersonnel.User = CurrentUser()
we could call this query 'LowSupplyItemsForMyStore'. The query
'OutOfStockItems' can now safely be based upon this query instead, and
doesn't need to find out which store ID to select anymore.
But since we just did this, I could just as well merge this SQL into the
'OutOfStockItems' query itself, and get rid of the extra
'LowSupplyItemsForMyStore' query that is only used by the 'OutOfStockItems'
query;
SELECT Inventory.ItemID
FROM Inventory
INNER JOIN StorePersonnel
ON Inventory.StoreID = StorePersonnel.StoreID
WHERE Inventory.Quantity = 0
AND StorePersonnel.User = CurrentUser()
Case closed; we have a working solution.

But there is one thing nagging me about the above solution: for each
'OutOfStockItems'-like query (there are quite a lot of them hanging around),
I effectively duplicated the functionality for 'LowSupplyItems' . What if
'LowSupplyItems' needs to change in the future? We'd have a maintainance
nightmare on our hands...

To link back to my previous posts, you can use the following mapping:
LowSupplyItems = QueryBase
OutOfStockItems = QueryDerived

My take on this was to first investigate whether queries could specify the
values for the parameters of the other queries they use on their own. But
I'm starting to guess I'm chasing a red herring here...

What's your take on this?
Kind regards,
Carl Colijn

 

[Jeff Boyce]

Carl

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]

Carl

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

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