passing value to a parameter subquery

  • Thread starter Thread starter antonette
  • Start date Start date
A

antonette

I've seen so many threads regarding this, but haven't seen the answer
I'm looking for:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

Item_Pricing is a parameter query that will prompt for [market]. I'd
like to pass Styles.market as the value for the parameter, but can't
figure out how to do so.

I can use:
PARAMETERS [market] currency = 350;
SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

.... which will work, but "PARAMETERS [market] currency =
[Styles].[market];" does not. I know how to set this up within VBA,
but want this done via SQL/queries.

Thanks all --
 
Hi,

That can be seen just on a purely technical basis: A parameter can hold
just one value, if there are N records in table Styles, Styles.market is a
set of N values. N values cannot fit where this is room for just one. So, a
parameter cannot represent Styles.market.



Hoping it may help,
Vanderghast, Access MVP
 
So there couldn't be a way to calculate the query on a record level,
using each Styles.market value as the parameter to recalculate
Item_Pricing.cost each time?

Maybe an actual subquery, like:
SELECT Styles.style_id, (SELECT Item_Pricing.cost FROM Item_Pricing
WHERE ...) as RecordCost, Styles.market FROM Styles;

There must be a way, I can feel it... :-p



Michel Walsh said:
Hi,

That can be seen just on a purely technical basis: A parameter can hold
just one value, if there are N records in table Styles, Styles.market is a
set of N values. N values cannot fit where this is room for just one. So, a
parameter cannot represent Styles.market.



Hoping it may help,
Vanderghast, Access MVP


antonette said:
I've seen so many threads regarding this, but haven't seen the answer
I'm looking for:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

Item_Pricing is a parameter query that will prompt for [market]. I'd
like to pass Styles.market as the value for the parameter, but can't
figure out how to do so.

I can use:
PARAMETERS [market] currency = 350;
SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

... which will work, but "PARAMETERS [market] currency =
[Styles].[market];" does not. I know how to set this up within VBA,
but want this done via SQL/queries.

Thanks all --
Click to expand...
Click to expand...
 
Hi,

Indeed, if the value is dependant of the record "in consideration" by the
computation, then a JOIN, direct or indirectly (where clause, sub-query,
etc.) is the way to proceed. My answer was more to demonstrate the
"incompatibilities" between the concept of a parameter and the concept of a
whole column from a table.

You did, in you first message, an example of a JOIN, implying "item_id"
fields, it performs the match, on a record by record basis, as it seems you
still seek for another field/condition, but at that point, the involved
relation is not clearly presented in your post (at least, I personally fail
to see it).




Hoping it may help,
Vanderghast, Access MVP


antonette said:
So there couldn't be a way to calculate the query on a record level,
using each Styles.market value as the parameter to recalculate
Item_Pricing.cost each time?

Maybe an actual subquery, like:
SELECT Styles.style_id, (SELECT Item_Pricing.cost FROM Item_Pricing
WHERE ...) as RecordCost, Styles.market FROM Styles;

There must be a way, I can feel it... :-p



"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Hi,

That can be seen just on a purely technical basis: A parameter can hold
just one value, if there are N records in table Styles, Styles.market is a
set of N values. N values cannot fit where this is room for just one. So, a
parameter cannot represent Styles.market.



Hoping it may help,
Vanderghast, Access MVP


antonette said:
I've seen so many threads regarding this, but haven't seen the answer
I'm looking for:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

Item_Pricing is a parameter query that will prompt for [market]. I'd
like to pass Styles.market as the value for the parameter, but can't
figure out how to do so.

I can use:
PARAMETERS [market] currency = 350;
SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

... which will work, but "PARAMETERS [market] currency =
[Styles].[market];" does not. I know how to set this up within VBA,
but want this done via SQL/queries.

Thanks all --
Click to expand...
Click to expand...
Click to expand...
 
Hello, thanks for the help so far.

On a simplified level, this is what I have:

