Re: counting records in a recordset

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 10/22/04 

Date: Fri, 22 Oct 2004 14:33:32 -0400

mgm wrote:
> hello,
>
> I have a query that is supposed to return only 1 record, however I
> recently found that because of an error in the database it can return
> more than 1. So what I need to do is capture if it is more than 1
> record and alert the user to contact the database administrator. How
> can I get the count of records returned by a recordset? Is there no
> way besides looping through with a counter variable?

A better plan is to set up a unique index or primary key so that the
duplicates cannot occur. I cannot get into specifics because you did not
specify your database type and version, as well as the columns in your table
that are supposed to identify a unique record.

In the meantime, a grouping query will reveal the duplicate records:

select col1,col2
from tablename
group by col1,col2
having count(*) > 1

Bob Barrows 

-- 
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Relevant Pages

  • Re: Confirmation Message when merging data from Access
    ... > I have a form letter that is linked to a query with parameters in my ... > database. ... This reply is posted in the Newsgroup; ...
    (microsoft.public.word.newusers)
  • Re: Using IF...Then when merging dates from Access to Word
    ... >> It's also better to do the manipulation with a query in Access. ... >> Please reply to the newsgroup unless you wish to avail yourself of my ... >>> all others to register as the date in the database. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Mail merge in Access
    ... read-only", I have cheked and neither the query that i am reading from, the database i am ... reading from, or the document that i am writting to is read-only. ... Query, OK... ... This reply is posted in the Newsgroup; please post any follow question or reply in the ...
    (microsoft.public.word.mailmerge.fields)
  • Re: How to Query on multiple tables
    ... Never ask a question about how to write a query (or anything else ... This is a horrible database design. ... UNION ALL ... Please reply to the newsgroup. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Access XP Automation of Word XP Mail Merge - Problem
    ... "Cannot open the database" is a catch-all error message that means Word wasn't ... You mention you're linking to a query. ... This reply is posted in the Newsgroup; please post any follow question or reply ...
    (microsoft.public.office.developer.automation)