Count ID from one table that matches another and show in a form...

[DH010010]

i have two tables one "job details" and another "additional jobs". the "job
details" table holds all the main information customers name and what job is
to be carried out ect. if they would like additional jobs they are stored in
the "additional Jobs" table linked to "job details". now in the form that
enters and displays this information i have an unbound box which i would
like to display the total number of jobs to be complete for that customer.

anyhelp on this would be great.

 

[Ken Sheridan]

Set the ControlSource property of the text box to:

=DCount("*","[Additional Jobs]","[JobID] = " & [JobID])

Where JobID is the primary key of the Job Details table and the
corresponding foreign key column in the Additional Jobs table. If JobID is
text rather than a number wrap the value in quotes characters like so:

=DCount("*","[Additional Jobs]","[JobID] = """ & [JobID] & """")

If you want to include the main job in the count as well as the additional
jobs then add 1:

=DCount("*","[Additional Jobs]","[JobID] = " & [JobID])+1

You could also, if you wish, include a continuous or datasheet view subform
based on the Additional Jobs in the main form provided this is in single form
view, linked on JobID. That way you'd see the additional jobs for the
current job listed, and be able to add, delete or edit additional jobs all
through the one form/subform.

Ken Sheridan
Stafford, England

 

[DH010010]

Thank you thank you thank you. I sat for 2 hours trying to do what you done
in 2 mins.

Set the ControlSource property of the text box to:

=DCount("*","[Additional Jobs]","[JobID] = " & [JobID])

Where JobID is the primary key of the Job Details table and the
corresponding foreign key column in the Additional Jobs table. If JobID is
text rather than a number wrap the value in quotes characters like so:

=DCount("*","[Additional Jobs]","[JobID] = """ & [JobID] & """")

If you want to include the main job in the count as well as the additional
jobs then add 1:

=DCount("*","[Additional Jobs]","[JobID] = " & [JobID])+1

You could also, if you wish, include a continuous or datasheet view subform
based on the Additional Jobs in the main form provided this is in single form
view, linked on JobID. That way you'd see the additional jobs for the
current job listed, and be able to add, delete or edit additional jobs all
through the one form/subform.

Ken Sheridan
Stafford, England

i have two tables one "job details" and another "additional jobs". the "job
details" table holds all the main information customers name and what job is
to be carried out ect. if they would like additional jobs they are stored in
the "additional Jobs" table linked to "job details". now in the form that
enters and displays this information i have an unbound box which i would
like to display the total number of jobs to be complete for that customer.

anyhelp on this would be great.