Re: query error

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: query error
    ... "Jeff Boyce" wrote: ... 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 ...
    (microsoft.public.access.queries)
  • 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/Expression Help Required!
    ... "Wind Down" records. ... Here is a snipt of the information i have extracted via a query. ... I just cant work out how to actually get "Total Time at work" for each ... and every "Agent Pin" (basically i dont know how to get Wind Down(End ...
    (microsoft.public.access.gettingstarted)
Quantcast