Re: query error

Again, I misunderstood. I saw the values you listed and assumed that was
your table, not the query's output.

It sounds like you've found a workable solution (DateDiff() function).

Regards

Jeff Boyce
Microsoft Office/Access MVP

"PHisaw" <PHisaw@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:EC8726BC-1AF6-488D-9633-B7C3DF5F1054@xxxxxxxxxxxxxxxx
Jeff,

Thanks again for replying. I believe I have figured out the problem and
have answered your questions, as well as asked some I'm hoping you will
take
time to explain, below.

Pam




"Jeff Boyce" wrote:

Pam

I didn't understand that the two queries give different answers for this
one
JobNumber.

It's not that the two queries give different answers. First query pulls
info from tables and lists multiple start and stop time entries per each
job.
The second query groups them by job number for one total time on job.


If you have a query that IS working, can you use it to solve your current
issue?

I have figured out the problem. The report used the DateDiff function to
calculation elapsed time between start and stop times. The query used the
calculations "Total Task: Sum([StartTime]-[StopTime])" and then on the
second
query "SumTotalTask". When I changed the query calculation to DateDiff to
total time, it calculated properly.

As for your SQL, perhaps I don't understand the underlying data, ... but
I'm
wondering if the job number doesn't have unique Rotor and Stator part
numbers already associated with it. I'm seeing that you are grouping by
these two fields, but wonder why?

If I leave off the two part numbers in the grouping clause, I get the
message "You tried to exceute a query that does not include the specified
expression 'RotorPartNumber' as part of an aggregate function." I didn't
see
where it affected the results, so I left it in there.

Will you please explain how I could have handled this differently, maybe
more efficiently?

What happens if you only return the sum of the task time per job number?

By the way, your table holds redundant information ... the TaskTime.
Since
you've already recorded start and stop times, the TaskTime value could be
calculated from these. No need to store it, too. And that TaskTime
field,
is it a text field, a numeric field, or (hopefully not) a Date/Time
field.
The Access Date/Time data type is intended to show a point-in-time, not a
duration.

I'm not sure what you mean by table holds redundant information. I don't
have the TaskTime stored in a table, it is only in the query. Please
explain.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"PHisaw" <PHisaw@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E2B30A85-69A8-4BD8-9482-5BD052CC8F13@xxxxxxxxxxxxxxxx
Jeff,

Thank you for your reply. I thought maybe you would see something in
the
code that could possibly cause a problem. I will list below the
records
for
the particular job that is causing problems (there may be others, but
this
one stands out) If I pull the records thru the query and then do the
calculations on the report, it totals correctly. If I do the
calculations
in
the second query and then use this on the subform, it is incorrect.

All records are filtered for JobNumber 7061
RecordNumber StartTime StopTime TotalTask
478 6/6/2006 12:34:37 PM 6/6/2006 2:23:30 PM 1:48
479 6/8/2006 8:28:02 AM 6/8/2006 11:34:38 AM 3:06
504 6/22/2006 6:01:08 AM 6/22/2006 6:54:54 AM 0:53
505 6/22/2006 6:01:53 AM 6/22/2006 6:12:48 AM 0:10
507 6/22/2006 7:24:31 AM 6/22/2006 11:29:57 AM 4:05
508 6/22/2006 12:36:48 PM 6/22/2006 1:55:28 PM 1:18
510 6/26/2006 7:30:11 AM 6/26/2006 11:31:23 AM 4:01
511 6/26/2006 1:27:34 PM 6/26/2006 4:34:46 PM 3:07
512 6/27/2006 7:29:31 AM 6/27/2006 11:30:26 AM 4:00
513 6/27/2006 12:28:51 PM 6/27/2006 4:35:09 PM 4:06
515 6/28/2006 8:13:49 AM 6/28/2006 9:05:16 AM 0:51
547 7/24/2006 6:10:28 AM 7/24/2006 9:12:13 AM 3:01

JobNumber RotorPartNumber StatorPartNumber SumTask
7061 R004CU06D1R3210 ST10CU01D1R31A1 6:33

