Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reporting Horizontal and Vertical Data (2K)

    I need to create a report that will display a scheduling grid. (It is currently in Excel, I need to recreate it in Access) Employee Name appears as vertical column against the left margin. The Date is a horizontal row at the top. The junction of each displays, What, Location and ClassCode. Am I going to have to store the data in Access, then push it back to Excel to report?

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Reporting Horizontal and Vertical Data (2K)

    How are you reporting in Excel, with a Pivot table or something else?
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting Horizontal and Vertical Data (2K)

    Currently, users are manually filling in a spreadsheet. There are no formulas or proper fields per say.

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

    Re: Reporting Horizontal and Vertical Data (2K)

    A typical design in Access would probably have a PersonID, ClassDate, ClassID (ClassCode?), and Location. (You could also create a table that showed the class being taught, the date it was being presented, and the location.) With that kind of design, a CrossTab query (like a pivot in Excel) would give you a starting place. But there are some complications with CrossTabs, so you might be better off having a query that simply does a make table for use in your report. Some fundamental questions are:
    - can a person take two or more classes on one day?
    - how many classes does a typical person take in a month?
    - do you need to span more than one month with your report?
    - how do you envision a person entering data - on a form or in a table (not recommended)?
    The real advantage of Access in such situations is that several people can be entering data at the same time, but it does work rather differently from Excel - even though tables look very much like a worksheet.
    Wendell

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reporting Horizontal and Vertical Data (2K)

    This should answer your questions:
    [Background: The schedule tracks Who (Trainer), What (Class Code), their Role, Where (Location) and When (Date).]
    - A Trainer can only be booked for one event per day.
    - It is possible that a trainer is not scheduled for a day. (it may be possible to fill in Zero Length)
    -The report has potential to range over 1Year
    -The Users will do the data entry into a form.

    My main concern is formatting the report. The format is not up for negotiation because they are comfortable with the format and need to have easy readablity. I must display the Date Field as a row at the top, The Trainers name down the left margin. The intersection displays What, Role, and Location.

    I tried to create a crosstab, but encountered two problems: It requires a calculation at the intersection. And, I need it to list three values (Class Code, Role, and Location) at the intersection.

    Am I dreaming to ask this of Access??

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

    Re: Reporting Horizontal and Vertical Data (2K)

    A pivot table in Excel and a crosstab query in Access always use aggregate functions (count, sum, max, ...) to summarize data. Your schedule is quite different. If it's going to range over a year, it's going to have a large number of columns. An Access report is probably not the ideal tool for it - it can be done, but it will have to be built in code instead of being driven directly by a table or query. In this case, Excel seems to be easier.

    Is there a particular reason you want to do this in Access? And how much effort are you willing/allowed/able to put into it?

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

    Re: Reporting Horizontal and Vertical Data (2K)

    Hans' comments are appropriate, but you may still be able to trick Access into doing what you want. (The only significant advantage I can see is the multi-user capability of Access.)

    I presume you are actualy entering What, Role and Location in a single cell, with a carriage return after What and Role. You can do the same thing in a query and return a single value with the CR using string concatenation. And the pivot does indeed involve calculation, but you should only ever have one entry per day per trainer, so the max function should work. One challenge may be if no trainer is scheduled for a given day then you won't get a column for that day - e.g. weekend days. Would that be a problem?

    It would also be possible to build a table in code that would mirror your Excel report and achieve the same results, but it would require a fair bit of effort and code. Bottom Line: Access is capable of it, but you have to decide if having multiuser capability is worth the effort.
    Wendell

Posting Permissions

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