Code to find record that matches a text in a control box

[Marcia]

Big thank you Davegri. There was no internet signal since yesterday so I travelled to the nearest town where there is signal. Split form? Thank you again. In my initial learning of Access, I did not understand the function of primary key, foreign key and what referential integrity means. I felt so overwhelmed by those terms. I'll get back after looking into the attached database.

[Marcia]

Hi Davegri. I added records in the database that your attached. The information on the owner (Name, Address, Sex) is not stored in the Main Table.

[davegri]

Indeed. Those fields don't need (and shouldn't) be repeated in the MainTable and I removed them in the attached DB. The Owner_FK in the MainTable points to the correct owner in the OwnersTable. Required fields can be extracted from there whenever needed. For example, qReports is now the recordsource for ReportPerOwner. The query combines the tables to get the desired fields. Relational database 101.

I added lookup comboboxes for PreviousNo and TaxDecNo. Added a button to set up a revision for the currently selected record. The currently selected record does not have to be looked up via these comboboxes, it can just be the current record showing on the form.
Tax davegri-v02.zip

[Marcia]

I am now practicing on your latest DB but here are some issues niggling me:
1. How do I quickly get the Owners_PK into the Owners_FK field of the MainTable after importing the existing Excel Files?
2. How do I make a separate table showing all the new and revised TaxDecNo, the names of the owners their mailing address and sex? The structure is exactly like the MainTable, except that the blank records in the fields must be filled. There is an annual report requiring this format.
Thank you.

[davegri]

1. Importing Excel files. Please post an example Excel File here. I assume the Excel files would be imported to populate the Access DB, a one time effort, or will this be ongoing to update the database on a regular basis?
2. Why can't you use MainTable? If you want only revised and new records, you will need to add a date field to hold the latest activity, then the report recordsource query would extract records meeting the required dates.

[Marcia]

Excel import is just a one time thing, structured exactly like the main table but without the TaxDecID. The TaxDecID will be supplied in Access. I will attach the sample file once I am in a proper laptop. Any updating or editing will be done in Access. Yeah, I've done the inserting of names, address and sex in the main table report, magic I say.

[Marcia]

I would now want to create a report on the history of tax decs. It is a catalogue of the changes from an original tax dec to its latest revision or vice versa (History from the latest revision of a tax dec to its origin). I don't know where to start. Any idea please? Thank you.

[Micron]

Do you have history records at this point? The create a query that returns those records. When you have it working as desired, try making a report from it by using the report wizard. You should have some idea of how you want it to look first, e.g. a report for just one tax dec, or report for all tax decs where they are grouped together by tax dec. Don't bother with sorting or grouping in the query as report options will override query settings.

[davegri]

Let's get some basic data structures defined and implemented before trying to process them.
How do you currently uniquely identify an Owner? Isn't there some taxpayer ID? Using the owner name would present difficulties in the event of duplicate names or misspellings.
Also, how will the OwnersTable be populated? It currently holds only 2 names and I would anticipate a few hundreds or thousands.
You also mentioned 200,000 tax transactions. Trying to display that in a single datasheet, as currently designed would be unworkable. Perhaps having the form filtered to a single owner would be more suitable?

[orange]

I agree with davegri --time to get your structures set up(normalized) to meet your requirements. Jumping into physical Access before you have a tested and vetted design to match your requirements will cause you lots of frustration and "workarounds".
Do you have samples of the outputs needed/expected from this proposed database? It may seem impractical, but getting your requirements detailed and structures set up and vetted with test data and test scenarios before getting too deeply involved with physical database is a more efficient strategy for database design and development. A vetted data model is a blueprint for your database design.
Good luck with your project.

[Marcia]

Issues:
1. On populating the Owner_FK of imported records- I solved it by preparing an OwnersTable in Excel then filled the Owner_FK by means of the VLOOKUP before exporting the data to Access. I have a sample data of 500 because the assessor staff are still straightening their excel data of misspelled names and conflicting addresses.
2. The Owner ID are the names. There are about 30K owners of 200K tax dec of which only 2 cases with the same name so I added their middle names. I thought the unique taxpayer ID is the autonumber? The ReportPerOwner is perfect for us.
3. I have a sample of the tax dec report in Excel, I will post it when internet signal is okay as I am using cellular data.
Thank you for everything Davegri, Micron, Orange.

[davegri]

The Owner ID are the names. There are about 30K owners of 200K tax dec of which only 2 cases with the same name so I added their middle names. I thought the unique taxpayer ID is the autonumber?

In that case we will need the OwnerName initially in MainTable. A one time conversion will match the ownerName in the MainTable and OwnerTable to insert the proper Owner_FK into the MainTable, corresponding with the OwnerTable autonumber.

The conversion effort could also set up tables for Address, Location and Brgy, all with autonumber Primary keys and insert those as Foreign keys into the OwnersTable via comboboxes in the OwnersTable Form. That would reduce spelling errors in those fields to Zero.

[orange]

I agree with davegri again. If you're going to redesign, then normalize, use the dbms to manage referential integrity, any cascade updates/deletes that are relevant, and opt for autonumber PKs. You can also have composite unique indexes to manage duplication.
Good luck.

[davegri]

Added tables I suggested in post #27. Also added search combobox for Owner to RPTax form.
Modified the qReportData to accommodate the new tables.
Added a query, qOwnerAddress to get the current owner mailingaddress for the RPTax form.
Added a search combobox for the Owners Form.
Added error handling for most of the procedures I added or modified.
Tax davegri-v03.zip

[Marcia]

That's quick Davegri. You're correct in insisting for tables to record location and addresses. The files given to me are a nightmare to begin with (different spelling of names, multiple addresses, multiple property locations which is impossible...) The encoder was instructed to copy to the letter all info in the tax decs which were prepared during the Underwood typewriter era. The typist typed those relevant info as they sound, never mind the correct spelling. I will integrate the additional tables, meanwhile kindly go over the attached DB containing the sample format of the TaxDec Tracker. In place of the TaxDecID is the OTD_No field. This represents the original number of the property that links all later revisions. It will be a monstrous job checking the previous number of a revised taxdec to what orig number. Also I added a Date Approved field, this will be used in the Annual Query Reports for exporting to Excel. The ReportPerOwner is wrong because it includes the revised taxdecs so please ignore that, your latest post should correct it. Thank you.Real Property System.zip