update query

[NAS]

An update query i try to run shows the progres bar as being full, but then
nothing happens, access freezes and the data isnt there. Access 2003, Windows
XP

 

[KARL DEWEY]

Post the SQL of your update query for FREE advice (Well worth the price) by
opening the query in design view, click on VIEW - SQL View, highlight all,
copy, and paste in a post.

 

[NAS]

Ha ha yes I know it is free, I put that there to hopefully ward off this
"Steve" character I have read so much about. Anyways here it is:

INSERT INTO tbl_MBRS ( SSN, GRD, LNAME, FNAME, MI, PRUC, TRUC, ATRUC, FRUC,
RECORD_STATUS_CODE, [WK PH] )
SELECT Right(ODSE_INDIVIDUAL_MARINE.SSN,9) AS SOCIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE AS GRD,
ODSE_INDIVIDUAL_MARINE.LAST_NAME AS LNAME, ODSE_INDIVIDUAL_MARINE.FIRST_NAME
AS FNAME, ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL AS MI,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE AS PRUC,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE AS TRUC,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE AS ATRUC,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE AS FRUC,
ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER AS [WK PH]
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
WHERE (((ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE)<>"E") AND
((ODSE_INDIVIDUAL_MARINE.COMPONENT_CODE)="11"));

 

[John W. Vinson]

Ha ha yes I know it is free, I put that there to hopefully ward off this
"Steve" character I have read so much about.

Anyways here it is:

INSERT INTO tbl_MBRS ( SSN, GRD, LNAME, FNAME, MI, PRUC, TRUC, ATRUC, FRUC,
RECORD_STATUS_CODE, [WK PH] )
SELECT Right(ODSE_INDIVIDUAL_MARINE.SSN,9) AS SOCIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE AS GRD,
ODSE_INDIVIDUAL_MARINE.LAST_NAME AS LNAME, ODSE_INDIVIDUAL_MARINE.FIRST_NAME
AS FNAME, ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL AS MI,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE AS PRUC,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE AS TRUC,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE AS ATRUC,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE AS FRUC,
ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER AS [WK PH]
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
WHERE (((ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE)<>"E") AND
((ODSE_INDIVIDUAL_MARINE.COMPONENT_CODE)="11"));

How big are these tables? I presume the ODSE_ tables are linked from some
(big?) server database? Are the SSN, STATUS_CODE and COMPONENT_CODE fields
indexed in their tables? If you change this query into a simple SELECT query,
open it in datasheet view, and go to the last record, how long does it take?

And how many mbytes is your database? Is it perhaps pushing the 2GByte size
limit?

 

[NAS]

The database is 129MB and there are 202,781 records. It takes up to 5 minutes
to go to the last record. Operational Data Store Enterprise (ODSE) is an
Oracle database that represents the current snapshot of all the data in the
Marine Corps Total Force System (MCTFS). Hopefully that gives everyone an
idea about much info is there. Record status code is the only field in the
table that is indexed.
--
NO, I DO NOT WANT YOU TO BUILD A DATABASE FOR ME FOR A "REASONABLE FEE".

Ha ha yes I know it is free, I put that there to hopefully ward off this
"Steve" character I have read so much about.

Anyways here it is:

INSERT INTO tbl_MBRS ( SSN, GRD, LNAME, FNAME, MI, PRUC, TRUC, ATRUC, FRUC,
RECORD_STATUS_CODE, [WK PH] )
SELECT Right(ODSE_INDIVIDUAL_MARINE.SSN,9) AS SOCIAL,
ODSE_INDIVIDUAL_MARINE.PRESENT_GRADE_CODE AS GRD,
ODSE_INDIVIDUAL_MARINE.LAST_NAME AS LNAME, ODSE_INDIVIDUAL_MARINE.FIRST_NAME
AS FNAME, ODSE_INDIVIDUAL_MARINE.MIDDLE_INITIAL AS MI,
ODSE_INDIVIDUAL_MARINE.PRESENT_REPORTING_UNIT_CODE AS PRUC,
ODSE_INDIVIDUAL_MARINE.TEMPORARY_REPORTING_UNIT_CODE AS TRUC,
ODSE_INDIVIDUAL_MARINE.ADDL_TEMP_REPORTING_UNIT_CODE AS ATRUC,
ODSE_INDIVIDUAL_MARINE.FAP_REPORTING_UNIT_CODE AS FRUC,
ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE,
ODSE_INDIVIDUAL_MARINE.WORK_TELEPHONE_NUMBER AS [WK PH]
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
WHERE (((ODSE_INDIVIDUAL_MARINE.RECORD_STATUS_CODE)<>"E") AND
((ODSE_INDIVIDUAL_MARINE.COMPONENT_CODE)="11"));

How big are these tables? I presume the ODSE_ tables are linked from some
(big?) server database? Are the SSN, STATUS_CODE and COMPONENT_CODE fields
indexed in their tables? If you change this query into a simple SELECT query,
open it in datasheet view, and go to the last record, how long does it take?

And how many mbytes is your database? Is it perhaps pushing the 2GByte size
limit?

 

[John W. Vinson]

The database is 129MB and there are 202,781 records. It takes up to 5 minutes
to go to the last record. Operational Data Store Enterprise (ODSE) is an
Oracle database that represents the current snapshot of all the data in the
Marine Corps Total Force System (MCTFS). Hopefully that gives everyone an
idea about much info is there. Record status code is the only field in the
table that is indexed.
--

Well, that's the problem. If it takes five minutes just to VIEW the data, it
will necessarily take longer to both view the data *and* populate a local
table!

More critically, since you're joining two tables on SSN, and you apparently
don't have an index on SSN, it will need to do a *full table scan* for every
record appended in order to find the matching SSN. This will slow things down
very drastically. Can you talk to your Oracle DBA about getting this critical
field indexed?