Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report on 2 querys (Access 2000)

    Edited by HansV - inserted <!t>[pre]<!/t> and <!t>[/pre]<!/t> tags to preserve spacing - see <!help=19>Help 19<!/help>

    This is the current situaition. I have one table which records invoices, and on each order, the consultant(person who placed the order) get points added to the account. On another table, I keep track of all the points the consultans uses. What I am trying to do is have a report which shows all the points accumulated by each consultant over a month or year. So for example, I would like to a report to show like:
    <pre>Person A

    Points Awarded
    Invoice# pts
    123 71
    342 98
    345 88

    Point Used
    Date Amount
    01/01/2004 200
    02/03/2004 100

    Person B

    Points Awarded
    Invoice# pts
    129 71
    355 67
    348 129

    Point Used
    Date Amount
    01/05/2004 65
    02/06/2004 120
    </pre>

    This would happen for each consultant. The tables have no relation to each other. Any ideas would be great

    Thanks

  2. #2
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Report on 2 querys (Access 2000)

    This will require a report with 2 subreports; 1 for points earned and 1 for points used. You will need to do this approach, since this way guarentees you will get a listing for each consultant whether or not that consultant has any activity that month in either points earned or used. The report itself is based on the consultants table. The linking Master/Child fields for the subreports would be the Conultant ID field.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  3. #3
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report on 2 querys (Access 2000)

    Hi Mark,

    Thanks for the reply, I have setup my report as you have stated, with two subreports and linking the reports together by consultant ID, but I am still having an issue. For each consultant, I am getting all the invoices and all the point used stored in the db, not just the invoices and points used for that specific consultant. Is there something that I am missing? Any ideas would be great.

    Thanks

  4. #4
    3 Star Lounger
    Join Date
    Apr 2003
    Location
    Calgary, Alberta
    Posts
    327
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report on 2 querys (Access 2000)

    Actaully I got it nevermind [img]/forums/images/smilies/smile.gif[/img] Dumb mistake. The problem was that I types in the master and child fields, but in one of the queries, I was not retrieving the Consultant ID

    Thanks for the help [img]/forums/images/smilies/smile.gif[/img]

Posting Permissions

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