Help with a totals or sum query

[Scuda]

Hi All, I currently using

=DMax("[SENE_CASE_#]","tblSENEIncidentLogCY")

to give me a count of ALL Incidents (SENE_CASE_#), i know about the poor
labeling, I am changing it.

BUT, what I would like is a TOTAL of the TYPE of incidents in the
tblSENEIncidentLogCY. In my field NATURE OF DISTRESS (in that table) there
are 4 types, they will have either SAR, ADMIN, LE, or M in the NATURE OF
DISTRESS.

How can I modify my code to get specific totals?

Thanks so much!

Steph

 

[Frank H]

Dmax will give you the highest value in the domain (table).
DCount is what you would want to use.
The syntax depends upon the data type of [Nature of Distress].
If a string:
DCount("[SENE_CASE_#]","tblSENEIncidentLogCY", "[Nature of Distress]='SAR'")


You would have to do a separate Dcount for each of the 4 types.

The best way to get what you are looking for is with a Totals query.

In the query design view, turn on the Totals row and bring down the NATURE
OF DISTRESS field twice. In the first field on the design grid, set the
totals row to Group By, on the second field set the Totals row to Count. When
you run it you should get what you describe.

 

[Scuda]

Thanks guys, sorry for the delay, for some reason I am not getting e-mail
notifications. I am still having a bit of trouble.

The NATURE OF DISTRESS is a combo box, that looks up on a table.

Dmax will give you the highest value in the domain (table).
DCount is what you would want to use.
The syntax depends upon the data type of [Nature of Distress].
If a string:
DCount("[SENE_CASE_#]","tblSENEIncidentLogCY", "[Nature of Distress]='SAR'")


You would have to do a separate Dcount for each of the 4 types.

The best way to get what you are looking for is with a Totals query.

In the query design view, turn on the Totals row and bring down the NATURE
OF DISTRESS field twice. In the first field on the design grid, set the
totals row to Group By, on the second field set the Totals row to Count. When
you run it you should get what you describe.
--
Frank H
Rockford, IL

Hi All, I currently using

=DMax("[SENE_CASE_#]","tblSENEIncidentLogCY")

to give me a count of ALL Incidents (SENE_CASE_#), i know about the poor
labeling, I am changing it.

BUT, what I would like is a TOTAL of the TYPE of incidents in the
tblSENEIncidentLogCY. In my field NATURE OF DISTRESS (in that table) there
are 4 types, they will have either SAR, ADMIN, LE, or M in the NATURE OF
DISTRESS.

How can I modify my code to get specific totals?

Thanks so much!

Steph

 

[Scuda]

Hi Ken, thanks again.

Here is the info. I have a Search and Rescue Log here, and onthe "main"
page, I have what I call a "dashboard" It is a FORM, that has Snapshot
Statistical Info, number of Boardings, Number of Rescues, etc.

The record source for that form is a combo of 3 tables, but the main one
being tblSENEIncidentLogCY. I don't have a Foriegn Key as far as I know.

I am attempting to get a count of how many text boxes in the NATURE OF
DISTRESS are for SAR. I just want a total count of how many SAR cases we
have run so far.

Does that make sense?

Thanks again!

Well, a combo box is just a control in which the data is shown. What I think
you probably mean is that it is a numeric foreign key column in
tblSENEIncidentLogCY which references the primary key of another table.
We'll need to know the names of foreign key and primary key columns in the
tables, the name of the text column containing the Nature of Distress values
in the other table, and the name of the other table, Also, its still not
clear whether you want to count the rows returned or sum the values in the
SENE_CASE_# column.

And can you explain the context in which you are doing this; is it a form,
report, query, whatever? Your subject line suggests a query, but the fact
that the expression is preceded by an = sign suggests a control in a form or
report. If a form or report what is the form or report's RecordSource?
We'll probably be better able to advise if you tell us what you are
attempting to do, rather than how you are trying to do it.

If you used the 'lookup wizard' for the data type of the Nature of Distress
column when designing the tblSENEIncidentLogCY table, for future reference
avoid this feature like the plague. For reasons why see:

http://www.mvps.org/access/lookupfields.htm

Ken Sheridan
Stafford, England

Thanks guys, sorry for the delay, for some reason I am not getting e-mail
notifications. I am still having a bit of trouble.

The NATURE OF DISTRESS is a combo box, that looks up on a table.

Dmax will give you the highest value in the domain (table).
DCount is what you would want to use.

[quoted text clipped - 28 lines]

Steph
Steph

--
Message posted via AccessMonster.com


.

 

[Scuda]

Just to clarify a little.

I am using =DMax("[SENE_CASE_#]","tblSENEIncidentLogCY") as my DEFAULT
VALUE of an Unbound text box, which gives me a good total of ALL incidents in
NATURE OF DISTRESS, but as I mentioned earlier, i just want a total of the
NATURE OF DISTRESS entries that have SAR in the text.

Steph

Hi Ken, thanks again.

Here is the info. I have a Search and Rescue Log here, and onthe "main"
page, I have what I call a "dashboard" It is a FORM, that has Snapshot
Statistical Info, number of Boardings, Number of Rescues, etc.

The record source for that form is a combo of 3 tables, but the main one
being tblSENEIncidentLogCY. I don't have a Foriegn Key as far as I know.

I am attempting to get a count of how many text boxes in the NATURE OF
DISTRESS are for SAR. I just want a total count of how many SAR cases we
have run so far.

Does that make sense?

Thanks again!

Well, a combo box is just a control in which the data is shown. What I think
you probably mean is that it is a numeric foreign key column in
tblSENEIncidentLogCY which references the primary key of another table.
We'll need to know the names of foreign key and primary key columns in the
tables, the name of the text column containing the Nature of Distress values
in the other table, and the name of the other table, Also, its still not
clear whether you want to count the rows returned or sum the values in the
SENE_CASE_# column.

And can you explain the context in which you are doing this; is it a form,
report, query, whatever? Your subject line suggests a query, but the fact
that the expression is preceded by an = sign suggests a control in a form or
report. If a form or report what is the form or report's RecordSource?
We'll probably be better able to advise if you tell us what you are
attempting to do, rather than how you are trying to do it.

If you used the 'lookup wizard' for the data type of the Nature of Distress
column when designing the tblSENEIncidentLogCY table, for future reference
avoid this feature like the plague. For reasons why see:

http://www.mvps.org/access/lookupfields.htm

Ken Sheridan
Stafford, England

Thanks guys, sorry for the delay, for some reason I am not getting e-mail
notifications. I am still having a bit of trouble.

The NATURE OF DISTRESS is a combo box, that looks up on a table.

Dmax will give you the highest value in the domain (table).
DCount is what you would want to use.
[quoted text clipped - 28 lines]

Steph

Steph

--
Message posted via AccessMonster.com


.