Results 1 to 4 of 4
  1. #1
    mckay3129 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    2

    Count number of records active each day in a range.

    Hi All,

    I'm reletivley new to Access and am working on a project with a User database. Also a first time poster here, so please bear with me!

    I have a database with a large table named tblUsers. All users, active and inactive are located here. While I realize this is not an ideal set-up, it's not feasible to change as there is already a large amount of other forms and tables and queries taht utilize this current setup.

    I need to find a way to specify a date range and have Access come back with the number of active users for each date in the date range specified.


    I've not been able to come up with a solution. So far I've created a query that lists all of the active users for a specified date (singluar) and then the report counts them to give me the result. I've not been able to expand this to the range concept. I initially thought that I could maybe even create a macro that would rerun the query for each date in a specified range and then find some way to store/tabulate the data as it went, but was unable - my knowledge is somewhat limited.


    Any pointers at all would be very helpful!

    Chris

  2. #2
    NTC is offline VIP
    Windows Vista Access 2007
    Join Date
    Nov 2009
    Posts
    2,392
    first; you may want to limit the record set. Since tables grow to infinity you may not want to include records from the 1970s or whatever. So the preliminary thing is to make a simple select query that results in the universe of records that you want. Let's call this query GroupSelect.

    one can group by date in 2 different places: you can do it in a report, using its grouping/sorting feature - or - you can do it in a query by using an AggregateQuery.

    I trust you have a textbook and can look up those.

    Decide which path you want to take and use the GroupSelect query as your record source.

    Hope this helps.

  3. #3
    mckay3129 is offline Novice
    Windows 7 Access 2007
    Join Date
    Aug 2010
    Posts
    2
    Hi,

    Thanks for the reply.

    I''ve already narrowed the recordsource to be a subset. The date range is supplied via form and the form runs a select query that returns all records that were active over the date range.

    How do I, in the report, tell it to group on the date? By this I mean, the fields relating to date are: Active Date and Inactive Date. My date range is supplied via the form and I can't figure out how to get that into thereport so tha the report can "count" for each date in the range.

    Perhaps a small example:

    The select query contains (For Date Range 8/14/10 - 8/16/10):

    Name,ID_Num,ActiveDate, InactiveDate
    John Brown, 0001, 4/5/2009, ,
    Chris McKay, 7454, 9/10/2007, 8/15/2010
    Jim John, 5689, 8/1/2010, ,
    Joni Jone, 7859, 7/9/2010, 8/25/2010

    The report should give:

    Date Census
    8/14/10 4
    8/15/10 4
    8/16/10 3

    Since the date range is user supplied perhaps a very starter question, how do I get it either into a usable variable or into the query report to sort by?

    Chris

  4. #4
    weekend00 is offline I may not be right
    Windows XP Access 2003
    Join Date
    Aug 2010
    Posts
    1,295
    Create a table holding every single date from the first day of your record to the day which you are not going to use this database. for example, if the earliest date in you records is 2/3/2010, and you are not going to use this database in 5/7/2010 then create a table [allDates]:
    [Date]
    2/3/2010
    2/4/2010
    ...
    5/6/2011

    of course, you may put the last day as today, and add when you need more dates.
    an easy way to populate the dates is to copy from excel, it's easy to populate a series of date.
    run the query below, type in FromDate and ToDate, then you will get it.
    SELECT allDates.Date, Count(allDates.Date) AS CountOfDate
    FROM allDates LEFT JOIN UserTable ON (allDates.Date >= UserTable.activeDate) AND (allDates.Date <= UserTable.inActiveDate or UserTable.inActiveDate is null)
    GROUP BY allDates.Date
    HAVING (((allDates.Date)>=[FromDate] And (allDates.Date)<=[ToDate]));

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

Similar Threads

  1. Counting Active Records Between 2 Dates
    By catat in forum Queries
    Replies: 5
    Last Post: 07-28-2010, 10:55 AM
  2. Replies: 7
    Last Post: 07-22-2010, 01:14 PM
  3. Count Dates within Date Range
    By eckert1961 in forum Queries
    Replies: 2
    Last Post: 03-08-2009, 10:58 AM
  4. how to convert week number to date range
    By qwang1115 in forum Access
    Replies: 1
    Last Post: 02-13-2009, 11:35 AM
  5. Continuous Range of Records
    By tigers in forum Access
    Replies: 0
    Last Post: 10-10-2007, 08:36 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