subQuery for a total

[jscriptor09]

Hi

I am trying to figure out how to SQL this situation

I have a SQL statement that select from a Table, Groups product sold by [Store ID] while summing up products [Sold This Week].

This works fine. So the fields are
[Fiscal Week], [Store ID], Product, [Sold This Week]



I need to add subQuery that so that using same Table, I want to Sum, in same row, for each Product [Total Sold] in each [Store ID] so that the final list of fields looks like this

[Fiscal Week], [Store ID] as theStore, Product, [Sold This Week] , [Total Sold]

if I try to set up the Query "Pseudo" like this

[Fiscal Week], [Store ID] as theStore, Product, [Sold This Week] , (Select from Table sum([Sold]) where [Store ID]=theStore ) as [Total Sold]

it keeps prompting for theStore .

Question:
Is there a way to pass theStore as a parameter to the sub query?

If there is an example somewhere on the web, I would appreciate a pointed and even a example query for a similar situation.

thanks for the help.

Mike

[rpeare]

I don't understand your question, can you give an example of your starting data and what you want the end result to be.