Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    DCOUNT (Access 03 SP2)

    I can't get any form of COUNT or DCOUNT to work on my query. I've attached a print screen of the data. I want to be able to reflect the number of times the particular afq appears in the data. Seems simple. Why can't I get it to work?
    Attached Files Attached Files
    thanks
    christine

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

    Re: DCOUNT (Access 03 SP2)

    As far as I can tell, each afq occurs exactly once. So I suppose I'm missing something, or you haven't provided all information...

  3. #3
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DCOUNT (Access 03 SP2)

    I didn't want to bog the system down. In fact, I don't think it would fit at all. The query returns over 200,000 rows. I just gave a sample.
    thanks
    christine

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

    Re: DCOUNT (Access 03 SP2)

    Does this expression do what you want?
    <code>
    DCount("*","TableName","afq=" & Chr(34) & [afq] & Chr(34))
    </code>
    Replace TableName with the name of the table (or query) your query is based on.

    Note: with 200,000 records, this will be very slow.

    You could also create a Totals query that has only the field afq twice, the first with Group By as Total option, the second with Count.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DCOUNT (Access 03 SP2)

    I'm not getting anywhere fast. What I want to have happen is a new calculated field that shows how many times that AFQ appears. I've adjusted the sample to show this. For example, in the 1st one (1.13) that particular AFQ shows up 30 times out of the 200,000 records. In excel, this would be a simple COUNTIF(RANGE,CRITERIA). The problem you know is the 200,000 records. Maybe your DCOUNT formula works, but due to the size, you right, it's too slow to use. The totals are working, so maybe I'm doing something wrong there. I'm at a loss. Any ideas, Hans? THANKS!!!!!
    Attached Files Attached Files
    thanks
    christine

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

    Re: DCOUNT (Access 03 SP2)

    Try the following:

    1) Create a query based on your table.
    Select View | Totals or click the Totals button on the toolbar.
    Add the afq field to the query grid twice.
    Don't add any other fields to the query grid.
    Set the Total option for the first column to Group By (this is the default anyway).
    Set the Total option for the second column to Count.
    Save this query as qryCountAFQ.
    If you open the query in datasheet view, it should display the count for each afq in a reasonable amount of time.

    2) Create a query based on your table and on qryCountAFQ.
    Join them on the afq field.
    Add all fields from the table that you need to the query grid.
    Add the CountOfafq field from qryCountAFQ to the query grid.
    This query should display the count of afq's.

  7. #7
    2 Star Lounger
    Join Date
    Aug 2001
    Location
    Boston, Massachusetts, USA
    Posts
    167
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: DCOUNT (Access 03 SP2)

    Hans to the rescue as usual. Thanks yet again. I keep forgetting to step back and nest my queries. I try to accomplish everything in one rather than serveral. Thank you - thank you - thank you as always.
    thanks
    christine

Posting Permissions

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