It shouldn't be too difficult but you'll need to do a little bit of vba programming or manually running of a couple of queries to modify/add records in your data table. Here's a suggestion showing a couple of queries you'd run:
I had a very long description here but I thought it best to just show you an example and explain the example. In the attached example, you would import your excel data into a table called TempImportTable (every time you import the excel data, it would be to a table called TempImportTable.) Then a couple of queries are run which update the data.
After you import the new data, you'd then run the "AppendNewDataFromImportTable" (which prompts you for the DataMonth for the new set of records) or "AppendNewDataFromImportTableEx2WithAutoDate" (which automatically sets each record of the new data to the 1st of the current month.)
The concept with having a DataMonth field in the data table is that you want to 1. Have a DateMonth field to distinguish the dates of the newly imported records. and 2. Make it a date field type so you can do future searches on a range of dates.
Also note: UniquePropertyID in the example table might be your unique property ref number.
Then optionally you would run the 2nd query called "UpdateMatchingDataFromImportTable" which updates the matching unique property ID's from the TempImportTable to the data table (note: I didn't update the DataMonth field in the query for the data table but you can copy the expression from the Append query to the Update row of the DataMonth field for the data table.) Hopefully that makes sense for you to edit the Update query if you want to do this. The SomePropertyData1 or 2 field might be the new "non-empty" data from the newly imported TempImportTable, otherwise, you could modify this query to change your field which identifies the record as "empty" to "non-empty" (again - I hope that makes sense as an example.)
SCRATCH THAT ON THE ORDER OF THE QUERIES - you'd want to run the update query FIRST!, then the Append query 2nd (otherwise you're updating the records you've just imported.) Make sense?
I did this example very quickly to just to show you a couple of example queries. What would be left to do is perhaps design a form with a button to automate the importing of the new data import table and automatically run the queries. I personally like to get fancy on the form and have a "Browse" type button which let's the user browse for the excel import file but you can find some examples of this (not sure but there might be one in the code bank of this forum.)
I hope this helps. The table/fields will most likely be different in your mdb but the running of the queries concept is similar to update your data table.
Last edited by pkstormy; 03-18-08 at 17:50.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)