Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count the No of recs in query (Office 2000)

    Is there any way I could count how many records there are in a query or a table (automatically) and display it?

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the No of recs in query (Office 2000)

    You can use a Totals query and Count in the Totals row for the key
    or in SQL, use:
    SELECT Count(tablename.keyfieldname) AS CountOfkeyfieldname
    FROM tablename;

    You can display it by referencing CountOfkeyfieldname.

  3. #3
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the No of recs in query (Office 2000)

    SELECT Count(AppendToDeuville.keyfieldname) AS CountOfkeyfieldname
    FROM tablename;

    Do I relpace the keyfeildname with a feildname? It gives me an error message saying that it cannot find the tablename or query name. I am sure the query is there.

    Does it matter if its an append query?

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Count the No of recs in query (Office 2000)

    Display where, count when? If you're opening a table or query, the number of records displays automatically at the bottom. If you're *using* a table or query and want to know how many records there are, it depends on how you're using it. In a form, you can use the form's recordsetclone to get the recordcount. In a recordset of any sort, you can movelast to get the recordcount. If you want a record count *in* your query, you can use ThomasW's suggestion to count all the records in the query.
    Charlotte

  5. #5
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the No of recs in query (Office 2000)

    What I want to do is count the records and merge it to a word document which am using as a fax cover to let the recepient know how many records Im sending them. This has to be done in a query that Im merging with the word.

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the No of recs in query (Office 2000)

    Sorry, I'll be more clear. If your query is named AppendToDeuville and the key of the underlying table is called DeuvilleID, then the statement is:

    SELECT Count(AppendToDeuville.DeuvilleID) AS CountOfDeuvilleID
    FROM AppendToDeuville;

    The query I was describing was a Totals query which merely gives you the count of all the records.

  7. #7
    4 Star Lounger
    Join Date
    Aug 2001
    Location
    London, UK
    Posts
    516
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count the No of recs in query (Office 2000)

    Thanx Thomas,
    That is EXACTLY what I wanted to do.
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

Posting Permissions

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