Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Crosstab Issue (Access 2002)

    I am currently running a report that provides me the the details from a time table. (I have attached a spreadsheet to show the format I need).

    How can I accomplish this in Access?

    I've explored the crosstab query, but it doesn't seem to help. I can only get three columns of data and it the options that are listed isn't what I'm looking for.

    Thanks.
    Attached Files Attached Files
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Crosstab Issue (Access 2002)

    You may be able to get a crosstab query to come close to your format, but it wants to do totals in its own fashion, and missing data will give you a fit. We usually solve this kind of problem by creating a temporary table that looks like your desired format. In your case you will probably need code to deal with situations where you don't have a record for a day, or you have only partial records - i.e. somebody starts at 7:00AM and forgets to check out at lunch, or they leave for an appointment and don't return till the next day. Some challenges to think about.
    Wendell

  3. #3
    Lounger
    Join Date
    Jan 2001
    Location
    Pennsylvania, USA
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Crosstab Issue (Access 2002)

    You CAN accomplish this with a crosstab table if you are willing to have four records per person -- in, lunch out, lunch in, out. You can later group these by person to create the report. To accomplish this, do the following:

    First, create a query against the time table that includes the following fields:
    <UL><LI>person's name
    <LI>day of week ("Monday", "Tuesday", etc.)
    <LI>time element ("In", "Lunch Out", "Lunch In", "Out")
    <LI>time element order (1=In, 2=Lunch Out, 3=Lunch In, 4=Out)
    <LI>time[/list]Next, create a crosstab query based on the above query. Group by Person, Time Element Order and Day of Week. Make Person, Time Element Order and Time Element the Row Headings, Day of Week the Column Heading and First(time) the Value.

    Once you have created the query, change the Column Headings property of the query to use the following headings:
    "Monday";"Tuesday";"Wednesday";"Thursday";"Fri day"

    This not only ensures that the columns will be in that order, but also ensures that the columns will always appear, even if there is no time for a given day (i.e. a day off).

    You can then use this query as the RecordSource of a report. In Sorting and Grouping, group by person and sort by time element number. Don't print the time element number, just the time element text.

Posting Permissions

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