Query Source for Text Box

[merry_fay]

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

 

[XPS350]

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

In DSum the first parameter is tke field you want to sum (V in your
case).
The second one is the table (or query) V is in (T1).
The third, optional, paramater is a condition (like "Code=2", meaning
to sum only from records where code equals 2). Your third doen't look
like that.

Groeten,

Peter
http://access.xps350.com

 

[merry_fay]

Does that mean I can use my query on the second section of DSum?

I did try that first but it still didn't work. How does the query need to be
done to use it in the second part?

Thanks
merry_fay

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

In DSum the first parameter is tke field you want to sum (V in your
case).
The second one is the table (or query) V is in (T1).
The third, optional, paramater is a condition (like "Code=2", meaning
to sum only from records where code equals 2). Your third doen't look
like that.

Groeten,

Peter
http://access.xps350.com
.

 

[XPS350]

Does that mean I can use my query on the second section of DSum?

I did try that first but it still didn't work. How does the query need to be
done to use it in the second part?

Thanks
merry_fay

Hi,
I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:
=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")
But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?
I have quite a few text cells to do based on different criteria & tables.
Thanks
merry_fay

In DSum the first parameter is tke field you want to sum (V in your
case).
The second one is the table (or query) V is in (T1).
The third, optional, paramater is a condition (like "Code=2", meaning
to sum only from records where code equals 2). Your third doen't look
like that.

Peter
http://access.xps350.com
.

If you have stored the query as Q1 and V is part of the query, then
=DSum("V";"Q") is a valid expression.

Groeten,

Peter
http://access.xps350.com

 

[John W. Vinson]

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

The third argument to DSUM (or any domain function) is not a full SQL query as
you are using; instead it's just the WHERE clause of a query (without the word
WHERE).

I'd suggest taking the SELECT that you're using, store it as a query in its
own right, and just use DLookUp on that query.

 

[Daryl S]

Merry_fay -

This will be much easier if you set up a new query with the joins you need,
then do a DSUM from that. Your new query should conbain the field you want
to sum as well as any fields that might contain the criteria. Something
like this:

SELECT Sum(Value) AS V, [Cost Type], Flag FROM CostTypeLook INNER JOIN
(CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co)

Then use that query (say you saved it as NewQryName), and then DSUM based on
that query, using the criteria you want:

=DSum("V","NewQryName","[Cost Type]=10 AND CCLook.Flag='NP'")

 

[merry_fay]

Is saving it as a new query the only way to do this?

If so, I'm going to have to write about 20 extra queries in my database as I
have to do a lot of these text cells based on different tables each with
different links & criteria.

Is there no other way of setting the value in the text cell as the output of
my SQL statement eg code in the 'on load' function?

Thanks
merry_fay

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

The third argument to DSUM (or any domain function) is not a full SQL query as
you are using; instead it's just the WHERE clause of a query (without the word
WHERE).

I'd suggest taking the SELECT that you're using, store it as a query in its
own right, and just use DLookUp on that query.

 

[Daryl S]

Merry_fay -

What is the problem with the extra queries? They don't take up much space
(they don't store data), and it is pretty easy to maintain.


--
Daryl S

Is saving it as a new query the only way to do this?

If so, I'm going to have to write about 20 extra queries in my database as I
have to do a lot of these text cells based on different tables each with
different links & criteria.

Is there no other way of setting the value in the text cell as the output of
my SQL statement eg code in the 'on load' function?

Thanks
merry_fay

Hi,

I'm trying to set the value of a text cell as the sum of the values in one
table with conditions based on returned values from linked tables:

=DSum("V","T1","(SELECT Sum(Value) AS V FROM CostTypeLook INNER JOIN (CCLook
INNER JOIN CostBase ON (CCLook.[Cost Centre] = CostBase.[Cost Centre]) AND
(CCLook.Co = CostBase.Co)) ON (CostTypeLook.Account = CostBase.Account) AND
(CostTypeLook.Co = CostBase.Co) WHERE CostTypeLook.[Cost Type]=10 AND
CCLook.Flag='NP') AS T1")

But of course it isn't working which is why I'm having to ask for help!
Is there a way to do this without having to write individual queries?

I have quite a few text cells to do based on different criteria & tables.

Thanks
merry_fay

The third argument to DSUM (or any domain function) is not a full SQL query as
you are using; instead it's just the WHERE clause of a query (without the word
WHERE).

I'd suggest taking the SELECT that you're using, store it as a query in its
own right, and just use DLookUp on that query.