counting and placing answer in text box

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total for
each , total absent, total holiday,etc, but only for the id shown on the form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient

Access has a DCount function that will do this for you. Lookup DCount in the
Help file and see if you can get it to work. If you have trouble, post back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on the
form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient

i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab query
and use forms![main form]![id] in the id criteria box and then in the am
criteria box of the am field i put "absent" and count in the next totals row
then this will show the total amount of "absents" in the result, but how do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in the
Help file and see if you can get it to work. If you have trouble, post back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on the
form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient

i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab query
and use forms![main form]![id] in the id criteria box and then in the am
criteria box of the am field i put "absent" and count in the next totals row
then this will show the total amount of "absents" in the result, but how do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in the
Help file and see if you can get it to work. If you have trouble, post back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on the
form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient

i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab query
and use forms![main form]![id] in the id criteria box and then in the am
criteria box of the am field i put "absent" and count in the next totals row
then this will show the total amount of "absents" in the result, but how do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in the
Help file and see if you can get it to work. If you have trouble, post back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on the
form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient

Assuming your InputTbl table has a field named "Status", do the following:

Add a text box to your main form and label it Total Absent. Put the
following in the Control Source property of the text box:

=DCount("[Status]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND
[Status] = 'Status'"

Note: That is a single quote and double quote at the end.

Do the same for the other statuses.

Steve



"bigmac" wrote in message
...
i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab
query
and use forms![main form]![id] in the id criteria box and then in the
am
criteria box of the am field i put "absent" and count in the next totals
row
then this will show the total amount of "absents" in the result, but how
do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in
the
Help file and see if you can get it to work. If you have trouble, post
back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and
can
contain one of the following, "absent","present","placement",
holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on
the
form
example id1 shows all absents,holidays,placements etc for that
particular
id, also last bit, can this be done so that it automaticaly updates
after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing
me.
regards mac
learner ,please be patient

Assuming the expression is otherwise correct, add a closing parentheses

"Steve" help_available_at_very_reasonable_rates@contactme .com wrote in
message m...
Assuming your InputTbl table has a field named "Status", do the following:

Add a text box to your main form and label it Total Absent. Put the
following in the Control Source property of the text box:

=DCount("[Status]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND
[Status] = 'Status'"

Note: That is a single quote and double quote at the end.

Do the same for the other statuses.

Steve



"bigmac" wrote in message
...
i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab
query
and use forms![main form]![id] in the id criteria box and then in the
am
criteria box of the am field i put "absent" and count in the next totals
row
then this will show the total amount of "absents" in the result, but
how do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in
the
Help file and see if you can get it to work. If you have trouble, post
back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the
input
tbl), on the sub form there is [am] and [pm] fields, both lookup and
can
contain one of the following, "absent","present","placement",
holiday"or
bank holiday. what i am trying to do is on the main form show the
total
for
each , total absent, total holiday,etc, but only for the id shown on
the
form
example id1 shows all absents,holidays,placements etc for that
particular
id, also last bit, can this be done so that it automaticaly updates
after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing
me.
regards mac
learner ,please be patient

hi steve the only fields i have in my inputtbl are, id,am,and pm so what do i
use instead of status? also am i right in thinking that this expression will
return the total of "absent" in the am field and place the total on my main
form?
--
learner ,please be patient


"Steve" wrote:

Assuming your InputTbl table has a field named "Status", do the following:

Add a text box to your main form and label it Total Absent. Put the
following in the Control Source property of the text box:

=DCount("[Status]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND
[Status] = 'Status'"

Note: That is a single quote and double quote at the end.

Do the same for the other statuses.

Steve



"bigmac" wrote in message
...
i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab
query
and use forms![main form]![id] in the id criteria box and then in the
am
criteria box of the am field i put "absent" and count in the next totals
row
then this will show the total amount of "absents" in the result, but how
do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in
the
Help file and see if you can get it to work. If you have trouble, post
back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and
can
contain one of the following, "absent","present","placement",
holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on
the
form
example id1 shows all absents,holidays,placements etc for that
particular
id, also last bit, can this be done so that it automaticaly updates
after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing
me.
regards mac
learner ,please be patient

First, I made a mistake at the end of the expression. [Status] = 'Status'"
should have been [Status] = 'Absent'"

Now, Bruce is correct about the closing parantheses. The above should have
been:
[Status] = 'Absent'")

Finally, with your clarificfation, put the following in the Control Source
of the textbox:
=DCount("[AM]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND [AM] =
'Absent'") +
DCount("[PM]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND [PM] =
'Absent'")

To answer your question, the above will total "Absent" for both AM and PM.
If you only want AM or PM, omit the apprpriate part in the expression above.

Steve

"bigmac" wrote in message
news
hi steve the only fields i have in my inputtbl are, id,am,and pm so what
do i
use instead of status? also am i right in thinking that this expression
will
return the total of "absent" in the am field and place the total on my
main
form?
--
learner ,please be patient


"Steve" wrote:

Assuming your InputTbl table has a field named "Status", do the
following:

Add a text box to your main form and label it Total Absent. Put the
following in the Control Source property of the text box:

=DCount("[Status]","InputTbl","[ID] = " & Forms![MainForm]![ID] & " AND
[Status] = 'Status'"

Note: That is a single quote and double quote at the end.

Do the same for the other statuses.

Steve



"bigmac" wrote in message
...
i have tried the dcount and cannot get it to work (my one brain cell
still
sleeping),but what i have managed to learn is that if i make a crosstab
query
and use forms![main form]![id] in the id criteria box and then in the
am
criteria box of the am field i put "absent" and count in the next
totals
row
then this will show the total amount of "absents" in the result, but
how
do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount
in
the
Help file and see if you can get it to work. If you have trouble, post
back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the
input
tbl), on the sub form there is [am] and [pm] fields, both lookup and
can
contain one of the following, "absent","present","placement",
holiday"or
bank holiday. what i am trying to do is on the main form show the
total
for
each , total absent, total holiday,etc, but only for the id shown on
the
form
example id1 shows all absents,holidays,placements etc for that
particular
id, also last bit, can this be done so that it automaticaly updates
after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing
me.
regards mac
learner ,please be patient

chers steve worked well thanks
--
learner ,please be patient


"bigmac" wrote:

i have tried the dcount and cannot get it to work (my one brain cell still
sleeping),but what i have managed to learn is that if i make a crosstab query
and use forms![main form]![id] in the id criteria box and then in the am
criteria box of the am field i put "absent" and count in the next totals row
then this will show the total amount of "absents" in the result, but how do
i get the totals onto my main form?
--
learner ,please be patient


"Steve" wrote:

Access has a DCount function that will do this for you. Lookup DCount in the
Help file and see if you can get it to work. If you have trouble, post back.

Steve




"bigmac" wrote in message
...

hello i have two tables[mastertbl] and [inputtbl] ,both joined in a
relationship by [id]fields,(one is a primarykey).
and two forms[main form] and a subform[mainsubform](made from the input
tbl), on the sub form there is [am] and [pm] fields, both lookup and can
contain one of the following, "absent","present","placement", holiday"or
bank holiday. what i am trying to do is on the main form show the total
for
each , total absent, total holiday,etc, but only for the id shown on the
form
example id1 shows all absents,holidays,placements etc for that particular
id, also last bit, can this be done so that it automaticaly updates after
each new field input?
i do not know any vba , and as for epressions i am just learning so
please
can you make them clear for me to try and learn what you are showing me.
regards mac
learner ,please be patient