DSM Labs Consistency check.accdb: Difference between revisions
Jump to navigation
Jump to search
Ttenbergen (talk | contribs) m (m) |
Ttenbergen (talk | contribs) |
||
(66 intermediate revisions by 2 users not shown) | |||
Line 1: | Line 1: | ||
The program | |||
:: '''X:\CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb''' | |||
generates request extracts and imports lab data as provided by DSM. | |||
The data is stored in [[DSM Labs data.accdb]]. | |||
== Requesting/importing == | |||
* [[Instructions for requesting a batch of data from DSM]] | |||
* [[Instructions for importing a batch of DSM Data]] | |||
== Log == | |||
=== changes for next version === | |||
nothing right now | |||
<!-- if needed un-comment this following: | |||
{{TT | | |||
_DEV_DSM | |||
* Next version: | |||
** Nothing right now. | |||
}} | |||
end of comment--> | |||
=== version 2023-09-27 === | |||
copied to server, notified Pagasa | |||
* removed Trish from email and added Herman | |||
=== version 2023-05-17 === | |||
not rolled yet | |||
* removed re-connect for PHI, shouldn't be needed any more and was causing error. | |||
=== version 2023-01-12 === | |||
copied to server | |||
* updated [[created_AdmitDtTm query]] so it won't crash when there are nulls in all three dates | |||
=== version 2022-11-03 === | |||
* added some labs to mappings as per email from Allan. [[User:Ttenbergen|Ttenbergen]] 10:22, 2022 November 3 (CDT) | |||
=== version 2022-08-29 === | |||
copied to server [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT) | |||
* fixed error that was introduced into an SQL statement in the most recent update. [[User:Ttenbergen|Ttenbergen]] 15:48, 2022 August 29 (CDT) | |||
** specifically, a previous change to query 1_C_requester_cc_maker had added a "where" clause to that query, and VBA code in Sub Export_requestor_file() was trying to add another where clause; changed that to "and" and it fixed it. [[User:Ttenbergen|Ttenbergen]] 12:11, 2022 August 31 (CDT) | |||
=== version 2022-07-07 === | |||
copied to server [[User:Ttenbergen|Ttenbergen]] 11:49, 2022 July 12 (CDT) | |||
* added queryRunner to get better error management for queries | |||
* updated compact_runner that wasn't working at all | |||
* updated the command in VBA that generates the 1_D_format_limit data to use the new Admit_DtTm, it was still using the old format. | |||
=== version 2022-06-09 === | |||
copied to server | |||
* updated query ''import_request_matcher'' to do counts and filter to only 0s | |||
* updated query ''1_cc_request_maker'' to only list vetted records | |||
=== version 2022-06-01 === | |||
copied to server | |||
* cleanup: deleted table ''TEMP'' and queries ''tmp_DSM_D_ID_fix_2020-10-01'', ''s_mapping_lab_updater1'' and ''s_group_updater''; renamed ''s_group_updater1'' to ''s_group_updater'' | |||
* Julie had raised a scenario where duplicate data might arrive in the file; confirmed that import process de-duplicates against import file rather than deleting duplicates after | |||
* [[Change to replace Accept DtTm with first Service tmp entry, and Arrive DtTm with first Boarding Loc]]: | |||
** General: | |||
*** updated DB_Connect to also connect [[L TmpV2 table]], since we now need [[Boarding Loc]] and [[Service tmp entry]] to make [[Admit DtTm]] | |||
*** copied in [[created_AdmitDtTm query]] and [[created_FirstService query]] from [[CFE]] | |||
** requester: | |||
*** updated query ''1_C_requester_cc_maker'' to use new Admit_DtTm instead of old Arrive_DtTm | |||
*** confirmed that VBA for sending request doesn't need modification | |||
** importer: | |||
*** confirmed no changes should be required to importer | |||
=== version 2020-09-30 === | |||
* updated the specs for the importer so it will work with the [[RecordStatus field]] added to requestor in last change. Ttenbergen 14:22, 2020 September 30 (CDT) | |||
=== version 2020-08-13 === | |||
* Added [[RecordStatus field]] to the DSM requestor to query and file.--[[User:PTorres|PTorres]] 15:43, 2020 August 13 (CDT) | |||
=== version 2020-06-04 === | |||
* fixed link to this page, it still had link to old page name | |||
* changed import to start at [[Accept_DtTm]] instead of [[Arrive_DtTm]] as it had been; as per various discussions during [[Task Team Meeting - Rolling Agenda and Minutes 2020]] | |||
=== version 2020-05-06 === | |||
* added "DAO.DBEngine.SetOption dbmaxlocksperfile, 15000" to prevent out-of-diskspace error [https://web.archive.org/web/20200224033850/http://www.accessrepairnrecovery.com/blog/fix-access-file-sharing-lock-count-exceeded-error] (archived version) | |||
* changed query ''2_dedupe_appender_group'' to set DSM_TestCode to 0 rather than "not from group" for records arriving as test; this would have generated a hidden error all along but there would be no consequence to the data, this just cleans up that hidden error | |||
=== version 2019-12-18 === | |||
* Added several labs as per Allan Garland, incl the ABG labs. | |||
* fixed a bug from last time, re-imported queries I had misunderstood | |||
** s_group_ListNewFound - used when labs first arrive to mark them with date | |||
** s_mapping_lab_NewFound - used when labs first arrive to mark them with date | |||
=== version 2019-12-09 === | |||
* added automatic reconnecting (on opening the following runs: module DB_Connect, function DB_Connect) | |||
* removed old queries: | |||
** s_group_ListNewFound (replaced by ''s_group_imported_today'') | |||
** s_group_ListNewlyAdded (replaced by ''s_group_imported_today'') | |||
** s_mapping_lab_NewFound (replaced by ''s_mapping_lab_today'') | |||
** s_mapping_lab_ListNewlyAdded (replaced by ''s_mapping_lab_today'') | |||
* improved error messaging when trying to run export a second time the same day, which breaks because it tries to create a directory which already exists. | |||
=== version 2019-05-15 === | |||
* added [[query Import_request_matcher]] to be used as part of [[Instructions for importing a batch of DSM Data]]. | |||
===version 2019-02-14 === | |||
* tweaked messaging and button hover overs slightly. | |||
* automated re-linking | |||
=== version 2018-04-03 === | |||
* updated table s_mapping_lab to see new fields as PTT as per email from Allan Garland to Tina Tenbergen 2018-02-28 | |||
=== version 2017-11-24 === | |||
* updating importer to cycle through all files in a directory rather than opening each individually. | |||
=== version 2017-04-29 === | |||
::( | * changed the way new labs are checked for | ||
=== version 2016-09-26 === | |||
Moved to X: | |||
* some tweaks to deal with Alun's new data as of March. Ttenbergen 15:23, 2016 October 17 (CDT) | |||
== | === version 2015-12-01 === | ||
local for now | |||
* | * adding checks for locations and labs that have not been encountered before. Ran across different field lengths, emailed Alun.Ttenbergen 14:43, 2015 December 1 (CST) | ||
== | == Related articles == | ||
{{Related Articles}} | |||
[[Category:DSM Labs Extract]] | [[Category:DSM Labs Extract]] | ||
[[Category:DSM Labs Consistency check.accdb | *]] |
Latest revision as of 10:55, 2023 September 27
The program
- X:\CCMDB\DSM Labs Imports\DSM Labs Consistency check.accdb
generates request extracts and imports lab data as provided by DSM.
The data is stored in DSM Labs data.accdb.
Requesting/importing
Log
changes for next version
nothing right now
version 2023-09-27
copied to server, notified Pagasa
- removed Trish from email and added Herman
version 2023-05-17
not rolled yet
- removed re-connect for PHI, shouldn't be needed any more and was causing error.
version 2023-01-12
copied to server
- updated created_AdmitDtTm query so it won't crash when there are nulls in all three dates
version 2022-11-03
- added some labs to mappings as per email from Allan. Ttenbergen 10:22, 2022 November 3 (CDT)
version 2022-08-29
copied to server Ttenbergen 15:48, 2022 August 29 (CDT)
- fixed error that was introduced into an SQL statement in the most recent update. Ttenbergen 15:48, 2022 August 29 (CDT)
- specifically, a previous change to query 1_C_requester_cc_maker had added a "where" clause to that query, and VBA code in Sub Export_requestor_file() was trying to add another where clause; changed that to "and" and it fixed it. Ttenbergen 12:11, 2022 August 31 (CDT)
version 2022-07-07
copied to server Ttenbergen 11:49, 2022 July 12 (CDT)
- added queryRunner to get better error management for queries
- updated compact_runner that wasn't working at all
- updated the command in VBA that generates the 1_D_format_limit data to use the new Admit_DtTm, it was still using the old format.
version 2022-06-09
copied to server
- updated query import_request_matcher to do counts and filter to only 0s
- updated query 1_cc_request_maker to only list vetted records
version 2022-06-01
copied to server
- cleanup: deleted table TEMP and queries tmp_DSM_D_ID_fix_2020-10-01, s_mapping_lab_updater1 and s_group_updater; renamed s_group_updater1 to s_group_updater
- Julie had raised a scenario where duplicate data might arrive in the file; confirmed that import process de-duplicates against import file rather than deleting duplicates after
- Change to replace Accept DtTm with first Service tmp entry, and Arrive DtTm with first Boarding Loc:
- General:
- updated DB_Connect to also connect L TmpV2 table, since we now need Boarding Loc and Service tmp entry to make Admit DtTm
- copied in created_AdmitDtTm query and created_FirstService query from CFE
- requester:
- updated query 1_C_requester_cc_maker to use new Admit_DtTm instead of old Arrive_DtTm
- confirmed that VBA for sending request doesn't need modification
- importer:
- confirmed no changes should be required to importer
- General:
version 2020-09-30
- updated the specs for the importer so it will work with the RecordStatus field added to requestor in last change. Ttenbergen 14:22, 2020 September 30 (CDT)
version 2020-08-13
- Added RecordStatus field to the DSM requestor to query and file.--PTorres 15:43, 2020 August 13 (CDT)
version 2020-06-04
- fixed link to this page, it still had link to old page name
- changed import to start at Accept_DtTm instead of Arrive_DtTm as it had been; as per various discussions during Task Team Meeting - Rolling Agenda and Minutes 2020
version 2020-05-06
- added "DAO.DBEngine.SetOption dbmaxlocksperfile, 15000" to prevent out-of-diskspace error [1] (archived version)
- changed query 2_dedupe_appender_group to set DSM_TestCode to 0 rather than "not from group" for records arriving as test; this would have generated a hidden error all along but there would be no consequence to the data, this just cleans up that hidden error
version 2019-12-18
- Added several labs as per Allan Garland, incl the ABG labs.
- fixed a bug from last time, re-imported queries I had misunderstood
- s_group_ListNewFound - used when labs first arrive to mark them with date
- s_mapping_lab_NewFound - used when labs first arrive to mark them with date
version 2019-12-09
- added automatic reconnecting (on opening the following runs: module DB_Connect, function DB_Connect)
- removed old queries:
- s_group_ListNewFound (replaced by s_group_imported_today)
- s_group_ListNewlyAdded (replaced by s_group_imported_today)
- s_mapping_lab_NewFound (replaced by s_mapping_lab_today)
- s_mapping_lab_ListNewlyAdded (replaced by s_mapping_lab_today)
- improved error messaging when trying to run export a second time the same day, which breaks because it tries to create a directory which already exists.
version 2019-05-15
- added query Import_request_matcher to be used as part of Instructions for importing a batch of DSM Data.
version 2019-02-14
- tweaked messaging and button hover overs slightly.
- automated re-linking
version 2018-04-03
- updated table s_mapping_lab to see new fields as PTT as per email from Allan Garland to Tina Tenbergen 2018-02-28
version 2017-11-24
- updating importer to cycle through all files in a directory rather than opening each individually.
version 2017-04-29
- changed the way new labs are checked for
version 2016-09-26
Moved to X:
- some tweaks to deal with Alun's new data as of March. Ttenbergen 15:23, 2016 October 17 (CDT)
version 2015-12-01
local for now
- adding checks for locations and labs that have not been encountered before. Ran across different field lengths, emailed Alun.Ttenbergen 14:43, 2015 December 1 (CST)