Thanks again for your help.
Pam

"Jeff Boyce" wrote:

Pam

We can't see your data, nor do we know what is happening that you
consider
not "calculating properly". Is there anything different about any of
the
data involved in this one "job number" or the records?

More specific descriptions of the data and what is wrong about it
could
help
us come up with more specific suggestions...

Regards

Jeff Boyce
Microsoft Office/Access MVP

"PHisaw" <PHisaw@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DD7E10AE-13AF-4E3D-9341-EDC12DD0935A@xxxxxxxxxxxxxxxx
Hi,

I have two queries that list and calculate time for a subform.
Listed
below
is the SQL view of each:
First query:

SELECT tWorkLog.JobNumber, tGeneralInfo.RotorPartNumber,
tGeneralInfo.StatorPartNumber, tWorkLog.Tech,
tLookupWorkCodes.WorkCode,
tWorkLog.StartTime, tWorkLog.StopTime, tWorkLog.Comments,
Sum(([StartTime]-[StopTime])) AS TotalTask
FROM (tWorkLog LEFT JOIN tLookupWorkCodes ON tWorkLog.WorkCode =
tLookupWorkCodes.WorkCodeID) INNER JOIN tGeneralInfo ON
tWorkLog.JobNumber
=
tGeneralInfo.JobNumber
GROUP BY tWorkLog.JobNumber, tGeneralInfo.RotorPartNumber,
tGeneralInfo.StatorPartNumber, tWorkLog.Tech,
tLookupWorkCodes.WorkCode,
tWorkLog.StartTime, tWorkLog.StopTime, tWorkLog.Comments;

Second query:

SELECT qWorkLog.JobNumber, qWorkLog.RotorPartNumber,
qWorkLog.StatorPartNumber, Sum([qWorkLog.TotalTask]) AS SumTask
FROM qWorkLog
GROUP BY qWorkLog.JobNumber, qWorkLog.RotorPartNumber,
qWorkLog.StatorPartNumber
HAVING
(((qWorkLog.RotorPartNumber)=[Forms]![fManufacturing]![RotorPartNumber]))
OR
(((qWorkLog.StatorPartNumber)=[Forms]![fManufacturing]![StatorPartNumber]));

After checking several records, everything seems to be working as it
should
EXCEPT, I have a specific job number that is not calculating
properly.
I
have calculated time, searched all records, rewrote criteria in
query
and
it
still totals wrong.
Even though there are only 12 records that are included in this
particular
job, I'm afraid they would be too distorted to list them properly.

I'm hoping someone can point me in the right direction with
information
provided as to why this may not be calculating as it should for this
one
job.


Any help is greatly appreciated!
Thanks in advance,
Pam








.

Relevant Pages

  • Re: conditional formatting in form slows down calculations
    ... table to see all dates and a filtered query to see recent dates. ... endless calculating when the form opens. ... Once it finished the calculating, ... > CF (Conditional Formatting) is a weird beast, and it can cause the strange ...
    (microsoft.public.access.forms)
  • Re: formatting a calculated number field on a form
    ... > I only used age at death as an example. ... > and heart attack, age 40 and heart attack, surgery and death, etc. ... >>bit different in that a parameter query limits the recordset before it ... I assumed that you were calculating on ...
    (microsoft.public.access.forms)
  • Re: calculating dates in records
    ... What I was suggesting is calculating the next date (that you will need to ... I'm imagining a query against the most recent row (requires a ... <Access MVP> ... >> Jeff Boyce ...
    (microsoft.public.access.formscoding)
  • Re: Parameter Query and Date calculations....
    ... Access is not good at determining the data types of calculated query fields, ... parameters, and unbound controls. ... Declare any parameter in your query. ... > I am calculating a future date using the DateAdd function in a query (the ...
    (comp.databases.ms-access)
  • Re: query error
    ... The second query groups them by job number for one total time on job. ... you've already recorded start and stop times, the TaskTime value could be ... I have a specific job number that is not calculating properly. ...
    (microsoft.public.access.queries)