Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2003
    New York/New Zealand

    Unanswered: Sub Query question

    I am having a hard time here... I want to LEFT JOIN a table to an inline view of another table but when I try to save the query access tells me that it is an invalid format... This is what I am trying.

    SELECT Nature.Nature, CINT(NZ(Apr1.Apr, 0)) AS Apr
    FROM Nature LEFT JOIN (SELECT Nature, ComplaintNumber AS Apr FROM NatureCountPerMonth WHERE Month = 4) Apr1
    ON Nature.Nature = Apr1.Nature;

    It doesn't like seeing the second SELECT but as far as I can tell this is a valid SQL statement. What am I doing wrong?

    Also a side note that should probably be in another thread I have also written a TRANSFORM query and would like to generate a report but I can't get access to open the query to design the report... Is this something that is just not going to happen or do I have to do something that I'm not seeing. This is the transform query...

    PARAMETERS [Enter Start Date] DateTime, [Enter End Date] DateTime;
    TRANSFORM NZ(Count(Complaints.Nature), 0) AS CountOfNature
    SELECT Complaints.Nature, COUNT(Complaints.Nature) AS Total
    FROM Complaints
    WHERE Complaints.LetterDate BETWEEN [Enter Start Date] AND [Enter End Date]
    GROUP BY Complaints.Nature
    PIVOT CDate("1/" & Month(Complaints.LetterDate) & "/" & Year(Complaints.LetterDate));

    It works like I want it to but I want to create a report for it and it just ain't happening.

    Thanks for any help,

  2. #2
    Join Date
    Jul 2003
    Amsterdam, Nederland

    Question Why not like this

    SELECT Nature, ComplaintNumber AS Apr ,Nature.Nature, CINT(NZ(Apr1.Apr, 0)) AS Apr
    FROM Nature
    LEFT JOIN Apr1.Nature ON Nature.Nature = Apr1.Nature
    WHERE NatureCountPerMonth.Month = 4

  3. #3
    Join Date
    Oct 2003
    New York/New Zealand
    That doesn't work... Not sure why... Not only that but as far as I can tell the query I have written is a valid sql query. Does access just not like using inline views?

Posting Permissions

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