Results 1 to 12 of 12
  1. #1
    Join Date
    Nov 2002
    Posts
    59

    Unanswered: SQL (Join or nested)

    Hi everyone and thanks in advance for your help!!!

    My problem is to combine two quries using SQL instead of MS Access query tool.

    My first SQL statement is:

    SELECT Max(CertDate) AS MaxOfCertDate, CarNo
    FROM tblVset
    GROUP BY CarNo
    HAVING (((Max(CertDate))<=#8/18/2007#))
    ORDER BY Max(CarNo);


    Second one:

    SELECT CertNo, CertDate, CarNo
    FROM tblVset


    What I need to do is to match CertDate and CarNo of second sql query with the MaxOfCertDate and CarNo of first one?

    For example:
    If I find MaxOfCertDate = 12/12/2007 and CarNo= 25 as a result of first query.

    I would like to create a query to get this by using second query:

    SELECT CertNo, CertDate, CarNo
    FROM tblVset
    WHERE CertDate = #12/12/2007# AND CarNo= "25"

    So this will help me to find right CertNo cuz there is more than one CertNo for each CarNo.

    Thanks again.
    Cuneyt

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Are you sure you copiued & pasted statement #1 correctly as that is invalid...

    Anyway - I wouldn't bother making them a single statement in Access. In other RDBSs yes but in Access I would create the two stored queries and then join them in a third stored query. Are you comfortable joining tables\ stored queries?

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Nov 2002
    Posts
    59
    Thanks for quick reply pootle...

    First works with no problem in my database.
    What makes you think it is wrong? - just curiosity...

    You are right it is very easy to join them in a stored query but then my only problem is that date in first query. It is a variable in my ASP code, I don't know how I can assign a value for that variable then?

    That's what I see as an SQL query in third stored query:

    SELECT CertNo, CertDate, CarNo
    FROM tblVset
    WHERE (((CertDate)=#4/30/2007#) AND ((CarNo)="8022"));

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by cuneyt
    What makes you think it is wrong? - just curiosity...
    the ORDER BY clause is invalid

    after the GROUP BY, each row of the result has a different CarNo

    you can't ORDER BY Max(CarNo)

    perhaps you were thnking of ORDER BY Max(CertDate)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Nov 2002
    Posts
    59
    well... that's what MS Access Query Builder created by itself and as I said before that part of the query works perfectly...

  6. #6
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    does the query list multiple or 1 record?
    Ryan
    My Blog

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    you're right, Access will execute it

    shame on Access
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jun 2004
    Location
    Seattle, WA
    Posts
    601
    Quote Originally Posted by r937
    you're right, Access will execute it

    shame on Access
    wow, that doesn't make any sense.
    Ryan
    My Blog

  9. #9
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by rguy84
    wow, that doesn't make any sense.
    yeah, but then, it works anyway -- i suggest you test it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  10. #10
    Join Date
    Nov 2002
    Posts
    59
    rguy84 first query lists one record...

    thanks

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by cuneyt
    well... that's what MS Access Query Builder created by itself and as I said before that part of the query works perfectly...
    My apologies cuneyt. It really should not work - this is now the second example of Access\ JET allowing (what would normally be considered to be) invalid syntax in aggregate queries that I have seen on this board.

    Anyway - using a variable against a stored query is no different to using a variable against a table. They are logically the same. Do you know how to do this against a table? If so - just apply it to the stored query.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2002
    Posts
    59
    Of course... You are right... I was away from computers for too long I guess

    Thanks

Posting Permissions

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