Count entries

[Greta]

Access 2002...
Can I put a text box onto a form and have it display the
number of times the word "Failed" is entered on the form?
Thanks,
Greta

 

[Gary Miller]

Greta,

You sure can, but I am not clear if you are trying to total
these for all of the records on the form that are in one
control or if you are trying to total a bunch of controls
that are on one record. Could you please elaborate?

Gary Miller

 

[Greta]

I'm not very good at explaining these things, but here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of equipment
that is filled out by data entry. They enter "Pass" in
the left column or "Fail" in the center column, the far
right column is for comments...why it failed.
Each form creates a record in the table related to it.
I want to count how many of these tests are "Fail" on the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are on
one record.
Thanks so VERY much for helping me!
Greta

 

[Gary Miller]

Greta,

With this setup it should be a breeze, although in hindsight
it may have been better design to have one "Result" field
that would be set to either Pass or Fail for reporting ease.

Put an unbound textbox control in the Form Footer of this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and using
the name that you have for that field. Do the same for the
middle column with it's name.

If you aren't too far down the road with designing this DB,
you may want to consider just using one field. You could
then display the totals in a slightly different manner.

Gary Miller

 

[Greta]

I know what you mean...I wanted to have one column, but
the users' boss wants two.
I'm actually counting about 12 fields. Each field has
it's own name.
Can I do that?
Thanks,
Greta

-----Original Message-----
Greta,

With this setup it should be a breeze, although in hindsight
it may have been better design to have one "Result" field
that would be set to either Pass or Fail for reporting ease.

Put an unbound textbox control in the Form Footer of this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and using
the name that you have for that field. Do the same for the
middle column with it's name.

If you aren't too far down the road with designing this DB,
you may want to consider just using one field. You could
then display the totals in a slightly different manner.

Gary Miller

I'm not very good at explaining these things, but here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of equipment
that is filled out by data entry. They enter "Pass" in
the left column or "Fail" in the center column, the far
right column is for comments...why it failed.
Each form creates a record in the table related to it.
I want to count how many of these tests are "Fail" on the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are on
one record.
Thanks so VERY much for helping me!
Greta

.

 

[Gary Miller]

Not sure as now I am a little confused by your "12 fields".
Is this 12 columns of data accross or are you saying that
you have 12 items down and 3 across for every record? The
first should work as I explained it as long as you have room
in your footer, the second will make things very
interesting.

BTW, I should have pointed out that the Count() method will
work correctly if the entry is as you originally
said...Passed or no entry, Failed or no entry. Count treats
a blank entry as nothing so would correctly just add up the
Passed entries ignoring the blanks. If anything else gets
entered, it would count that as well and you would need a
more sophisticated method.

Gary Miller

I know what you mean...I wanted to have one column, but
the users' boss wants two.
I'm actually counting about 12 fields. Each field has
it's own name.
Can I do that?
Thanks,
Greta

-----Original Message-----
Greta,

With this setup it should be a breeze, although in hindsight
it may have been better design to have one "Result" field
that would be set to either Pass or Fail for reporting ease.

Put an unbound textbox control in the Form Footer of this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and using
the name that you have for that field. Do the same for the
middle column with it's name.

If you aren't too far down the road with designing this DB,
you may want to consider just using one field. You could
then display the totals in a slightly different manner.

Gary Miller

I'm not very good at explaining these things, but here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of equipment
that is filled out by data entry. They enter "Pass" in
the left column or "Fail" in the center column, the far
right column is for comments...why it failed.
Each form creates a record in the table related to it.
I want to count how many of these tests are "Fail" on the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are on
one record.
Thanks so VERY much for helping me!
Greta
-----Original Message-----
Greta,

You sure can, but I am not clear if you are trying to
total
these for all of the records on the form that are in one
control or if you are trying to total a bunch of controls
that are on one record. Could you please elaborate?

Gary Miller

Access 2002...
Can I put a text box onto a form and have it display the
number of times the word "Failed" is entered on the
form?
Thanks,
Greta


.

.

 

[Greta]

Yep, it's 12 items down and 3 across for every record.
I did like you suggested, and it worked, sort of...It's
counting the "Fail"s, but it's counting them for ALL the
records. Record if record 1 has 3 "Fail"s, and recrod 2
has 1 "Fail" it is telling me, at the bottom of each
record, that the total is 4...instead of 3 for record 1
and 1 for record 2.
Getting tired of this yet?
Greta

