Results 1 to 13 of 13
  1. #1
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21

    Advice on how to Build a Specific Report

    Basically I have two separate payments that are stored in the same table, and I need to generate a report showing all payments made in a certain time frame. The two payments do not always fall into the same time frame, so I need the report to only show the payment if it occurred in the specified time frame. I would like the field to be left blank in the report if the payment has not been made or was made outside of the specified time frame.



    My fieldss are basically: Name, 1stPaymentDate, 1stPaymentAmount, 2ndPaymentDate, 2ndPaymentAmount

    I need the report to show all the fields, but leave the dates and payments blank if they fall outside the time frame. What approach should I take?

  2. #2
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    There is probably a way to do this in one query but it would involve more advanced [and more elegant ] SQL.

    For a simpler approach . . .

    Try something like this:
    [You'll have to test your SQL - these are just examples]:

    1. Create QryNames something like this:
    Select Name From [YourTableName]

    2. Create Qry1stPayment like this:
    Select * From [YourTableName]
    Where 1stPaymentDate Between [YourStartDateHere] And [YourEndDateHere];

    3. Create Qry2ndPayment like this:
    Select * From [YourTableName]
    Where 2ndPaymentDate Between [YourStartDateHere] And [YourEndDateHere];

    4. Create a Query like this:
    i. In Query Design - select the above three queries in the order I listed them above.
    ii. Draw Join lines between the Name field in QryNames and the Name fields in the other two queries.
    iii. Dbl-Click each of the Join lines [one at a time ] and select '2' [All rows from Left Table . . .]
    iv. Dbl-Click Name from QryNames and PaymentDate and PaymentAmount from Qry1stPayment & Qry2ndPayment.
    v. Run the query.

    Verify that you are getting the results that you need.

    Build your Report on this Query.

    Let me know if you run into trouble.

    ***Is your Time Frame always going to be predictable [like Previous Month, or Previous Ten Days . . .]?

  3. #3
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    That isn't working correctly for me. The first three queries work correctly, but the one to base the report on does not. I get 39 entries for each unique ID when I need just 1. And the time frame will not always be the same, but I have a form to enter the start and end date and I have the code down to limit the queries to that time frame.

  4. #4
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Did you select Name from QryNames - and then the Payment Date & Amount from the other two queries?

    If so . . .

    It is probably something to do with the Join property that I had you create between the queries.

    Try deleting the join lines [Right-Click -> Delete] - and then recreating them - but just leave them as simple joins without altering the join Properties.

    If that doesn't work . . .

    Do both your join lines have right arrows pointing Away from QryNames?

    Does each Name only appear once in your Table?

    Where does the number 39 come from?
    Is each of the 39 rows for each Name identical to all the other rows for that Name?
    How many names are there in your Table - 39 maybe?

    Let me know.

  5. #5
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    I have tried all three different join properties and I get the same result.

    1 - Yes the arrow points from the names to the payments

    2 - I am actually using the ID instead of the name because there is a possibility for duplicate names

    3 - I have no idea where the 39 comes from, there aren't even that many entries in the table (yet... there will be a ton)

    Ideas?

  6. #6
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Is your database small enough to post here without zipping?
    If not - can you strip it down and post it here for me?
    Change confidential data if you need to.
    Or - make a copy of your DB - take everything out but the Table and the 4 queries & post it here for me.
    I canot unzip stuff where I am.

  7. #7
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    I cannot make it small enough to upload without zipping. Here it is stripped down and zipped.

  8. #8
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Is this what you were aiming at?
    [See attached screenshot].

  9. #9
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    YES! That is exactly what I need it to do!

  10. #10
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    lol . . .

    You should have done exactly what I told you to do!


    In your Payments Report query - I just created joins between
    Payments1.ID -> PaymentsP2.ID
    Payments1.ID -> PaymentsP1.ID

    Then I right-clicked the join lines and selected option 2 in the Join Properties box.

    See the attached screenshot.

    Then - I just opened your Form 'Payments Made Report' - plugged in 01/01/2011 & 12/31/2011 . . .

    And ran the PaymentsP2 query.

    Let me know if this solves your issue.

  11. #11
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    I don't know what I am missing, but I cannot draw those join lines. The only place I can find to connect them is in relationships, where they are already joined and it is not making any difference. Where do I need to do this?

  12. #12
    cbgroves is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Nov 2011
    Posts
    21
    And of course I figure it out after I post, thanks this got me where I needed to be!

  13. #13
    Robeen is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    Mar 2011
    Location
    Tulsa, Oklahoma.
    Posts
    1,596
    Happy to help! All the best!

    Please mark it 'Solved'.
    Last edited by Robeen; 12-12-2011 at 08:28 AM. Reason: Omission.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Need Report Advice
    By Kevo in forum Reports
    Replies: 1
    Last Post: 08-04-2011, 09:22 PM
  2. Command button-Build Query/Report
    By jnb22019 in forum Programming
    Replies: 6
    Last Post: 07-13-2011, 08:14 AM
  3. print a specific report
    By cvolkers in forum Reports
    Replies: 4
    Last Post: 09-13-2010, 11:38 PM
  4. Print a specific record report from a form
    By cynthiacorley in forum Reports
    Replies: 27
    Last Post: 02-08-2010, 06:34 AM
  5. Replies: 3
    Last Post: 01-14-2010, 08:32 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums