Query Source for Text Box

  • Thread starter Thread starter merry_fay
  • Start date Start date
M

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
 
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
Click to expand...

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
 
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



XPS350 said:
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
Click to expand...

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
.
Click to expand...
 
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

XPS350 said:
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
Click to expand...
Click to expand...
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
.
Click to expand...
Click to expand...

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
 
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
Click to expand...

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.
 
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'")
 
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

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
 
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


merry_fay said:
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

John W. Vinson said:
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
Click to expand...

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.
Click to expand...
Click to expand...
 
Back
Top