# Thread: Count function in Access (Access 2000/XP)

1. ## 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. ## 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. ## 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
•