query wont update

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.
 
J

Jerry Whittle

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?
 
S

S.Clark

$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...
 
N

NAS

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*"));
 
J

Jerry Whittle

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.
 
H

Hans Up

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 ...
 
Top