Results 1 to 3 of 3
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Count function in Access (Access 2000/XP)

    I posted here with a question about a week ago, and had my answer in less than 24 hours so I figured if there is anyone on the net that can help me it is you guys!!

    Here is my situation... I have a table with customer name, and 10 additional fields (one for each fiscal year of sales). I want to count the number of fiscal years a customer has made 1 or more purchases. So if a customer bought something from us in 1994, 1995, and 1998 and nothing in any other fiscal years, the number of years that customer has purchased from us is 3 and I want that to add that to a "Frequency" Field in the table using an update query. I know how to use the DCOUNT function to count the number of records that meet a certain criteria for a specified field, but I want to do the opposite. I want to count the number of fields for each record that meet a specific criteria and be able to specify the 10 fields to test for that criteria.

    I hope I explained that well enough that you understand what I am talking about.. If not I will try to clear that up.

    Look forward to any responses.....

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

    Re: Count function in Access (Access 2000/XP)

    To count the number of years with sales, create a query based on the table, add the fields you want to see, plus a calculated field.Say that the fields are named Sales1994, Sales1995, ..., Sales2003 containing the number of sales for the customer in each year. In an empty column, enter

    Frequency: Abs(([Sales1994]>0)+([Sales1995]>0)+([Sales1996]>0)+([Sales1997]>0)+([Sales1998]>0)+([Sales1999]>0)+([Sales2000]>0)+([Sales2001]>0)+([Sales2002>0)+([Sales2003]>0))

    ([Sales1994]>0) is either True = -1 or False = 0; adding these values yields -(number of years with sales); taking the absolute value makes the result nomn-negative.

    Remark: As you see, it is a rather nasty formula. There is no need to change the structure of your table now, but in general, it is better to split the data into two tables:
    <UL><LI>A table with customer information (name, address, phone, ...) and a unique identifier, say an AutoNumber field named CustomerID
    <LI>A table with sales information: CustomerID, date or year, and other information specific to the sales; this table can contain multiple records for one customer.[/list]Such a structure makes it much easier to summarize data, and is more efficient in the end - a single table with 50 years of sales in 50 fields is not very handy <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Count function in Access (Access 2000/XP)

    You rock Hans!!! Your the same guy that helped me out with my last question too. Thanks a bunch.... I can't believe I never thought of that LOL [img]/forums/images/smilies/smile.gif[/img] I was focused around a count function of some sort and never really strayed away from the idea. Thanks again.

    As for the suggestion, that is exactly what I have. Althought I said it was a table that I was trying to get it to work with, it is actaully a query based on another query that is a summary of a few tables I have. I made it a table so that I could play with it and not muck it up and now that I know how to do it I can do it in the query. Thanks again!!!!

Posting Permissions

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