Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Daily Count (2000)

    I have a table where a name and date is entered at sign in and then at sign out the date left is entered. The problem I have is how do I get these records in a table so I can count them by day. I need to be able to convert the data into a report that will show things like how many names on any given day or what is the average number of names for a week that type of thing. I need it to also count the name even if a left date is not entered yet. I hope I am making myself clear. I have attached a sample dB.
    Attached Files Attached Files

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

    Re: Daily Count (2000)

    Hi Bill,

    You don't need to create a table that duplicates the records, it would be redundant. Instead, I would create a table tblDates with a date/time field TheDate. Populate it with the range of dates you want to look at.
    Next, create a query based on tblSignin and tblDates. Add TheDate from tblDates and RecordID and Name from tblSignIn. Don't join the tables, but set the criteria for DateStart to < = [TheDate] and for DateEnd to > = [TheDate] Or Is Null. These fields are not shown.
    Note: to avoid problems with some browsers, I have inserted spaces between < or > and = and [TheDate]; these spaces shouldn't be typed.
    Save this query as tblDateList. It will serve as the basis for other queries. I have included two sample queries in the attached modified version of your database: one to count the number of records per day, the other to count the average number of records per day in a week.
    Attached Files Attached Files

  3. #3
    2 Star Lounger
    Join Date
    Jun 2001
    Location
    Kenton, Delaware, USA
    Posts
    107
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Daily Count (2000)

    Very elegant, thank you. As usual you are right on the mark.

Posting Permissions

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