counting and placing answer in text box

[bigmac]

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

 

[bigmac]

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

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
(e-mail address removed)

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

 

[bigmac]

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

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
(e-mail address removed)

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

 

[bigmac]

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

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
(e-mail address removed)

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

 

[BruceM]

Assuming the expression is otherwise correct, add a closing parentheses

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
(e-mail address removed)

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

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
(e-mail address removed)




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

 

[bigmac]

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

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
(e-mail address removed)

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

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
(e-mail address removed)




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

 

[bigmac]

chers steve worked well thanks
--
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

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
(e-mail address removed)

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