query wont update

  • Thread starter Thread starter NAS
  • Start date Start date
N

NAS

I have an append query that wont run! The progress bar gets to the first 2
green blocks and then nothing, it times out. I have left in running for a few
hours to be safe, but it still doesnt work. I am using Access 2003 on Windows
XP.
 
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

Does it return records as a Select query?
 
$5 on Cross Join.

Jerry Whittle said:
Show us the SQL. Open the query in design view. Next go to View, SQL View
and copy and past it here.

Does it return records as a Select query?
Click to expand...
 
Sory I should have explained more. It is an append query, here is the
SQL:INSERT INTO tbl_MBRS ( SSN, PRESENT_GRADE_CODE, LAST_NAME, FIRST_NAME,
MIDDLE_INITIAL, PRESENT_REPORTING_UNIT_CODE, TEMPORARY_REPORTING_UNIT_CODE,
ADDL_TEMP_REPORTING_UNIT_CODE, FAP_REPORTING_UNIT_CODE,
WORK_TELEPHONE_NUMBER, DUTY_STATUS_CODE, PRESENT_UNIT_JOINED_DATE,
INDIVIDUAL_LOCATION_CODE, EXPIRATION_OF_ACTIVE_SERVICE, SCREENING_DATE,
FUTURE_MONITORED_COMMAND_CODE, FUTURE_REPORTING_UNIT_CODE,
ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, PARUC, FARUC, TARUC, ATARUC
)
SELECT ODSE_INDIVIDUAL_MARINE.SSN,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE, ODSE_INDIVIDUAL_MARINE.LAST_NAME,
ODSE_INDIVIDUAL_MARINE.FIRST_NAME, ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER,
ODSE_INDIVIDUAL_MARINE.DUTY_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.PRESENT_UNIT_JOINED_DATE,
ODSE_INDIVIDUAL_MARINE.INDIVIDUAL_LOCATION_CODE,
ODSE_INDIVIDUAL_MARINE.EXPIRATION_OF_ACTIVE_SERVICE,
ODSE_RESERVE.SCREENING_DATE,
ODSE_MARINE_COMMAND.FUTURE_MONITORED_COMMAND_CODE,
ODSE_MARINE_COMMAND.FUTURE_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_ARRIVAL_DATE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_DEPARTURE_DATE,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD
FROM ((ODSE_INDIVIDUAL_MARINE INNER JOIN ODSE_MARINE_COMMAND ON
ODSE_INDIVIDUAL_MARINE.SSN = ODSE_MARINE_COMMAND.SSN) INNER JOIN ODSE_RESERVE
ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_RESERVE.SSN) INNER JOIN
ODSE_SERVICE_TOUR ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_SERVICE_TOUR.SSN
WHERE (((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*"));
 
Will this run? If so how long does it take to scroll down to the last records?

SELECT ODSE_INDIVIDUAL_MARINE.SSN,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE,
ODSE_INDIVIDUAL_MARINE.LAST_NAME,
ODSE_INDIVIDUAL_MARINE.FIRST_NAME,
ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER,
ODSE_INDIVIDUAL_MARINE.DUTY_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.PRESENT_UNIT_JOINED_DATE,
ODSE_INDIVIDUAL_MARINE.INDIVIDUAL_LOCATION_CODE,
ODSE_INDIVIDUAL_MARINE.EXPIRATION_OF_ACTIVE_SERVICE,
ODSE_RESERVE.SCREENING_DATE,
ODSE_MARINE_COMMAND.FUTURE_MONITORED_COMMAND_CODE,
ODSE_MARINE_COMMAND.FUTURE_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_ARRIVAL_DATE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_DEPARTURE_DATE,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD
FROM ((ODSE_INDIVIDUAL_MARINE INNER JOIN ODSE_MARINE_COMMAND ON
ODSE_INDIVIDUAL_MARINE.SSN = ODSE_MARINE_COMMAND.SSN) INNER JOIN ODSE_RESERVE
ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_RESERVE.SSN) INNER JOIN
ODSE_SERVICE_TOUR ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_SERVICE_TOUR.SSN
WHERE (((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*"));

If you have fields like below, this is part of the problem. They should be
in another table.
PRESENT_ADMIN_RPT_UNIT_CD
FAP_ADMIN_RPT_UNIT_CD
TEMP_ADMIN_RPT_UNIT_CD
ADD_TEMP_ADMIN_RPT_UNIT_CD

Also these have me confuse. If it has to be equal to 45124, why even check
for 01524?

(ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")

I also find it interesting that you are picking on people from the New
England area if SSN is what I think it is.

BTW: Retired USAF E-8 here.
 
NAS said:
Sory I should have explained more. It is an append query, here is the
SQL:INSERT INTO tbl_MBRS ( SSN, PRESENT_GRADE_CODE, LAST_NAME, FIRST_NAME,
MIDDLE_INITIAL, PRESENT_REPORTING_UNIT_CODE, TEMPORARY_REPORTING_UNIT_CODE,
ADDL_TEMP_REPORTING_UNIT_CODE, FAP_REPORTING_UNIT_CODE,
WORK_TELEPHONE_NUMBER, DUTY_STATUS_CODE, PRESENT_UNIT_JOINED_DATE,
INDIVIDUAL_LOCATION_CODE, EXPIRATION_OF_ACTIVE_SERVICE, SCREENING_DATE,
FUTURE_MONITORED_COMMAND_CODE, FUTURE_REPORTING_UNIT_CODE,
ESTIMATED_ARRIVAL_DATE, ESTIMATED_DEPARTURE_DATE, PARUC, FARUC, TARUC, ATARUC
)
SELECT ODSE_INDIVIDUAL_MARINE.SSN,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE, ODSE_INDIVIDUAL_MARINE.LAST_NAME,
ODSE_INDIVIDUAL_MARINE.FIRST_NAME, ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER,
ODSE_INDIVIDUAL_MARINE.DUTY_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.PRESENT_UNIT_JOINED_DATE,
ODSE_INDIVIDUAL_MARINE.INDIVIDUAL_LOCATION_CODE,
ODSE_INDIVIDUAL_MARINE.EXPIRATION_OF_ACTIVE_SERVICE,
ODSE_RESERVE.SCREENING_DATE,
ODSE_MARINE_COMMAND.FUTURE_MONITORED_COMMAND_CODE,
ODSE_MARINE_COMMAND.FUTURE_REPORTING_UNIT_CODE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_ARRIVAL_DATE,
ODSE_INDIVIDUAL_MARINE.ESTIMATED_DEPARTURE_DATE,
ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD,
ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD
FROM ((ODSE_INDIVIDUAL_MARINE INNER JOIN ODSE_MARINE_COMMAND ON
ODSE_INDIVIDUAL_MARINE.SSN = ODSE_MARINE_COMMAND.SSN) INNER JOIN ODSE_RESERVE
ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_RESERVE.SSN) INNER JOIN
ODSE_SERVICE_TOUR ON ODSE_INDIVIDUAL_MARINE.SSN = ODSE_SERVICE_TOUR.SSN
WHERE (((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.PRESENT_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.FAP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*")) OR
(((ODSE_INDIVIDUAL_MARINE.SSN) Like "0*") AND
((ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD)="45124" And
(ODSE_MARINE_COMMAND.ADD_TEMP_ADMIN_RPT_UNIT_CD) Not Like "*01524*"));
Click to expand...

What are the data types of these fields in the ODSE_MARINE_COMMAND table?

PRESENT_ADMIN_RPT_UNIT_CD
FAP_ADMIN_RPT_UNIT_CD
TEMP_ADMIN_RPT_UNIT_CD
ADD_TEMP_ADMIN_RPT_UNIT_CD

Your WHERE clause does text comparisons on those fields. If they are
numerical, instead of text, data types Jet/ACE may not throw an error,
but transforming from number to text could take considerable time ...
 
Back
Top