You have an error in your query. You need to change the select query so
it does not return all the fields in the table, plus the listed ones in
the select clause
SELECT tblBH_MHCD_PAUTH_TimeTrack_Step2.* <<<<<< Every field >>>>>>
INSERT INTO tblPAUTH_Comm ( RcvdTmLen, RcvdTmCalc
, RcvdTmCalc2, RcvdDTCalc
, CmpltTmLen, CmpltTmCalc
, CmpltTmCalc2, CmpltDTCalc
, ElapsedHrs, ElapsedMins
, DaysOut, MinsOut, Turnaround
, TurnaroundCal, [In Comp?]
, [BusInComp?], [CalInComp?] )
SELECT Len([rcvd tm]) AS RcvdTmLen
, IIf([Rcvdtmlen]=3,Left([rcvd tm],1) & ":" &
Right([rcvd tm],2),Left([rcvd tm],2) &
":" & Right([rcvd tm],2)) AS RcvdTmCalc
, TimeValue([RcvdTmcalc]) AS RcvdTmCalc2
, ([Rcvd Dt]+[RcvdTmCalc2]) AS RcvdDTCalc
, Len([Cmplt Tm]) AS CmpltTmLen
, IIf([Cmplttmlen]=3,Left([Cmplt tm],1) & ":" &
Right([Cmplt Tm],2),Left([Cmplt tm],2) & ":" &
Right([Cmplt Tm],2)) AS CmpltTmCalc
, TimeValue([CmpltTmcalc]) AS CmpltTmCalc2
, ([Cmplt Dt]+[CmpltTmCalc2]) AS CmpltDTCalc
, DateDiff("h",[RcvdDtCalc],[CmpltDtCalc]) AS ElapsedHrs
, DateDiff("n",[RcvdDtCalc],[CmpltDtCalc]) AS ElapsedMins
, Weekends([Rcvd Dt],[Cmplt Dt],[mcauth_urg_in]) AS DaysOut
, [DaysOut]*1440 AS MinsOut
, ([ElapsedMins]-[MinsOut])/60 AS Turnaround
, ([ElapsedMins]/60) AS TurnaroundCal
, Compliance([mcauth_urg_in],[Turnaround]) AS [In Comp?]
, BusCompliance([mcauth_urg_in],[Turnaround]) AS [BusInComp?]
, CalCompliance([mcauth_urg_in],[TurnaroundCal]) AS [CalInComp?]
FROM tblBH_MHCD_PAUTH_TimeTrack_Step2
WHERE (((tblBH_MHCD_PAUTH_TimeTrack_Step2.[CMPLT DT]) Between #1/24/2008#
And #1/31/2008#));
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
The select query runs within seconds.
No, the CMPLT DT is not indexed in the tblBH_MHCD_PAUTH_TimeTrack_Step2 table.
I looked at the tblPAUTH_Comm and there are no indexes.
There are only like 1500 records.
Jerry Whittle said:
How does the select statement run by itself? Is it slow? Is
tblBH_MHCD_PAUTH_TimeTrack_Step2.[CMPLT DT] indexed as it's in the WHERE
clause.
If you remove the user defined functions, does it run faster? I noticed that
you have a number of user defined functions. You might want to check them to
see if they are written efficiently.
Assuming that the SELECT part runs quick enough, it could be the
tblPAUTH_Comm. Are there many indexes in it? You might try to drop any
indexes that aren't unique or the primary key. Also are there any fields with
validation rules? They can slow things up.
How many records are we talking about?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
Here is the SQL.
INSERT INTO tblPAUTH_Comm ( RcvdTmLen, RcvdTmCalc, RcvdTmCalc2, RcvdDTCalc,
CmpltTmLen, CmpltTmCalc, CmpltTmCalc2, CmpltDTCalc, ElapsedHrs, ElapsedMins,
DaysOut, MinsOut, Turnaround, TurnaroundCal, [In Comp?], [BusInComp?],
[CalInComp?] )
SELECT tblBH_MHCD_PAUTH_TimeTrack_Step2.*, Len([rcvd tm]) AS RcvdTmLen,
IIf([Rcvdtmlen]=3,Left([rcvd tm],1) & ":" & Right([rcvd tm],2),Left([rcvd
tm],2) & ":" & Right([rcvd tm],2)) AS RcvdTmCalc, TimeValue([RcvdTmcalc]) AS
RcvdTmCalc2, ([Rcvd Dt]+[RcvdTmCalc2]) AS RcvdDTCalc, Len([Cmplt Tm]) AS
CmpltTmLen, IIf([Cmplttmlen]=3,Left([Cmplt tm],1) & ":" & Right([Cmplt
Tm],2),Left([Cmplt tm],2) & ":" & Right([Cmplt Tm],2)) AS CmpltTmCalc,
TimeValue([CmpltTmcalc]) AS CmpltTmCalc2, ([Cmplt Dt]+[CmpltTmCalc2]) AS
CmpltDTCalc, DateDiff("h",[RcvdDtCalc],[CmpltDtCalc]) AS ElapsedHrs,
DateDiff("n",[RcvdDtCalc],[CmpltDtCalc]) AS ElapsedMins, Weekends([Rcvd
Dt],[Cmplt Dt],[mcauth_urg_in]) AS DaysOut, [DaysOut]*1440 AS MinsOut,
([ElapsedMins]-[MinsOut])/60 AS Turnaround, ([ElapsedMins]/60) AS
TurnaroundCal, Compliance([mcauth_urg_in],[Turnaround]) AS [In Comp?],
BusCompliance([mcauth_urg_in],[Turnaround]) AS [BusInComp?],
CalCompliance([mcauth_urg_in],[TurnaroundCal]) AS [CalInComp?]
FROM tblBH_MHCD_PAUTH_TimeTrack_Step2
WHERE (((tblBH_MHCD_PAUTH_TimeTrack_Step2.[CMPLT DT]) Between #1/24/2008#
And #1/31/2008#));
:
One thing to try is press the Alt + Tab keys to swing around back to the
database. Every once in a while things will look like they are working;
however, a little popup error message, like X number of records have been
updated, is hidden behind Access. In a really bad case there might be a major
error message not part of Access holding things up.
Other than that, show us the query / SQL. Open the query in design view.
Next go to View, SQL View and copy and past it here. Information on primary
keys and relationships would be a nice touch too. Also any indexes.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
:
I have an append query that has alot of calculations in it and it takes
several hours to run. I have tried copying and pasting the data but because
of the calculations it still takes forever to copy. Does anyone have any
suggestions on what I can do?
thanks
Lisa
