Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Count in Query (2000)

    I have a query that I want to group three fields - client Number, Client Name and Advanced days. By adding advanced days, I will get duplicate Client Numbers. What I want to do is do a running count on Client number and only keep the client number where the Advanced Days is the highest. I thought I could sort on Advanced days - descending order. If I can make a calculation where it assigns a 1, 2 to the client number and then have the criteria =1, I would get 1 client number with the highest number for Advanced days. My problem is, I don't know how to do the calculation. Am I completely off base. Help.......

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

    Re: Running Count in Query (2000)

    I'm confused. You start by saying that you want to group three fields - that made me think that Client Number, Client Name and Advanced days were fields from the table(s) the query is based on. But then, you say that you want to assign 1, 2, ... to Client Number, which would make it a calculated column.

    Does the following do what you want?
    - Create a query based on your table.
    - Add the three fields you mention (this assumes that Client Number is a field in the table)
    -Select View | Totals.
    - Set the Total option for Advanced Days to Max (leave it as Group By for the other two fields)

    If that is not what you want, could you try to explain again?

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Count in Query (2000)

    Thanks Hans. That worked. These fields were part of a bigger query. I thought if I took the advanced days out of the larger query and got it down to one Advanced days to one client then join that query to the larger query on a one to one join that would be a solution. Since they only wanted to see the highest number for advanced days per client that worked perfectly. Thanks.

Posting Permissions

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