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

    Total query (Win ME/Access 97)

    <P ID="edit" class=small>(Edited by charlotte on 22-Jan-03 19:50. to active link to previous thread)</P>Wendell and Gary - This is more on my report in message <!post=216593,216593>216593<!/post>.

    So far, so good. The next payment date query linked to the customer master query is functioning. Now I want to add a pair of Total queries to add the total payments from the Paid Amount field. There are two tables with payments. I need to add all payments made be each customer to get total payments. I made two Total queries grouping on customer number and adding amount paid, which gave me the two required sums; however, I can't integrate these into the original query with the next pay date.

    In other words, I have two queries based on one record per customer, call it CustMst. These work well together. When I make a query which sums multiple records per customer, the query alone functions, but when I add it to the CustMst query, everything falls apart.

    The report is ready, except for those two sums of amount paid grouped by customer.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total query (Win ME/Access 97)

    In the previous thread, you mentioned that you couldn't get the database below 100 KB.
    In the first place, remember that you can't attach a .mdb to a message; you must put it in a zip file and attach *that*.
    To give Loungers an idea of what you're trying to do, you don't need to provide the entire database.

    - Make a copy of the database, work with the copy.
    - Remove all forms and reports.
    - Remove all tables and queries that are not relevant to your problem.
    - Leave only just enough records in the remaining tables to illustrate the problem.
    - Remove or modify sensitive information.
    - Compact the database.
    - Zip the database.

    If this doesn't get the size of the zip file below 100 KB, you can try decompiling and then compacting the database. Do a search for Decompile in this forum if you don't know what decompiling is. I can't imagine that the zipped database will still be too big then.

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

    Re: Total query (Win ME/Access 97)

    Here is the reduced data base.
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total query (Win ME/Access 97)

    George,

    I suspect that you need to use a left join from QClientes to the other queries instead of an inner join. An inner join will only return records for which the join field on both sides is equal and non-empty. Since QSumPagosCurr and QSumPagosHistoria have records for only a few NumClientes, the query combining all these will also have records for a few NumClientes. A left join from QClientes to the others will return a record for each NumCliente in QClientes, together with corresponding data in the other queries if available, null otherwise. To edit a join, double click the line joining two queries; select the option where *all* records from QClientes are returned. I have attached a screenshot of what it looks like after you have done this.

    If this is not what you need, post back and explain in more detail where "it all falls apart".
    Attached Images Attached Images
    • File Type: png x.png (4.2 KB, 0 views)

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

    Re: Total query (Win ME/Access 97)

    At first glance, this looks like the solution. What a simple solution for my headache! I will check it out more thoroughly, but it looks good. Thank you all.

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

    Re: Total query (Win ME/Access 97)

    OK. The totals are now available in the query and on the report. Now, I want to simply add the two totals and get a total total, so to speak. Another problem pops up. If there is no record in the QSumPagosHist, then the total total is blank. There will always be a record in the QSumPagosCurr, but the QSumPagosHist record may not always exist. The control source of the total total field in the query is Expr1: [QSumPagosCurr].[SumOfMontoPagado]+[QSumPagosHistoria].[SumOfMontoPagado]". That functions when there is record in both QSumPagosCurr and QSumPagosHist, but fails when no record exists in QSumPagosHist.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Total query (Win ME/Access 97)

    Try

    Expr1: Nz([QSumPagosCurr].[SumOfMontoPagado],0)+Nz([QSumPagosHistoria].[SumOfMontoPagado],0)

    The Nz function substitutes the second argument if the first argument is null (empty). In this case, we count null values as 0.

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

    Re: Total query (Win ME/Access 97)

    It is done. Thank you and Gary for the help. You guys are incredible! (When do you sleep?)

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Total query (Win ME/Access 97)

    Glad all went well and you figured out the problem. Me, I only sleep sporadically. However, I don't think Hans sleeps at all !!!
    Regards,

    Gary
    (It's been a while!)

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

    Re: Total query (Win ME/Access 97)

    No Gary, you are wrong, Hans does it in his sleep.

    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

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

    Re: Attaching sample db files

    Tho' not applicable in this case, in addition to the suggested steps (all highly recommended), to reduce size further when attaching a sample database I've recently found it can be useful to convert db to Access 97 format before posting (I normally use A2K format). Since A97 does not support Unicode (which requires 2 bytes vice1 byte per character) this can significantly reduce size of the db file. Anyone using A2K or later should have no problem converting the A97 file back to later version.

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Attaching sample db files

    If you used any features that didn't exist in A97, though, anyone opening it in A97 would have big problems. You might want to indicate the versions it actually *works* in in the message accompanying any such attachment.
    Charlotte

Posting Permissions

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