How to organize data resulting in a report that tracks historical changes

[Marcia]

Hello all. Attached is a snippet of a big file I am working on. In my Access self learning, I have already progressed into the creation of a form. I couldn't get my head around on how to manipulate the data and come up with an individual report for each tax declaration (TaxDecNo). The end result should be like an index cataloguing all the historical data pertaining to each tax dec. A tax declaration issued to a declarant in 1974 went through seven revisions (change of ownership, assessed value, area, boundary owners...). In my earlier posts, I talked about 7 tables because there were 7 revisions for about 30,000 tax declarations. One end result that I have already quite finished in my practice is the list of tax decs owned by an owner. Any suggestion would be very much appreciated on how to create a report for each individual tax dec. Thank you.

[mike60smart]

Hi Marcia

Are you asking how to get the Excel data into an Access database?

You stated you had progressed to a Form?

Can you attach your actual Access file?

[June7]

Does this workbook represent the structure of your database? Should not be 7 tables for 7 revisions, should be 1 table with a field for TaxDecNo. If you want the output shown on Result sheet, will have to use a UNION query.

If you want a report to show a single TaxDecNo, then apply filter criteria to report. If you want a report showing all tax decs for a single property, then there needs to be an identifier that carries over to all tax decs. I know my property has a municipal tax record identifier that is independent of owner and year. I don't see that in your sample data.

I don't think the municipality cares about sex of owner (well, shouldn't), at least I've never seen that on any of my tax records.

[Marcia]

Does this workbook represent the structure of your database? Should not be 7 tables for 7 revisions, should be 1 table with a field for TaxDecNo. If you want the output shown on Result sheet, will have to use a UNION query.

I merged all 3 tables and after reading your and Mike's Post, I did some quick adjustments.

If you want a report to show a single TaxDecNo, then apply filter criteria to report.

I will search the web on how to do this and the Union Query.RPTaxDec.zip

If you want a report showing all tax decs for a single property, then there needs to be an identifier that carries over to all tax decs. I know my property has a municipal tax record identifier that is independent of owner and year. I don't see that in your sample data.

Thank you for the suggestion of adding an identifier so I added a TaxDecID.

I don't think the municipality cares about sex of owner (well, shouldn't)

Gender and Development (GAD) and Rights-based planning is the byword today so sex is included in the database. it is interesting to note that 90.90 percent of all tax dec on record are owned by the male specie.

What I also want to do is a report of all properties owned by an owner reflecting only the latest Tax Decs. Something like, a new Tax Dec in 1974, TaxDecID 1974-1001, TaxDecNo 1974-1001 was revised several times in later years, the report should show the latest revision of TaxDecID 1974-1001 which is TaxDecNo 03-0013-00458.

[Marcia]

Hi Mike, please see post # 4, thank you.

[June7]

Regarding your 'latest revision' query, review http://allenbrowne.com/subquery-01.html#TopN