Access and Append

Topic

New Reply

Each month I download a bill. Currently, I have seven months of billing information in excel spreadsheets that must be worked. It is getting a bit much to constantly go back to these individual speadsheets to review for changes which will result in credits back to us. I think I can manage them better if they were in one database. And, I will need to update this database monthly w/ the new info that I will download.

I have developed a sample database based on 1 month’s information. (see attached snap shot of database). My question is how best to go about appending additional information each month to this database? (attached is sample data for May, June and July).

Reply | Quote

Replies

Each month I download a bill. Currently, I have seven months of billing information in excel spreadsheets that must be worked. It is getting a bit much to constantly go back to these individual speadsheets to review for changes which will result in credits back to us. I think I can manage them better if they were in one database. And, I will need to update this database monthly w/ the new info that I will download.

I have developed a sample database based on 1 month’s information. (see attached snap shot of database). My question is how best to go about appending additional information each month to this database? (attached is sample data for May, June and July).

I would just import each spreadsheet into a temporary table then use an append query to add this temporary table to your permanent table.

Reply | Quote

Each month there will be multiple repeated ACCN per bill. Do I need to run a Find Duplicate Query on the ACCN and delete all but one of the ACCN occurences?

Reply | Quote

Each month there will be multiple repeated ACCN per bill. Do I need to run a Find Duplicate Query on the ACCN and delete all but one of the ACCN occurences?

Is that what is required?

Reply | Quote

Perhaps you can use a totals query based on the imported table?

Reply | Quote

Now that I know how many duplicates of an ACCN there are how do I easily delete the dups leaving only one of each ACCN? Is there a way to let access do the work vs me deleting them individually?

Reply | Quote

Now that I know how many duplicates of an ACCN there are how do I easily delete the dups leaving only one of each ACCN? Is there a way to let access do the work vs me deleting them individually?

Are all fields in these records duplicated?
If so you can use aggregate (group by for all fields) and write them to a temporary table.
Then delete all records in the original table and copy them back from your temporary table.

Sorry Hans, i just noticed you suggested this earlier.

Reply | Quote