I have a db where I import building reports on a daily basis. The initial report has numerous fields that are filled in the first time the report is downloaded into my db. The firm I get the reports from has the same Main ID#, i.e. 201400567891 that remains the same, but the Version number changes with each update. Most of the info that is downloaded in Version 1 never changes, ie. job name, city, zip, etc. Basically only the Version #, a note field change.
What I am trying to do is take the Version 1 download and put the Publish Date for Version 1 in a field I call [1st report date]. I do this with a query that is working correctly. (Version 1 publish date.jpg)
The second query is identical to the first Query, except it takes any Version 2 report or higher and updates the fields that change. The only difference is that I have the query put the Publish Date in a different field [Rev_Date] in the same table. This query works properly as long as a report with the same Main ID# was not already in the system. In this case, the [Rev_date] field is NOT updated, however, every other field that I want changed IS updated. (revised date field worked.jpg)
Since I am just starting to download these records from this firm, not all Master ID# records are loaded with a Version 1. Some initially come into the system with a Version # of, say 2 or 3 or 4. If there is not a previous Master ID# in the system, the 2nd query WILL put the Publish Date in the [Rev_Date] field. (revised date field failed) I can tell the query worked except for the [Rev_date] update because the date of 3/10/2014 in the bottom right corner is what the [Rev_date] is supposed to be. the date was successfully imported into a second subform I use, but not the main form.
So the hang up is not what Version # is involved, the hang up is whether a record with the same Master ID# is already in my db. I have checked my table setups and nothing should be preventing it.
Thanks in advance for everyone's help
What I am trying to do is take the Version 1 download and put the Publish Date for Version 1 in a field I call [1st report date]. I do this with a query that is working correctly. (Version 1 publish date.jpg)
The second query is identical to the first Query, except it takes any Version 2 report or higher and updates the fields that change. The only difference is that I have the query put the Publish Date in a different field [Rev_Date] in the same table. This query works properly as long as a report with the same Main ID# was not already in the system. In this case, the [Rev_date] field is NOT updated, however, every other field that I want changed IS updated. (revised date field worked.jpg)
Since I am just starting to download these records from this firm, not all Master ID# records are loaded with a Version 1. Some initially come into the system with a Version # of, say 2 or 3 or 4. If there is not a previous Master ID# in the system, the 2nd query WILL put the Publish Date in the [Rev_Date] field. (revised date field failed) I can tell the query worked except for the [Rev_date] update because the date of 3/10/2014 in the bottom right corner is what the [Rev_date] is supposed to be. the date was successfully imported into a second subform I use, but not the main form.
So the hang up is not what Version # is involved, the hang up is whether a record with the same Master ID# is already in my db. I have checked my table setups and nothing should be preventing it.
Thanks in advance for everyone's help
Attachments
-
Version 1 Publish Date.jpg -
Revised date field worked.jpg -
Revised date field failed.jpg
