Page 1 of 2 12 LastLast
Results 1 to 15 of 19

Thread: Query Help

  1. #1
    Join Date
    Dec 2006
    Posts
    30

    Unanswered: Query Help

    I'm working on what I thought would be a relatively simple database and have run into a problem that has me stumped (which is NOT too hard to do). I have designed the attached crosstab query to be used as a source for several reports but can't quite get it to work the way I would like it to.

    The first column pulls values from a lookup table and, as can be seen, skips from FSStatusID 01 to 04. The missing IDs are in the lookup table but don't appear in the query because there are no records referencing them. The problem is that ALL Status IDs need to be visible on the reports, even if they have a count of zero. I've worked on this off and on for several days with no success and hope one of you experts can help me make sense of this.
    Attached Thumbnails Attached Thumbnails query.bmp  

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you post the SQL I'm sure we can fix this - sounds to me like you need a FULL JOIN between the tables for this query to work correctly.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2006
    Posts
    30
    Here's the Access-generated SQL code. Please go slow as I try to follow along with you

    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblMain.FSStatusID, Count(tblMain.RecordID) AS YTD
    FROM tblMain
    GROUP BY tblMain.FSStatusID
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    Thanks!

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ooh, not what I expected, but why not try this:
    PIVOT Nz(format([Date],"mmm"), 0) ....

    Unsure if it will work, but worth a shot!
    Note: Nz stands for "Non-Zero" - so where there's a blank record, let's stick a zero!
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oooh infact, new idea if that doesn't work:
    Code:
    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblMain.FSStatusID, Count(tblMain.RecordID) AS YTD
    FROM tblMain
    FULL JOIN <your other table> ON tblMain.FSStatusId = <your other table>.<joining field>
    GROUP BY tblMain.FSStatusID
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");
    Adjust the "ON" accordingly to show the join between the tables.
    George
    Home | Blog

  6. #6
    Join Date
    Dec 2006
    Posts
    30
    The Pivot NZ idea didn't work, so I pasted your SQL statement into my query. I filled in the blanks and came up with this:

    Code:
    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblMain.FSStatusID, Count(tblMain.RecordID) AS YTD
    FROM tblMain
    FULL JOIN tblStatus ON tblMain.FSStatusId = tblStatus.StatusDescription
    GROUP BY tblMain.FSStatusID
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    Unfortunately, Access chokes and tells me that there's a "Syntax error in TRANSFORM statement." I tried the other field from the status table and it gives the same error.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hmmm...
    Any chance you can post a sample of your database (you can export tables/queries etc by right clicking > export...)

    Remove any sensitive data from the relative tables and stick the copy on here - I will take a look at it if I can

    I've never really used crosstabs before, but I'm happy to give it a crack.
    George
    Home | Blog

  8. #8
    Join Date
    Dec 2006
    Posts
    30
    Here you go. I'm willing to try something other than a crosstab query if you have a better option.
    Attached Files Attached Files

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    And which query am I looking at exactly?
    EDIT: taken a best guess and playing with it now.
    George
    Home | Blog

  10. #10
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just a shot in the dark, but this should return all the records in the tblStatus (I assumme this has the missing items?).

    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblStatus.StatusDescription, Count(tblMain.RecordID) AS YTD
    FROM tblMain
    LEFT JOIN tblStatus ON tblMain.FSStatusId = tblStatus.StatusDescription
    GROUP BY tblMain.FSStatusID
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug"," Sep","Oct","Nov","Dec");

    ??


    MTB

  11. #11
    Join Date
    Dec 2006
    Posts
    30
    Mike,

    I tried your code and Access says "You tried to execute a query that doesn't include the specified expression 'StatusDescription' as part of an aggregate function." I have a vague understanding of what you're trying to accomplish with the JOIN statement but not enough to pick out whatever is causing the error. Please help educate a hapless Yank!

    Thanks!

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    The join criteria looks wrong:
    Code:
    LEFT JOIN tblStatus ON tblMain.FSStatusId = tblStatus.StatusDescription
    George
    Home | Blog

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I think I have your solution
    Code:
    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblStatus.StatusDescription, Count(tblMain.RecordID) AS YTD
    FROM tblMain RIGHT JOIN tblStatus ON tblMain.FSStatusID = tblStatus.StatusID
    GROUP BY tblStatus.StatusDescription
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
    Changes highlighted.

    The combination of our select from tblStatus and a RIGHT join provide us with the correct information.
    More explanation to follow on request
    George
    Home | Blog

  14. #14
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Oops forgot to change the group clause

    TRANSFORM Count(tblMain.RecordID) AS [The Value]
    SELECT tblStatus.StatusDescription, Count(tblMain.RecordID) AS YTD
    FROM tblMain
    LEFT JOIN tblStatus ON tblMain.FSStatusId = tblStatus.StatusDescription
    GROUP BY tblStatus.StatusDescription
    PIVOT Format([Date],"mmm") In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug "," Sep","Oct","Nov","Dec");


    Hi georgev

    I did look at it quizzically as it implies and ID fiels is joined to a text field is that what you meant ?? or somethink else


    MTB

  15. #15
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    georgev, I will have to stop taking phone call, I'm obviously not fast enough, but on the right lines, must be an age thing ??


    MTB

    ps It would also seem I don'y know my left from my right ?
    Last edited by MikeTheBike; 04-27-07 at 10:01.

Posting Permissions

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