Annually update a table where what has been updated is random and unkown.

[tomtheappraiser]

Hello all. I'm trying to figure out a better way to explain this, so, I assume, I'll pick the most lengthy and all over the place explanation. ;(. Here goes:

I have a database I use for appraising real estate that uses data the county assessor and collector produces annually. All of the fields in the (my) main table (in my database) deal with the generalization of each parcel. So lot size, total square footage of the bulding(s) in that parcel, zoning, taxes dues, etc are examples of the fields and the data they contain. There are hundreds of thousands of records.

Every year the county assessor releases all of the current data on the properties, as of January 1st of each year. They release it as a CSV in one large big beautiful table. This means I have to convert it all to access and break it all down to the relational tables in my database and update the changes made from last year.

Now, because this is the county, they are lax in their referential integrity over just about everything. SO there might be a property that sold to Acme, LLC and another parcel that the same company owns will be under the ownership of Acme LLC or another Acme L L C all separate records. I despise this time of year and the time it takes me to clean up their mess of a data sample. I mean it is exactly how bad you think it could be. So I'm trying to shave that down this year (and hopefully develop some sort of template to do it again every year in the feature I am still living the appraisal dream).

So, I guess I'm going to post a series of questions on this forum asking for the solution to one related grouping of issues at a time in each post

Because of this, in this post, I am going to explain, at length, how the data arrives and how I have broken this data up so that it is usable to me. This way, I'll reference this post at the beginning of each post/question I make concerning my overall problems, i. e. getting their data efficiently into my database, so that I don't have to repeat all of this everytime I post a question here.

That being said here is the background on what i receive. They only release this information every 2 years (because real property is only reassessed every 2 years). Every single year they released it in a completely different way. They hide the link for downloading this data making it difficult to find every cycle.

This time they released it as a json file. In prior years they had released a group of csv with tables broke down already (one CSV has all of the assessments for each parcel, one the owners, the other a main workbook, etc). I have no idea how json files work, but it is the only way i can find they have released ANY data for 2025.The file names, type and size are listed in the excel sheet that begins with the name screenshot* in the attachments. The only file I could figure out how to open was the .dbf file so that is where all of my data for this year is coming from. I took that .dbf file and converted it to a CSV file where I made some minor adjustments so that it would be able to be brought into my database as one table I called tbl2024AllParcels. Once in my DB, I then created a table that had just the parcel number as a GroupBy and it's generalized fields.So, as an example, I added a field called OwnerID which links it to a table called tblOwners, etc. This table is called tbl2024ParclesDistinct.



The way I have my database setup is, I have this overall table that summarizes all of the dependent tables information. It's called tblAllParcles. The indexed field is an autonumber field called AllParcelID. While this is unique, the county's PacrelNumber field is the indexed field the county uses. So I have AllParcelID as my indexed field because there could be parcels that have multiple cards.

The first issue I would like help with is updating the main table without knowing what has been updated. As I noted, the data I got from their current data released has most of the parcels I currently have in my DB, but some element might h ave changed. I don't want to update all of the table tblAllParcels with blanket update of each record because I might have data from prior releases that this rlease doesn't have. So, for instance, parcel 36Y07623 might have had a family room addedd, thereby increasing the square footage from 1,200 SF in 2023 to 1,800 SF in 2025. I need to update the new square footage for that parcel, whereas I don't need to update other records with the new data bhecause t hey haven't changed. Also, there might be someparcels that they don't include any iformation on, that I already have, so i don't want to update the records with that missing data.

I know this is a convultuted way of asking a much simpler question, bt I can't think of it right now, so i will start with this post. Along with the spreadsheet with the file list, I am including a zipped copy of this years CSV data and a copy of my database with only the structures with no data listed because of t he sheer size of the DB.

[June7]

I am afraid can't avoid updating all records or at least comparing existing records with import to determine if something changed. And preventing update with incoming Null adds complication. I presume the import could include newly created parcels. Perhaps a parcel was subdivided which means the original parcel no longer exists. Another complication in your data validation.

Start with a review of https://stackoverflow.com/questions/...crosoft-access

[CJ_London]

I presume you have a primary key for lot number - plot or parcel number perhaps? In which case perhaps have a history table rather than overwriting existing data

with regards owner, it is a common problem of different spellings. As a first step, create an alias table. In the first field put the name with all spaces and punctuation removed and in the second field the name as is. You may need to standardise some wording such as as Ltd and Limited or remove them altogether. I had a client who supplied around 120 McDonald restaurants- they had 20 different ways of spelling McDonald. You could try chatGPT to get a solution using regex.

There are a number of JSON parsers out there you can use - seem to recall access has a facility to import JSON.

[Micron]

Maybe this will help with the json
https://isladogs.co.uk/json-analyse-...orm/index.html