Calculations and Forms

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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
Click to expand...
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
Click to expand...
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.
 
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
 
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
Click to expand...
written an equivalent query to calculate the totals.
My problem is rather that I want the form which displays
Click to expand...
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
Click to expand...
bit new to Access - though I've used lots of other DBMSs
 
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
 
Back
Top