Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Unanswered: Tricky problem: Crosstab

    Hi All,

    I've racked my brain and search for a while and I still haven't come up with anything concrete. So I figured I'd throw this problem out there to see if maybe the community could give me some help.

    Developing a database to track actuals vs. commitments. The schedule table is structured as so (this is just a conceptual sample):

    project;transMonth;qty
    ProjectX;Jan-2005;14
    ProjectX;Feb-2005;14
    ProjectX;Jun-2005;14
    ProjectX;Oct-2005;16

    etc... and there could be multiple projects. Capturing the data is no problem, it's when it comes time to report on it. People want to see this spread out like:

    Jan-2005;Feb-2005;March-2005;April-2005;May-2005;Jun-2005; etc...
    Project X: 14;14;0;0;0;14;etc

    I tried creating a seperate table that just had month values from now ontill 2010 and then I outter joined the two and created a crosstab query, but there were two problems: a blank project would be listed (so that all months would show up) and I couldn't sort the column headings appropriately 'MMM-YYYY' looks like Jan-2005;Jan-2006;Jan-2007;etc...

    Let me know if I need to clarify anything? Thanks in advance for your thoughts/help!

    Warren
    -Warren
    Hack the Planet.

  2. #2
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Bump...

    I've got a couple ideas. One of which is that if I'm limited simply by Access, then I can utilize Excel as a reporting front end. I need to make some complex charts in the end anyway.

    But I'd still like to know if anyone else has encountered and solved something similar.

    Thanks,
    Last edited by WarrenM; 01-19-05 at 08:21.
    -Warren
    Hack the Planet.

  3. #3
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Hi
    If you format your months as 2005-01, 2005-02...2005-12 etc then they will sort correctly. You could get a query to generate this format from your current month field quite easily (create as a text field).

    If I understand your other problem, you want to see a complete set of months regardless of whether there is any data ? If you create a full set of dummy data:
    dummy;Jan-2005;0
    dummy;Feb-2005;0
    dummy;Jun-2005;0
    dummy;Oct-2005;0
    ..
    ..
    dummy;Dec-2010;0

    Then run your cross-tab and then filter out the dummy line.

    hth
    Chris

  4. #4
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    I already tried the month format like that and I ran that idea by the users and they didn't like.

    However, I didn't think to filter at the end reporting level... I was too hung up on trying to figure it out at the query level; great idea thanks.

    -Warren
    -Warren
    Hack the Planet.

  5. #5
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Well I don't think it's possible to filter at the report level? But what I did figure out is that I can filter out the dummy project with another query utilizing the crosstab query as the source...

    The next trick part is to ensure that the Dates being used are always accurate, as we run on different fiscal months here and there is no access to the calendar electronically. I'll just have to store the fiscal month as user input and run off of that.
    -Warren
    Hack the Planet.

Posting Permissions

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