Calculations and Forms

[Guest]

I have a table which has 3 fields - date, hours and job_id. This I call, with stunning originality "hours" I use it to record the number of hours I have worked on a job in a given day
I have a table, called job, which uses job_id as its primary key.

My problem is that I want a form to display the sum of the hours worked for a given job. But I can't work out how to tell Access that I want it to give me the total for this particular job. I have written a query which will give me the totals of hours worked for all the jobs, but I can't think of a way to get it to tell me specifically the hours for the present job I'm looking at.

Any ideas anyone?

 

[tina]

if you want to calculate the number of hours per job using
a query, then a Totals query will do it. open a query in
design view, add the "hours" table, drag the job_id and
hours fields into the grid, on the toolbar click the
Totals button (funny looking capital E). set job_id to
GroupBy and hours to Sum (i'm assuming that your hours
field in the table is a number data type.)

here's an example of the SQL statement, using a table i
built with similar table/field names:

SELECT tblHours.H_JobID, Sum(tblHours.HHours) AS
SumOfHHours
FROM tblHours
GROUP BY tblHours.H_JobID;

and by the way, i hope you didn't name the date field in
the table as "Date". probably not a good idea; in my
sample table i named the field HDate.

hth

-----Original Message-----
I have a table which has 3 fields - date, hours and

job_id. This I call, with stunning originality "hours" I
use it to record the number of hours I have worked on a
job in a given day

I have a table, called job, which uses job_id as its primary key.

My problem is that I want a form to display the sum of

the hours worked for a given job. But I can't work out how
to tell Access that I want it to give me the total for
this particular job. I have written a query which will
give me the totals of hours worked for all the jobs, but I
can't think of a way to get it to tell me specifically the
hours for the present job I'm looking at.

 

[Guest]

I'm OK with the sql side of it - i've successfully written an equivalent query to calculate the totals.

My problem is rather that I want the form which displays the job to show me the number of hours worked on that particular job. The query will show me the hours worked on every job - which works OK in a report, but not the form.

Or am I missing something obvious here? Probably. I'm a bit new to Access - though I've used lots of other DBMSs

 

[tina]

well, when all else fails, you can always add an unbound
text box to the form and set the ControlSource to a DSum()
function, as

=DSum("HHours","Hours","H_JobID = " & Forms!FormName!
H_JobIDControlName)

i just used the same table and field names from my SQL
example. if the jobid field is text, change the syntax to

=DSum("HHours","Hours","H_JobID = '" & Forms!FormName!
H_JobIDControlName & "'")

hth

-----Original Message-----
I'm OK with the sql side of it - i've successfully

written an equivalent query to calculate the totals.

My problem is rather that I want the form which displays

the job to show me the number of hours worked on that
particular job. The query will show me the hours worked on
every job - which works OK in a report, but not the form.

Or am I missing something obvious here? Probably. I'm a

bit new to Access - though I've used lots of other DBMSs

 

[Guest]

Thanks for this Tina. I'm sorry to be dim... I've followed your example, and this is what I've come up with
=DSum(hours!hours_worked,hours,hours!job_id=[Job_id]

I'm still getting the "#Name?" error though

I'm a bit confused about what the "domain" argument (i.e. when DSum takes 3 arguments, the 2nd is "domain") means. I assumed it meant the table name? I had to type it in by hand, because there didn't seem to be a clickable thing for table names - which makes me suspect I've gotten hold of the wrong end of the stick

The jobid field is a number, as all good primary keys should be

Thanks again