-----Original Message-----
Not sure as now I am a little confused by your "12 fields".
Is this 12 columns of data accross or are you saying that
you have 12 items down and 3 across for every record? The
first should work as I explained it as long as you have room
in your footer, the second will make things very
interesting.

BTW, I should have pointed out that the Count() method will
work correctly if the entry is as you originally
said...Passed or no entry, Failed or no entry. Count treats
a blank entry as nothing so would correctly just add up the
Passed entries ignoring the blanks. If anything else gets
entered, it would count that as well and you would need a
more sophisticated method.

Gary Miller

I know what you mean...I wanted to have one column, but
the users' boss wants two.
I'm actually counting about 12 fields. Each field has
it's own name.
Can I do that?
Thanks,
Greta

-----Original Message-----
Greta,

With this setup it should be a breeze, although in hindsight
it may have been better design to have one "Result" field
that would be set to either Pass or Fail for reporting ease.

Put an unbound textbox control in the Form Footer of this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and using
the name that you have for that field. Do the same for the
middle column with it's name.

If you aren't too far down the road with designing this DB,
you may want to consider just using one field. You could
then display the totals in a slightly different manner.

Gary Miller

I'm not very good at explaining these things, but here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of equipment
that is filled out by data entry. They enter "Pass" in
the left column or "Fail" in the center column, the far
right column is for comments...why it failed.
Each form creates a record in the table related to it.
I want to count how many of these tests are "Fail" on the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are on
one record.
Thanks so VERY much for helping me!
Greta
-----Original Message-----
Greta,

You sure can, but I am not clear if you are trying to
total
these for all of the records on the form that are in one
control or if you are trying to total a bunch of controls
that are on one record. Could you please elaborate?

Gary Miller

Access 2002...
Can I put a text box onto a form and have it display
the
number of times the word "Failed" is entered on the
form?
Thanks,
Greta


.



.

.

 

[Gary Miller]

Getting tired of this yet?

Still with you, but you may not like the solution, certainly
I don't <grin>. I have some questions about the design
structure and wonder if there may have been a better
approach somehow dividing the test items and results into
their own table using twelve records for each item, but not
knowing enough about the program and what you folks have
going on I will just work with what you have.

If I am understanding correctly now, what you really want to
do is add up how many Passes and how many Fails for the 12
items in the test procedure for that item. This would be the
totals for that record only, correct?

If you could talk your boss into going with a Pass/Fail
check box instead of the two separate fields you could use
an approach of adding up the values in the checkboxes if
they are stored as True(-1) and False(0) and multiplying
by -1....

=P1+P2+P3, etc...

I would work very hard on that one as you could put that
formula into a couple of textboxes at the bottom of the
form.

As this is text it is impossible to just add it up and you
need to convert whether or not something is in there to a
number. You could try putting the following into the
ControlSource of 2 textboxes. Be careful, though. A
ControlSource of a control as it only holds 255 characters.