_TABLE:ITEMS_
item_id(key)
vendor_id
gram_wt

Table Items lists items and their vendor and gram weight. The cost of
each item depends on the gold market and the vendor's pricing formula,
so I have query Item_Pricing set up that takes in [market] and
calculates cost:

_Query: Item_Pricing_
SELECT Item.item_id, Item.vendor_id, Item.gram_wt as grmwt,
EvalGoldCostPerGram(Item.vendor,nz([market],0)) AS goldcost_pergram,
[goldcost_pergram]*[grmwt] AS cost FROM Item;

Item_Pricing actually calls a user function EvalGoldCostPerGram() that
I've created in a VBA module. As you can see, [market] isn't an
actual field here, only a parameter that gets prompted for.

Table Styles is a list of styles and its components
(Styles.component_id corresponds to Item.item_id), with a gold market
listed for each component:

_Table:Styles_
ID(key)
style_id
component_id
market

Then my final query is trying to link Styles.market to the [market]
parameter in Item_Pricing to calculate the cost per
Styles.component_id:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

An example of Table Styles:
ID style_id component_id market
1 1 234 350
2 1 235 350
3 2 235 400
4 2 236 400
Here, both style #s 1 and 2 use component/item #235. But with the
different markets, each #235 will end up with a different cost.
 
Hi,


I see what is going on, better, now. You want to re-use your query
Item_Pricing that requires a parameter, but inside another query, where you
hoped to be able to feed the parameter with different values. That is maybe
possible, but I think it is better to re-write the final query, to get a
more standard solution, without hack.

First, I start with a modification to read the final query ( work on a
backup , just in case):


SELECT Styles.style_id, Styles.market
FROM Styles LEFT JOIN Item
ON Styles.component_id = Item.item_id


Note that I removed Item_Pricing, and, in the join, replaced it by Item, the
only table involved in Item_Pricing .

Once this is done, I would bring back the content of Item_Pricing, but only
the content that was in the SELECT clause:



SELECT Styles.style_id, Styles.market,
Item.gram_wt as grmwt,
EvalGoldCostPerGram(Item.vendor,nz(Styles.market,0)) AS
goldcost_pergram,
[goldcost_pergram]*[grmwt] AS cost
FROM Styles LEFT JOIN Item
ON Styles.component_id = Item.item_id


note I also replaced [market] by Styles.market, inside EvalGoldCostPerGram.


Sure, you can hand edit that final query, or use the graphical editor, it
should work in this case.

As you see, we are not very far from your initial work. I just hope it
relates well with the real work, that the proposed solution is easily
transposable to your real context.


Hoping it may help,
Vanderghast, Access MVP





antonette said:
Hello, thanks for the help so far.

On a simplified level, this is what I have:

_TABLE:ITEMS_
item_id(key)
vendor_id
gram_wt

Table Items lists items and their vendor and gram weight. The cost of
each item depends on the gold market and the vendor's pricing formula,
so I have query Item_Pricing set up that takes in [market] and
calculates cost:

_Query: Item_Pricing_
SELECT Item.item_id, Item.vendor_id, Item.gram_wt as grmwt,
EvalGoldCostPerGram(Item.vendor,nz([market],0)) AS goldcost_pergram,
[goldcost_pergram]*[grmwt] AS cost FROM Item;

Item_Pricing actually calls a user function EvalGoldCostPerGram() that
I've created in a VBA module. As you can see, [market] isn't an
actual field here, only a parameter that gets prompted for.

Table Styles is a list of styles and its components
(Styles.component_id corresponds to Item.item_id), with a gold market
listed for each component:

_Table:Styles_
ID(key)
style_id
component_id
market

Then my final query is trying to link Styles.market to the [market]
parameter in Item_Pricing to calculate the cost per
Styles.component_id:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

An example of Table Styles:
ID style_id component_id market
1 1 234 350
2 1 235 350
3 2 235 400
4 2 236 400
Here, both style #s 1 and 2 use component/item #235. But with the
different markets, each #235 will end up with a different cost.





