I would like to import a CSV file into a master database once a month. I need a report developed that will analyze that data.
The data will roughly consist of fields such as: Business Name, DBA, Address, Permit Number, Expiration Date. The 'Permit Number' will be unique to each business location & is valid for only 1 year. Because permits must be renewed yearly, the CSV file I import each month will contain nearly 99% of the permit numbers from the previous month. I need a report of the 1% of those numbers that were not previously on the list.
I need to know who the new permit numbers are each month while maintaining a master database. The permit number never changes for a business location, they just renew their permit yearly.
I will manually import the CSV data. The only querying I would need is to know who the new permit numbers were at the time of each monthly import of the CSV data. Each monthly import may consist of importing multiple CSV files. I need to know the new permit numbers at the time of the import as well as look back at previous imports & determine who the new permit numbers were at that time.
Here is a sample file I would be importing each month.
I want to keep all records imported. I don't want to only apend the records with new license numbers to the existing data. This way I can get a historical view of any one business, at any point in time.
I want to do one import a month. When I run the import I want to select multiple CSV files and import them at once. This import will be referenced as the date the import was executed.
I want a report generated, based on that import date, detailing which license numbers are new.
I would prefer this be done in MS Access or functional in Open Office.
Thank you for all of your interest!