=IIf(P1="Fail,1,0)+IIf(P2="Fail,1,0)+IIf(P3="Fail,1,0)+etc..
..

Another approach is to create a new query that will have 24
new fields. For each of these Pass and Fail fields you will
need to make a calculated new field in which you would type
a variant of this in for each...

P1C: IIf(P1="Pass",1,0)
F1C: IIf(F1="Fail",1,0)
P2C: IIf(....

This will give you new fields that are a 1 or 0 value. I
kept the names short because of the 255 limit that I talked
about. Now you would put your pass and fail unbound control
boxes in a new subform at the bottom of the detail form and
base that subform on your new query. Of course this new
query would need to contain the TestID or whatever is the ID
of the record you are working on to the the subform
maste/child relationship.

Now in the new subform, which will only be one record, you
would set the record source of the Pass and Fail boxes to
be...

=P1C+P2C+P3C+....

Told you that you wouldn't like it. I can think of some much
more elegant ways in code by setting tag properties of
controls and doing a loop through the controls testing for
the tags and adding things that way, but I don't know how
strong your VBA is.

I shudder to think of what you will need to go through when
you need to change your test items or add a couple of more.
Are the tests the same for all items? If not, you are dead
in the water for this and most of your reporting.

Gary Miller

Yep, it's 12 items down and 3 across for every record.
I did like you suggested, and it worked, sort of...It's
counting the "Fail"s, but it's counting them for ALL the
records. Record if record 1 has 3 "Fail"s, and recrod 2
has 1 "Fail" it is telling me, at the bottom of each
record, that the total is 4...instead of 3 for record 1
and 1 for record 2.
Getting tired of this yet?
Greta

-----Original Message-----
Not sure as now I am a little confused by your "12 fields".
Is this 12 columns of data accross or are you saying that
you have 12 items down and 3 across for every record? The
first should work as I explained it as long as you have room
in your footer, the second will make things very
interesting.

BTW, I should have pointed out that the Count() method will
work correctly if the entry is as you originally
said...Passed or no entry, Failed or no entry. Count treats
a blank entry as nothing so would correctly just add up the
Passed entries ignoring the blanks. If anything else gets
entered, it would count that as well and you would need a
more sophisticated method.

Gary Miller

I know what you mean...I wanted to have one column, but
the users' boss wants two.
I'm actually counting about 12 fields. Each field has
it's own name.
Can I do that?
Thanks,
Greta
-----Original Message-----
Greta,

With this setup it should be a breeze, although in
hindsight
it may have been better design to have one "Result" field
that would be set to either Pass or Fail for reporting
ease.

Put an unbound textbox control in the Form Footer of this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and
using
the name that you have for that field. Do the same for the
middle column with it's name.

If you aren't too far down the road with designing this
DB,
you may want to consider just using one field. You could
then display the totals in a slightly different manner.

Gary Miller

I'm not very good at explaining these things, but here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of
equipment
that is filled out by data entry. They enter "Pass" in
the left column or "Fail" in the center column, the far
right column is for comments...why it failed.
Each form creates a record in the table related to it.
I want to count how many of these tests are "Fail" on
the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are on
one record.
Thanks so VERY much for helping me!
Greta
-----Original Message-----
Greta,

You sure can, but I am not clear if you are trying to
total
these for all of the records on the form that are in
one
control or if you are trying to total a bunch of
controls
that are on one record. Could you please elaborate?

Gary Miller

Access 2002...
Can I put a text box onto a form and have it display
the
number of times the word "Failed" is entered on the
form?
Thanks,
Greta


.



.

.

 

[Gary Miller]

Greta,

....And it's not going to work the way you have it setup if
every item has a different number of and different names of
tests. Period.

I strongly recommend a full redesign of how this is being
approached. I don't think that you have any alternative.

Off the top of my head I would picture a table for whatever
it is that is being tested, another that holds all possible
tests, another to hold groups of tests. This last one would
say that group one has test 1, 6, 8 and13, group 2 has test
1, 3, 4 and 8 ,etc... Next you assign each tested item to
the appropriate group of tests. You would then probably have
one more that holds test results for each tested item.

Now for each item being tested you assign it to the proper
group and the db can pull in the proper test items, probably
in a subform of some type.

It is an interesting scenario that you have. I would suggest
posting back to the group what the business model is of what
you are trying to do with details and reporting needs and
ask for suggestions of how best to structure this. I think
that you will get some good workable ideas that will save
you a TON of work in the long run. There is also another
group here, microsoft.public.access.tablesdbdesign, that
would be a good one to post it too as well.

Good Luck and I will try to keep an eye open to the
responses you get. I will be curious to see the details of
what you need to do as well.

Gary

Wow...this is going to take a while, huh?
I have 96 procedures that each have from 12 to 31 tests in
their group.
Wish me luck, and thanks so much for taking the time to
figure this out for me...I really appreciate it.
Greta

-----Original Message-----

Getting tired of this yet?

Still with you, but you may not like the solution, certainly
I don't <grin>. I have some questions about the design
structure and wonder if there may have been a better
approach somehow dividing the test items and results into
their own table using twelve records for each item, but not
knowing enough about the program and what you folks have
going on I will just work with what you have.

If I am understanding correctly now, what you really want to
do is add up how many Passes and how many Fails for the 12
items in the test procedure for that item. This would be the
totals for that record only, correct?

If you could talk your boss into going with a Pass/Fail
check box instead of the two separate fields you could use
an approach of adding up the values in the checkboxes if
they are stored as True(-1) and False(0) and multiplying
by -1....

=P1+P2+P3, etc...

I would work very hard on that one as you could put that
formula into a couple of textboxes at the bottom of the
form.

As this is text it is impossible to just add it up and you
need to convert whether or not something is in there to a
number. You could try putting the following into the
ControlSource of 2 textboxes. Be careful, though. A
ControlSource of a control as it only holds 255 characters.

=IIf(P1="Fail,1,0)+IIf(P2="Fail,1,0)+IIf(P3="Fail,1,0)

+etc..
..

Another approach is to create a new query that will have 24
new fields. For each of these Pass and Fail fields you will
need to make a calculated new field in which you would type
a variant of this in for each...

P1C: IIf(P1="Pass",1,0)
F1C: IIf(F1="Fail",1,0)
P2C: IIf(....

This will give you new fields that are a 1 or 0 value. I
kept the names short because of the 255 limit that I talked
about. Now you would put your pass and fail unbound control
boxes in a new subform at the bottom of the detail form and
base that subform on your new query. Of course this new
query would need to contain the TestID or whatever is the ID
of the record you are working on to the the subform
maste/child relationship.

Now in the new subform, which will only be one record, you
would set the record source of the Pass and Fail boxes to
be...

=P1C+P2C+P3C+....

Told you that you wouldn't like it. I can think of some much
more elegant ways in code by setting tag properties of
controls and doing a loop through the controls testing for
the tags and adding things that way, but I don't know how
strong your VBA is.

I shudder to think of what you will need to go through when
you need to change your test items or add a couple of more.
Are the tests the same for all items? If not, you are dead
in the water for this and most of your reporting.

Gary Miller

Yep, it's 12 items down and 3 across for every record.
I did like you suggested, and it worked, sort of...It's
counting the "Fail"s, but it's counting them for ALL the
records. Record if record 1 has 3 "Fail"s, and recrod 2
has 1 "Fail" it is telling me, at the bottom of each
record, that the total is 4...instead of 3 for record 1
and 1 for record 2.
Getting tired of this yet?
Greta
-----Original Message-----
Not sure as now I am a little confused by your "12
fields".
Is this 12 columns of data accross or are you saying that
you have 12 items down and 3 across for every record? The
first should work as I explained it as long as you have
room
in your footer, the second will make things very
interesting.

BTW, I should have pointed out that the Count() method
will
work correctly if the entry is as you originally
said...Passed or no entry, Failed or no entry. Count
treats
a blank entry as nothing so would correctly just add up
the
Passed entries ignoring the blanks. If anything else gets
entered, it would count that as well and you would need a
more sophisticated method.

Gary Miller


I know what you mean...I wanted to have one column, but
the users' boss wants two.
I'm actually counting about 12 fields. Each field has
it's own name.
Can I do that?
Thanks,
Greta
-----Original Message-----
Greta,

With this setup it should be a breeze, although in
hindsight
it may have been better design to have one "Result"
field
that would be set to either Pass or Fail for reporting
ease.

Put an unbound textbox control in the Form Footer of
this
form. Now set the ControlSource to be
"Count([LeftColumnFieldName])" without the quotes and
using
the name that you have for that field. Do the same for
the
middle column with it's name.

If you aren't too far down the road with designing this
DB,
you may want to consider just using one field. You
could
then display the totals in a slightly different manner.

Gary Miller

I'm not very good at explaining these things, but
here
goes:
The deal is, I have a form for "Pass" or "Fail" for
several tests performed on a particular piece of
equipment
that is filled out by data entry. They enter "Pass"
in
the left column or "Fail" in the center column, the
far
right column is for comments...why it failed.
Each form creates a record in the table related to
it.
I want to count how many of these tests are

"Fail"
on

the
form...like at the bottom of the center column.
So, I'm trying to total a bunch of controls that are
on
one record.
Thanks so VERY much for helping me!
Greta
-----Original Message-----
Greta,

You sure can, but I am not clear if you are trying
to
total
these for all of the records on the form that

are
in

one
control or if you are trying to total a bunch of
controls
that are on one record. Could you please elaborate?

Gary Miller

Access 2002...
Can I put a text box onto a form and have it
display
the
number of times the word "Failed" is entered on
the
form?
Thanks,
Greta


.



.



.

.