Sums from two criteria

[Tim Shepherd]

Good day,

I am trying to get some totals on a subform, which has the fields:

Goats (continuous subform), information on groups of goats observed during a
survey.
Survey Key (foreign key)
Quadrant (text, one of four compass points)
Number Adults (integer)
Number Young (integer)
Group Total (Adult + Young, from query)

What I want to show is a total for each quadrant in a survey and a grand
total for the survey. In the subform footer, in unbound text boxes, I am
able to get the survey total with a DSum and restricting it to Survey Key. I
cannot figure out how to get the totals for the quadrants. The DSums I have
for each quadrant are giving me grand totals for the quad (adding multiple
surveys) rather than restricted to one survey (example of DSum:
Nz(DSum(“[Goup_Total]â€, “qryGoatsâ€, “[Quadrant]=’NE’â€),0)). Can anyone
assist me with getting a sum based on two criteria (quadrant and survey key),
or a better way to get these values on the form? Using Access 2003.

Thank you in advance,
Tim

 

[Allen Browne]

For an example of the criteria you need, mock up a query using qryGoats as
the source 'table', and some test values as criteria for quadrant and key.
Then switch the query to SQL View (View menu), and look at the WHERE clause.

The 3rd argument for DSum will probably look something like this when you
add the quotes:
"(Quadrant='NE') AND ([Survey Key] = 99)"
If [Survey Key] is a Text field (not a Number field), it will need the quote
marks around it too.

 

[Tim Shepherd]

Thanks Allen,

I tried setting a DSum with and AND in the third argument, and I am getting
a interesting number. It is giving me a value of 36 (there are four survey
records entered so far with values for the quadrant (NW) as 2, 3, 3, and 2).
I have the DSum set up as (maybe I missed something):
=Nz(DSum("[Group_Total]","qryGoat_Detection",("[Goat_Survey_Key]= " &
[Goat_Survey_Key]) And ("[Quadrant]='NW'")),0)

The SQL for a totals query that provides the values I'm looking for is:
SELECT qryGoat_Detection.Goat_Survey_Key, Sum(qryGoat_Detection.Group_Total)
AS NW_Total
FROM qryGoat_Detection
WHERE (((qryGoat_Detection.Quadrant)="NW"))
GROUP BY qryGoat_Detection.Goat_Survey_Key;

Tim

For an example of the criteria you need, mock up a query using qryGoats as
the source 'table', and some test values as criteria for quadrant and key.
Then switch the query to SQL View (View menu), and look at the WHERE clause.

The 3rd argument for DSum will probably look something like this when you
add the quotes:
"(Quadrant='NE') AND ([Survey Key] = 99)"
If [Survey Key] is a Text field (not a Number field), it will need the quote
marks around it too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good day,

I am trying to get some totals on a subform, which has the fields:

Goats (continuous subform), information on groups of goats observed during
a
survey.
Survey Key (foreign key)
Quadrant (text, one of four compass points)
Number Adults (integer)
Number Young (integer)
Group Total (Adult + Young, from query)

What I want to show is a total for each quadrant in a survey and a grand
total for the survey. In the subform footer, in unbound text boxes, I am
able to get the survey total with a DSum and restricting it to Survey Key.
I
cannot figure out how to get the totals for the quadrants. The DSums I
have
for each quadrant are giving me grand totals for the quad (adding multiple
surveys) rather than restricted to one survey (example of DSum:
Nz(DSum(“[Goup_Total]â€, “qryGoatsâ€, “[Quadrant]=’NE’â€),0)). Can anyone
assist me with getting a sum based on two criteria (quadrant and survey
key),
or a better way to get these values on the form? Using Access 2003.

 

[Allen Browne]

You don't have the AND inside the quotes:

