Results 1 to 10 of 10
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summarizing inv records to append to A/R table (Win ME/Access 97)

    I have a table, MovimientosInv, which contains data on sales of items. Each record has the customer number, item number, quantity sold, cost data, invoice number, dates, ect. One invoice number may have various records.

    I need to summarize the MovimientosInv records by invoice number to create an invoice record for accounts receivable purposes. This is getting messy to do with queries. I use one query to select only sales items from the inventory table, then another to summarize on the invoice field, then another to append to the accounts receivable table. I keep creating double records, or the summed fields don't append.

    I thought this would be simple, but it isn't. There must be a cleaner way to do this. What can you all recommend?

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    I don't understand exactly what you are trying to achieve with multiple queries.
    If you had a query that grouped by invoice number and summed the value of the items would that not give you the information you require?

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    Yes, I can get that far; however, when I try to append those records to the A/R table, I lose the sums.

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    Can I open a query in VBA and have access to the fields in the results? I could do that, and build the append record that way. If you can, could you show me a short snippit of code as an example? Thanks.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    Hi George
    Why don't you post the queries you are using and we can get an idea of what you are doing.
    Pat <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  6. #6
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    The act of putting together a document for you to explain what I was doing led me to the solution. It works! It could probably have been done better in VBA working directly from the table. I will change it eventually.

    I find that I end up with many queries, and I don't really know how to organize them. In inventory management, there is what is called a "where-used" file. Does anyone have anything like that to organize queries? What would be useful is a list of all queries saying where they are used, other queries, forms, reports. Do you know of anything like that? Someone in your group came up with a great program for listing the details of tables called Documentor. Is there anything for queries?

  7. #7
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Documenting Queries (Access 97)

    There's a built-in Documenter feature in Access (Tools menu, Analyze, Documenter) which will create a detailed report describing properties of all selected objects in database, including queries. While this is a very useful tool, what the report does not provide is a description of how the objects in database are interrelated. For example, for a query it won't tell you which forms, reports, or controls use that query as a RecordSource or RowSource; you'd have to look at the report generated for each form or report to see which queries may be used by that object. You'd also have to look at any SQL statements used as a RecordSource or RowSource, and also examine any VBA code which may reference the query. For what you want you'd probably have to purchase a commercial product such as FMS Total Access Analyzer which will set you back some $$$ (the Access 97 version of Analyzer is a "bargain" at $199 USD).

    HTH

  8. #8
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Documenting Queries (Access 97)

    I looked at the web page for FMS, and it is a very complete program; however, it is a little steep for me. Thanks for the information.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Documenting Queries (Access 97)

    You might want to look at Speed Ferrett also - it doesn't give you a complete cross-reference listing like FMS (which actually isn't complete as it doesn't show SQL statement references in VBA), but it will find all the occurances of a given text string, including those in VBA. Very useful in renaming things, or checking to see if something is used anywhere, and less expensive than FMS Total Analyzer.
    Wendell

  10. #10
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summarizing inv records to append to A/R table (Win ME/Access 97)

    You may find running things such as this in queries gives better performance compared to code. Whether the difference is noticable is another matter.

Posting Permissions

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