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

    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
    Location
    Amsterdam, Nederland
    Posts
    450

    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
    Location
    New York/New Zealand
    Posts
    8
    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
  •