=DSum("[Group_Total]", "qryGoat_Detection",
"([Goat_Survey_Key] = " & Nz([Goat_Survey_Key],0) & ") And
([Quadrant]='NW')")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I tried setting a DSum with and AND in the third argument, and I am
getting
a interesting number. It is giving me a value of 36 (there are four
survey
records entered so far with values for the quadrant (NW) as 2, 3, 3, and
2).
I have the DSum set up as (maybe I missed something):
=Nz(DSum("[Group_Total]","qryGoat_Detection",("[Goat_Survey_Key]= " &
[Goat_Survey_Key]) And ("[Quadrant]='NW'")),0)

The SQL for a totals query that provides the values I'm looking for is:
SELECT qryGoat_Detection.Goat_Survey_Key,
Sum(qryGoat_Detection.Group_Total)
AS NW_Total
FROM qryGoat_Detection
WHERE (((qryGoat_Detection.Quadrant)="NW"))
GROUP BY qryGoat_Detection.Goat_Survey_Key;

Tim

For an example of the criteria you need, mock up a query using qryGoats
as
the source 'table', and some test values as criteria for quadrant and
key.
Then switch the query to SQL View (View menu), and look at the WHERE
clause.

The 3rd argument for DSum will probably look something like this when you
add the quotes:
"(Quadrant='NE') AND ([Survey Key] = 99)"
If [Survey Key] is a Text field (not a Number field), it will need the
quote
marks around it too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good day,

I am trying to get some totals on a subform, which has the fields:

Goats (continuous subform), information on groups of goats observed
during
a
survey.
Survey Key (foreign key)
Quadrant (text, one of four compass points)
Number Adults (integer)
Number Young (integer)
Group Total (Adult + Young, from query)

What I want to show is a total for each quadrant in a survey and a
grand
total for the survey. In the subform footer, in unbound text boxes, I
am
able to get the survey total with a DSum and restricting it to Survey
Key.
I
cannot figure out how to get the totals for the quadrants. The DSums I
have
for each quadrant are giving me grand totals for the quad (adding
multiple
surveys) rather than restricted to one survey (example of DSum:
Nz(DSum(“[Goup_Total]â€, “qryGoatsâ€, “[Quadrant]=’NE’â€),0)). Can anyone
assist me with getting a sum based on two criteria (quadrant and survey
key),
or a better way to get these values on the form? Using Access 2003.

 

[Tim Shepherd]

Thanks Allen!

That did the trick.
Cheers,
TIm

You don't have the AND inside the quotes:

=DSum("[Group_Total]", "qryGoat_Detection",
"([Goat_Survey_Key] = " & Nz([Goat_Survey_Key],0) & ") And
([Quadrant]='NW')")

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks Allen,

I tried setting a DSum with and AND in the third argument, and I am
getting
a interesting number. It is giving me a value of 36 (there are four
survey
records entered so far with values for the quadrant (NW) as 2, 3, 3, and
2).
I have the DSum set up as (maybe I missed something):
=Nz(DSum("[Group_Total]","qryGoat_Detection",("[Goat_Survey_Key]= " &
[Goat_Survey_Key]) And ("[Quadrant]='NW'")),0)

The SQL for a totals query that provides the values I'm looking for is:
SELECT qryGoat_Detection.Goat_Survey_Key,
Sum(qryGoat_Detection.Group_Total)
AS NW_Total
FROM qryGoat_Detection
WHERE (((qryGoat_Detection.Quadrant)="NW"))
GROUP BY qryGoat_Detection.Goat_Survey_Key;

Tim

For an example of the criteria you need, mock up a query using qryGoats
as
the source 'table', and some test values as criteria for quadrant and
key.
Then switch the query to SQL View (View menu), and look at the WHERE
clause.

The 3rd argument for DSum will probably look something like this when you
add the quotes:
"(Quadrant='NE') AND ([Survey Key] = 99)"
If [Survey Key] is a Text field (not a Number field), it will need the
quote
marks around it too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Good day,

I am trying to get some totals on a subform, which has the fields:

Goats (continuous subform), information on groups of goats observed
during
a
survey.
Survey Key (foreign key)
Quadrant (text, one of four compass points)
Number Adults (integer)
Number Young (integer)
Group Total (Adult + Young, from query)

What I want to show is a total for each quadrant in a survey and a
grand
total for the survey. In the subform footer, in unbound text boxes, I
am
able to get the survey total with a DSum and restricting it to Survey
Key.
I
cannot figure out how to get the totals for the quadrants. The DSums I
have
for each quadrant are giving me grand totals for the quad (adding
multiple
surveys) rather than restricted to one survey (example of DSum:
Nz(DSum(“[Goup_Total]â€, “qryGoatsâ€, “[Quadrant]=’NE’â€),0)). Can anyone
assist me with getting a sum based on two criteria (quadrant and survey
key),
or a better way to get these values on the form? Using Access 2003.