Results 1 to 6 of 6
  1. #1
    BigOnion is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    3

    Need help with a complex report

    Hi,
    I'm looking to create a report and the best way to ask my question is to just explain what I have and what I'm looking to do. (Which I do not know how to do)

    I have a table with three fields for this example:
    Start Date - User ID - End date
    The "End Date" will be null/blank if the user has not ended their trial, but the "Start Date" and "User ID" will always have values.

    I want to create a report that shows how many user per month that looks something like this:



    Month - Count
    1 - 23
    2 - 26
    3 - 28
    4 - 27
    etc....

    So, somehow it needs to look at each record and see if the "Start Date" is older than the month it's working on, and then see if there is a value in the "End Date" and if that date is greater than the month it's working on.

    Can anyone tell me how to create this report/query?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You need to provide clarification around the choice of StartDate (field/object names should not contain spaces or special characters but we can proceed anyway).
    This can be a prompt from the query itself, or user can input a date in a form text field.
    This date can be used in a >= start date fashion (>= 06/25/2016), or you might want to present a list of month names in a form combo box...

    Your query could be as simple as SELECT * FROM tblMyTable WHERE [BEGINNING DATE] >= tblMyTable.[Start Date];
    This would not consider if an end date is present, nor would it restrict the records to "June" based on my date example.
    Last edited by Micron; 10-16-2016 at 07:41 PM. Reason: edit sql
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    BigOnion is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    3
    All the dates are already in the table on each row. There is no prompting the user for a date. The Start Date is when the customer started.

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Firstly, forget about the report. Get the query working and then you can use the report wizard to create it.

    Please provide clarification on what you want to happen:
    - if the start date is this month
    - how far back in history do you want to go
    - how does the end date come into it - if there no end date, if there is an end date and it is this month or yesterday, if there is an end date and it is next month

  5. #5
    BigOnion is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    3
    Thanks for the help.

    I'll try to clarify.
    - If the start date is this month, then that record would only be counted in this months result.
    - I would like it to either just run for this year, or allow the user to input what year they want it to run for.
    - The end date only serves to restrict. So if the start date is 1/1/16 and the end date is 5/1/16 then it would only get counted in months 1-4.

    Examples:
    Super simple example with only one record:
    01/01/2016 3992 05/02/2016

    Report:
    1 1
    2 1
    3 1
    4 1
    5 0
    the rest though 12 is count of zero(0).

    A little more complex example:
    01/01/2016 3992
    02/01/2016 3993
    02/01/2016 3994 04/02/2016
    04/01/2016 3995
    05/01/2016 3996

    Report:
    1 1
    2 3
    3 3
    4 3
    5 4


    Please let me know if this helps.

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2013 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    As far as which months to run it for, set it up with a default (this year) and allow the user to change the from/to months if they choose.

    This would be a VBA routine as each record needs to be read once and added to multiple buckets. Either you could read and process each record with the monthly counts being stored in an array or you could make a loop whereby you run a query for each month selected and write the totals to a temporary table. Then the report would be based on the table.

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

Similar Threads

  1. Complex Report with similar fields
    By zmbaker in forum Reports
    Replies: 8
    Last Post: 07-14-2014, 03:14 PM
  2. Sub report of a complex query/report
    By roar58 in forum Reports
    Replies: 1
    Last Post: 03-11-2012, 08:41 PM
  3. Complex Grouping for Report
    By ss6857 in forum Reports
    Replies: 2
    Last Post: 07-14-2011, 11:42 AM
  4. Complex Year-to-Date Report
    By MHDataJockey in forum Reports
    Replies: 2
    Last Post: 05-11-2011, 10:42 AM
  5. Complex Query/Queries for a Report
    By Rawb in forum Queries
    Replies: 3
    Last Post: 02-04-2010, 07:44 AM

Tags for this Thread

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