Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals query (Access 2000)

    I want to relate two queries that are not connected namely qryMonthlyGain and qryMonthlyOutlays into one query called qryGrossmargin.However the results repeat themselves many times and i do not know how to build this query
    Is it possible to do it with a query ?

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

    Re: Totals query (Access 2000)

    You must join the two queries on afid. But since your database does not contain a form, it is impossible to test anything.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    I am sending you the example with the form where the query is placed.The list box based on this query does not show the results.

    regards

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totals query (Access 2000)

    The query qryGainBas has a quite a lot of criteria. I could not see the logic of them.

    When I cut them back to just these it seemed to me to work.
    Regards
    John



  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    if i cut that way, what will remain for the next query ? I am sending the attachement. For example,for december there should be results for the frist customer, but nothing appears.

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totals query (Access 2000)

    You do have an order for the first customer in December, but it has no Order Details records, so it does not appear.
    When I add one of them it does appear.

    Look in qryGain, which has no criteria, and you will see only two records.
    Regards
    John



  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    I am really sorry i cannot solve my problem.Please do apologize me for giving the false data. I have copied now real data and you can see in the form my problem. I will be so grateful if you correct it and send me the improved solution. Would you send back my attachement with the suggested solution ?

    Thank you for the kind attention

  8. #8
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totals query (Access 2000)

    This new one still has only 9 records in Order Details, and they all relate to afid 1. They are all in January 2007.

    So it still seems to me that if you change the query criteria as I suggested earlier it works OK.
    (When I click the right month and customer, data appears in the listbox. Whether it is the right data I don't really know.)

    Also in qryGain you have a criteria of true for the field PaymentID. I don't know what that means as PaymentID is a number.
    Regards
    John



  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    You will do me a favour if you send back my attachement with the correct answer. So far i cannot understand. I have changed the query criteia as you suggested.You cannot click on the right customer.There is not such a control on the form . The customer belongs to afid Nr 1. You must click on the option 1 which is afid 1 and then on the month january.Because all the records are from afid 1 and from january.
    So may i ask you to send me back the corrected version ? You obviously are able to solve it, I cannot solve my problem and it very important for me
    Thank you in advance for everything

    kind regards

  10. #10
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totals query (Access 2000)

    Here is my version.

    I don't understand what you mean by "there is no such control on the form?"
    Regards
    John



  11. #11
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    Thank you for the reply. To my regret in your version also the form gives the same errors.If you click on the first option, that is afid 1 and january you get one and the same result 10 times.Maybe the totals query is not right ?

  12. #12
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Totals query (Access 2000)

    I get 15 results. They all have the same number in the first column, but different numbers in column 2.

    I have no idea you what you expect to see , and what it means.

    I think you need to explain from the very beginning, what you are trying to do.
    Regards
    John



  13. #13
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals query (Access 2000)

    Thank you ! Yes you are right and i want to thank you for that.The form works fine the way you have suggested.Just to inform you that i have found my errors and thank you for your great help

Posting Permissions

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