Results 1 to 3 of 3
  1. #1
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Tricky Report (2003)

    I'm working with a data base that contains three related tables as follows

    Visitee (a list of people being visited)
    Visitor (a list of people doing the visit)
    Visits (a list of Visit dates made by a visitor to a visitee)

    I currently have a report that shows the visitee and the latest several visits in the following columnar format. It's based on a query that returns a record for each visit containing the visitee, date and visitor.The report uses grouping so that the Visitee name is not repeated on the indented lines underneath containing each Visit date and Visitor. [please note that I have used hyphens instead of spaces as it seems this forum deletes multiple spaces when displaying a message.]

    Visitee #1
    --Visit date, Visitor
    --Visit date, Visitor
    etc

    Visitee #2
    --Visit date, Visitor
    --Visit date, Visitor
    etc

    And so on for each Visitee.

    However, I need a report with the same data formatted similar to a spreadsheet with one line per visitee that looks something like this:

    Person Visited---Week of 5 June---Week of 12 June---Week of 19 June---etc.
    Visitee #1------------Visitor-------------Visitor-----------------Visitor--------------etc,
    Visitee #1------------Visitor-------------Visitor-----------------Visitor--------------etc,
    etc,


    I believe I can figure out how to select the date range for each week, but I can't figure out how to get the visitors displayed all on one line because they actually come from separate records in the query.

    I hope this explanation makes sense, as I realize it's rather tricky.

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

    Re: Tricky Report (2003)

    This can be done using a crosstab query with Visitee as row header, Date (by week) as column header and Visitor (using First as Total option) as value field.
    Creating a report based on a crosstab query is not trivial, since the field names (column headers) may be different each time you open the report. You need VBA code to handle this.
    See <post:=134,439>post 134,439</post:> for an example.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2001
    Location
    Escondido, California, USA
    Posts
    1,458
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Tricky Report (2003)

    Thanks Hans, I'll give it a try.

Posting Permissions

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