Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL Count argument problem (Access 2K3)

    Hello All!

    I'm having a bit of a problem figuring out how to write a SQL statement to achieve a count of records. Below is the base SQL statement:

    SELECT tblMain.Date
    FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
    GROUP BY tblMain.JobNumber, tblMain.Date
    HAVING (((tblMain.JobNumber)=[Job Number]));

    This query returns 50 records. How can I write a SQL statement that will return '50'?
    I've tried nesting a Subquery:

    SELECT Count(tblMain.Date) FROM ........
    ...WHERE tblMain.Date IN (SELECT......)

    This returns '1301' (obviously a little larger number than expected). Can anyone offer some advice?

    Thanks so much!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Count argument problem (Access 2K3)

    Store the first query (the one that returns 50 records) as, say, qryDates.
    Then create a query based on qryDates that returns the count:

    SELECT Count(*) AS NumberOfDates FROM qryDates

  3. #3
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Count argument problem (Access 2K3)

    Thanks for the tip!

    The problem is, I'm accessing this through ASP.net, which doesn't seem to recognize saved queries. It only likes to access tables directly, best I've been able to tell.

    Any way to word this as one?

    Thanks again.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL Count argument problem (Access 2K3)

    Does this work?

    SELECT COUNT(*) AS NumberOfDates FROM (SELECT tblMain.Date
    FROM tblMain INNER JOIN tblSub ON tblMain.ID = tblSub.ID
    GROUP BY tblMain.JobNumber, tblMain.Date
    HAVING (((tblMain.JobNumber)=[Job Number])))

  5. #5
    Star Lounger
    Join Date
    Apr 2005
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL Count argument problem (Access 2K3)

    It works perfectly Hans!!!

    50 records: it runs like a gem.

    Thank you sir!

Posting Permissions

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