Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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).
    Attached Files Attached Files

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='bbrown5' post='783443' date='08-Jul-2009 14:39']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).[/quote]
    I would just import each spreadsheet into a temporary table then use an append query to add this temporary table to your permanent table.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='bbrown5' post='783698' date='09-Jul-2009 13:49']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?[/quote]
    Is that what is required?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Perhaps you can use a totals query based on the imported table?

  6. #6
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Jacksonville, North Carolina, USA
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    [quote name='bbrown5' post='783891' date='10-Jul-2009 12:18']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?[/quote]
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •