Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    DDCount monthly and yearly (Access 2000)

    I want to count the number of the new customers for each affiliate per month, per quarter and per year on the basis of the LastUpdate field = True.
    The query must enumerate the affiliates and for each affiliate to show the counted number of the new customers ( lastupdated = true) for each month, for the 4 quarters and for 2007.I know I should use the function Dcount but building the query seems to me formidable
    Can you help me ?


    SELECT Customers.affiliate, Customers.LastUpdated
    FROM Customers
    WHERE (((Customers.LastUpdated)=True));

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

    Re: DDCount monthly and yearly (Access 2000)

    What is the date field that should be used for this?

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

    Re: DDCount monthly and yearly (Access 2000)

    You'll have to create a crosstab query with the affiliate as row header, the month as column header, and the count of a field as value field.
    You can then create a new query based on the crosstab query that adds the results per quarter and for the whole year.

  4. #4
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDCount monthly and yearly (Access 2000)

    Thank you. I am sending you the table with the filed under question
    Attached Files Attached Files

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

    Re: DDCount monthly and yearly (Access 2000)

    You mentioned that you wanted to select records for which LastUpdated = True. That only makes sense if LastUpdated is a Yes/No field, but it is a Date/Time field. A Date/Time field cannot be True or False. Can you explain what you mean?

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

    Re: DDCount monthly and yearly (Access 2000)

    In the attached version, I have removed the meaningless condition LastUpdated = True and created two queries as indicated earlier: a crosstab query and a select query based on the crosstab query.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Mar 2006
    Posts
    195
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: DDCount monthly and yearly (Access 2000)

    But that is exactly what i want ! How did you guess what shall i need ? Thank you so much indeed !

Posting Permissions

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