baadams
Board Regular
- Joined
- Mar 2, 2006
- Messages
- 134
In this particular application I don't think an update query would be applicable.
I've got a database that tracks constraints to a particular job. There are many types of constraints. Some constraints are entered into the database manually. Some are entered programmatically using macros. The ones entered automatically come from a different system via daily .CSV dumps. Once they enter the tracking database they are given status, OPR (organization of primary responsibility), ECD (estimated complete date) or they are closed. The above fields are assigned, only the OPR comes from the .CSV download.
Here's my question. How do I update the OPR in the database based on what is in the daily file? Each day the .CSV file is updated. The database only tracks open constraints and the .CSV file has all constraints open, closed and void. Right now we view each constraint and re-assign the OPR manually after the file is updated.
Here's the catch. If the OPR has changed I have to clear the ECD. The ECD that exists was the ECD for that organization and doesn't apply to the organization it's currently assinged to. Also if the OPR is "CL" meaning it's been closed I need to assign the field [Closed] to the value in the .CSV. The .CSV file has a field [Closed Date] so this seems pretty straight forward. I could easily assign the closed date with an update query. But If I figure out how to update the ECD and OPR, I'm sure the date assignment would be simple.
I don't know how to udated the OPR and clear the ECD. I might have 50 open constraints and there will be 8 constraints re-assinged to a new OPR, these are the ones I want to update and clear the old ECD.
My current thoughts are that I need two recordsets, these recordsets will compare the constraint ID (unique to each constraint) in the constraint table and the .CSV download, if the OPR is different between each file, assign the OPR in the constraint table the value that exists in the .CSV (which is linked to the database) and clear the ECD in the constraint table. Also check if the OPR = "CL", if so, tbl_constraints.[Closed] = CSV.[Closed Date]
I hope this makes sense? It's clear in my head, but I'm at a loss in the execution. Thanks for the help. Sorry it took so long to describe
I've got a database that tracks constraints to a particular job. There are many types of constraints. Some constraints are entered into the database manually. Some are entered programmatically using macros. The ones entered automatically come from a different system via daily .CSV dumps. Once they enter the tracking database they are given status, OPR (organization of primary responsibility), ECD (estimated complete date) or they are closed. The above fields are assigned, only the OPR comes from the .CSV download.
Here's my question. How do I update the OPR in the database based on what is in the daily file? Each day the .CSV file is updated. The database only tracks open constraints and the .CSV file has all constraints open, closed and void. Right now we view each constraint and re-assign the OPR manually after the file is updated.
Here's the catch. If the OPR has changed I have to clear the ECD. The ECD that exists was the ECD for that organization and doesn't apply to the organization it's currently assinged to. Also if the OPR is "CL" meaning it's been closed I need to assign the field [Closed] to the value in the .CSV. The .CSV file has a field [Closed Date] so this seems pretty straight forward. I could easily assign the closed date with an update query. But If I figure out how to update the ECD and OPR, I'm sure the date assignment would be simple.
I don't know how to udated the OPR and clear the ECD. I might have 50 open constraints and there will be 8 constraints re-assinged to a new OPR, these are the ones I want to update and clear the old ECD.
My current thoughts are that I need two recordsets, these recordsets will compare the constraint ID (unique to each constraint) in the constraint table and the .CSV download, if the OPR is different between each file, assign the OPR in the constraint table the value that exists in the .CSV (which is linked to the database) and clear the ECD in the constraint table. Also check if the OPR = "CL", if so, tbl_constraints.[Closed] = CSV.[Closed Date]
I hope this makes sense? It's clear in my head, but I'm at a loss in the execution. Thanks for the help. Sorry it took so long to describe
