Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calendar report (2003)

    I'm sure this should be simple but I have had a mental block

    I want to produce a week per page calendar that shows peoples birthdays. I have one table with all the days of the year and another with the names and dates of peoples birthdays. I have made a query which joins the two. However where more than one person shares a birthday the date is repeated. How can I produce a report so that the dates are shown once and the names of the people who's birthday it is are shown along side

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

    Re: Calendar report (2003)

    Set the Hide Duplicates property of the text box bound to the date field to Yes.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar report (2003)

    Thanks - that solves one problem but in order to format the page I need to make sure that each 'day' of the calendar occupies the same size space.

    Now days that have four people sharing a birthday are 4 times larger (taller) than those days with 0 or 1 birthday. i.e there are 4 records

    How can I keep the 'day' the same height independent of the number of people who have it as a birthday

    Does this make sense?

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

    Re: Calendar report (2003)

    You'll need a different approach.
    Copy the Concat function attached to <post#=301393>post 301393</post#>.
    Create a new query based on the query you already have.
    Change the new query to a Totals query.
    Group by the birthday.
    Replace the name field by a calculated expression

    NameList: Concat("qryEmployees","LastName","[BirthDay]=#" & Format([BirthDay],"mm/dd/yyyy") & "#","LastName",Chr(13) & Chr(10))

    where qryEmployees is the name of the query you already had, and LastName and BirthDay are the names of the relevant fields.
    Save the new query and use it as record source of the report.
    Use a text box high enough to contain the maximum number of persons with the same birthday to display the NameList field.

  5. #5
    2 Star Lounger
    Join Date
    May 2002
    Location
    Bristol, Avon, England
    Posts
    110
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calendar report (2003)

    Hans

    Once again I take my hat off to you.

    Thanks- It worked perfectly

    Matt

Posting Permissions

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