Results 1 to 7 of 7
  1. #1
    Join Date
    May 2009
    Posts
    4

    Unanswered: Help with data & how to format it.

    I have an excel database that has the following fields:
    Account/Charge Per Item/Qty./Amount/Tax/Discount/Cost/Date/Date of Service/Code/Description

    I need to create "invoices" for each one based on the account number and date because each account could have more than one invoice but on different dates. Each row lists an item and there could be multiple rows based on how many items were purchased but they will have the same date and same account number for that date.

    Any know how to do something like this?

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    An excel database? So is this Access or Excel?

    In any case, what's the problem?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    May 2009
    Posts
    4

    formatting help

    It's basically a list of rows. Each invoice could consist of 1 or more rows and you can tell based on account number but they are just run on rows. Each row lists an individual invoice line item and the price/tax, etc. There are like 50000 rows so to sort them individual out will take forever I need this by Monday.

    I have the database in Excel but I guess I was trying to generate a report by importing it into a table in Access. I've attached to examples of the database as is in Excel (Database) and then sort of an example of how I would like it to be if it's in Excel (test) or if I can make something similiar with a report in Access that would work. I just need to separate each individual invoice based on the account with the line item information and date (and in date order) for 2007.

    Thanks for any help!

  4. #4
    Join Date
    May 2009
    Posts
    4

    Files

    I guess you can't load xls so I uploaded in zipped file.
    Attached Files Attached Files

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ok, so now I understand what you've got. Have you had a look at Subtotals in Excel? At each change in Account Number, SUM the value field. Is that all you need? I am having trouble locking down what it is you want from us?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2009
    Posts
    4

    Wink Thank you!

    I was able to figure out. I just wanted a report that separated out all the line items by account number per day (which would have been each invoice). I imported the data into Access and then was able to generate a report after multiple tries that worked! Thank you for you help! And since I am guessing you are a StarTrek Fan, I hope you got to see the new movie!!

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I have thanks, and I thought it was awesome of course!!

    Glad you got it sorted out.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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