Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi all,
    I am creating a database that will be tracking Crime and Trauma Assistance counseling.
    They would like to see their Clinician names, type of counseling, and how many in that day and month were done but they would like it to look kind of like a calendar or at least a way to look at every day and how many were done that day including zero.. (I attached a sample).

    Is there a way to see every day in a month? The control is a [ServiceDate].
    I did a crosstab but I only have 3 fields [Clinician], [Service], [ServiceDate] and it only shows the dates of service not all dates in a date range (month). It would not have to look like the example as long as I could report an every day number.
    I also attached a stripped down version of the DB...
    The queries are called ClinicianProductivity and ClinicianProductivityXTab

    Thank you,
    Vicky
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The way to list all dates in the column header of the crosstab query is to enumerate them explicitly in the Column Headings property. But this is not only very tedious, it's likely to be impossible - your data span a wide date range, and a query can return at most 255 columns.

    If you want to return all dates in a non-crosstab query, you'd need to create a table that lists all dates (I usually use Excel to create the list, then import i0nto Access), and join it to your existing query with an outer join.

    Take a look at Duane Hookom's calendar report samples on Hook'D on Access. Perhaps there's something you can use.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Hans,
    I think the website reference will help me come up with something.
    Vicky

Posting Permissions

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