"Michel Walsh" <vanderghast@VirusAreFunnierThanSpam> wrote in message
Hi,

Indeed, if the value is dependant of the record "in consideration" by the
computation, then a JOIN, direct or indirectly (where clause, sub-query,
etc.) is the way to proceed. My answer was more to demonstrate the
"incompatibilities" between the concept of a parameter and the concept of a
whole column from a table.

You did, in you first message, an example of a JOIN, implying "item_id"
fields, it performs the match, on a record by record basis, as it seems you
still seek for another field/condition, but at that point, the involved
relation is not clearly presented in your post (at least, I personally fail
to see it).




Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
I was afraid you were going to suggest that. Since the actual queries
are more complex, it seems somewhat inefficient to me to copy over the
content. But I'm sure it will work and will probably do just that.

Thanks very much for your help!



Michel Walsh said:
Hi,


I see what is going on, better, now. You want to re-use your query
Item_Pricing that requires a parameter, but inside another query, where you
hoped to be able to feed the parameter with different values. That is maybe
possible, but I think it is better to re-write the final query, to get a
more standard solution, without hack.

First, I start with a modification to read the final query ( work on a
backup , just in case):


SELECT Styles.style_id, Styles.market
FROM Styles LEFT JOIN Item
ON Styles.component_id = Item.item_id


Note that I removed Item_Pricing, and, in the join, replaced it by Item, the
only table involved in Item_Pricing .

Once this is done, I would bring back the content of Item_Pricing, but only
the content that was in the SELECT clause:



SELECT Styles.style_id, Styles.market,
Item.gram_wt as grmwt,
EvalGoldCostPerGram(Item.vendor,nz(Styles.market,0)) AS
goldcost_pergram,
[goldcost_pergram]*[grmwt] AS cost
FROM Styles LEFT JOIN Item
ON Styles.component_id = Item.item_id


note I also replaced [market] by Styles.market, inside EvalGoldCostPerGram.


Sure, you can hand edit that final query, or use the graphical editor, it
should work in this case.

As you see, we are not very far from your initial work. I just hope it
relates well with the real work, that the proposed solution is easily
transposable to your real context.


Hoping it may help,
Vanderghast, Access MVP





antonette said:
Hello, thanks for the help so far.

On a simplified level, this is what I have:

_TABLE:ITEMS_
item_id(key)
vendor_id
gram_wt

Table Items lists items and their vendor and gram weight. The cost of
each item depends on the gold market and the vendor's pricing formula,
so I have query Item_Pricing set up that takes in [market] and
calculates cost:

_Query: Item_Pricing_
SELECT Item.item_id, Item.vendor_id, Item.gram_wt as grmwt,
EvalGoldCostPerGram(Item.vendor,nz([market],0)) AS goldcost_pergram,
[goldcost_pergram]*[grmwt] AS cost FROM Item;

Item_Pricing actually calls a user function EvalGoldCostPerGram() that
I've created in a VBA module. As you can see, [market] isn't an
actual field here, only a parameter that gets prompted for.

Table Styles is a list of styles and its components
(Styles.component_id corresponds to Item.item_id), with a gold market
listed for each component:

_Table:Styles_
ID(key)
style_id
component_id
market

Then my final query is trying to link Styles.market to the [market]
parameter in Item_Pricing to calculate the cost per
Styles.component_id:

SELECT Styles.style_id, Item_Pricing.cost, Styles.market FROM Styles
LEFT JOIN Item_Pricing ON Styles.component_id = Item_Pricing.item_id;

An example of Table Styles:
ID style_id component_id market
1 1 234 350
2 1 235 350
3 2 235 400
4 2 236 400
Here, both style #s 1 and 2 use component/item #235. But with the
different markets, each #235 will end up with a different cost.
Click to expand...
Click to expand...
 
Back
Top