Count error

  • Thread starter Thread starter RBDU
  • Start date Start date
R

RBDU

Hi All! Thanking anyone for a reply. Access97

I have a job sheet form where you can add up to six operator names in the
six fields available.

Another field [total operators] you input the total number of operators that
attended a job. Instead of inputting the number of operators that attended I
tried putting =count([operator 1] + [operator 2]) in the field [total
operators], but this adds up all the operator names in the entire database.

I only want to do it for that particular job. That is if I have 2 operators
that attended a job, [total operators] will equal 2, not 2691 for the whole
database.

Regards
Peter
 
Your table really needs to be redesigned.

One job can have many operators. Keep the info about the job in one table,
and each operator in a separate record in a related table.

Operator table (one record for each operator):
OperatorID AutoNum primary key
OperatorName Text
...

Job table (One record per job):
JobID AutoNum primary key
...

JobOperator table:
JobID Number relates to Job.JobID
OperatorID Number relates to Operator.OperatorID

You then create a main form bound to the Job table, with a subform bound to
the JobOperator table. The continuous subform has a combo, where you can
select one operator on each line, for as many rows as you have operators on
that job.

The count of operators in job 99 is then:
=DCount("*", "JobOperator", "JobID = 99")
 
I have taken that on board, thanking you very much.
Except I need to display the number of operators on the job form at the time
of data entry. (We never have any more than 6 operators).

Is there still a way to count the six text fields if all have data in them
and not the entire lot in the database.

Peter.


Allen Browne said:
Your table really needs to be redesigned.

One job can have many operators. Keep the info about the job in one table,
and each operator in a separate record in a related table.

Operator table (one record for each operator):
OperatorID AutoNum primary key
OperatorName Text
...

Job table (One record per job):
JobID AutoNum primary key
...

JobOperator table:
JobID Number relates to Job.JobID
OperatorID Number relates to Operator.OperatorID

You then create a main form bound to the Job table, with a subform bound to
the JobOperator table. The continuous subform has a combo, where you can
select one operator on each line, for as many rows as you have operators on
that job.

The count of operators in job 99 is then:
=DCount("*", "JobOperator", "JobID = 99")

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

Reply to group, rather than allenbrowne at mvps dot org.
RBDU said:
Hi All! Thanking anyone for a reply. Access97

I have a job sheet form where you can add up to six operator names in the
six fields available.

Another field [total operators] you input the total number of operators
that
attended a job. Instead of inputting the number of operators that attended
I
tried putting =count([operator 1] + [operator 2]) in the field [total
operators], but this adds up all the operator names in the entire
database.

I only want to do it for that particular job. That is if I have 2
operators
that attended a job, [total operators] will equal 2, not 2691 for the
whole
database.

Regards
Peter
Click to expand...
Click to expand...
 
So then, in the subform you would place a text box in the Form Footer
section, and set its Control Source to:
=Count("*")

If you want to do it with your existing structure, you could use a text box
with this Control Source:
=6 - IsNull([Operator1]) - IsNull([Operator2]) - IsNull(...
That will solve your immediate issue, but leave you with many others because
of the non-normalized structure.

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

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

RBDU said:
I have taken that on board, thanking you very much.
Except I need to display the number of operators on the job form at the
time
of data entry. (We never have any more than 6 operators).

Is there still a way to count the six text fields if all have data in them
and not the entire lot in the database.

Peter.


Allen Browne said:
Your table really needs to be redesigned.

One job can have many operators. Keep the info about the job in one
table,
and each operator in a separate record in a related table.

Operator table (one record for each operator):
OperatorID AutoNum primary key
OperatorName Text
...

Job table (One record per job):
JobID AutoNum primary key
...

JobOperator table:
JobID Number relates to Job.JobID
OperatorID Number relates to Operator.OperatorID

You then create a main form bound to the Job table, with a subform bound to
the JobOperator table. The continuous subform has a combo, where you can
select one operator on each line, for as many rows as you have operators on
that job.

The count of operators in job 99 is then:
=DCount("*", "JobOperator", "JobID = 99")

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

Reply to group, rather than allenbrowne at mvps dot org.
RBDU said:
Hi All! Thanking anyone for a reply. Access97

I have a job sheet form where you can add up to six operator names in the
six fields available.

Another field [total operators] you input the total number of operators
that
attended a job. Instead of inputting the number of operators that attended
I
tried putting =count([operator 1] + [operator 2]) in the field [total
operators], but this adds up all the operator names in the entire
database.

I only want to do it for that particular job. That is if I have 2
operators
that attended a job, [total operators] will equal 2, not 2691 for the
whole
database.

Regards
Peter
Click to expand...
Click to expand...
Click to expand...
 
Back
Top