Count Occurrences in Access

[Guest]

I have a sheet with a SUMPRODUCT formula that works great. The problem I am
running into is the row limitation in Excel. Is there a way to do the same
kind of task in Access? I am actually performing a query in Access first
that pulls the fields I am working with, and then linking that into Excel.
The Excel formula is
=IF($D3="SA",SUMPRODUCT(--($A$2:$A$19=$A3),--($B$2:$B$19<$B3)),"").

An sample of my data is below:

A B C D
Customer Contact date Campaign Code Appointment Result Code
A0500751 20060330 MC0001 PR
A0500751 20060410 AA0001 TS
A0500751 20060412 DM2625 SA
A0500751 20060412 DM2625 SA
A0500751 20060417 HK0002 FT
A0500751 20060421 HK0002 FT
27200841 20060207 PR0002 RR
27200841 20060418 DM2625 SA
27200841 20060418 DM2625 SA
27200841 20060418 DM2625 SA
27200841 20060418 DM2625 SA
27200841 20060420 HK0001 TS
27200841 20060629 AA0001 RT
27200845 20060207 PR0002 RR
27200845 20060418 DM2625 SA

What I would like to accomplish is get a count of the number of visits a
customer had before the sale (Appointment Result Code=SA). For example,
Customer A0500751 would have a count of 2 and 24200841 would have a count of
1 and so on. Can this be done in Access?

 

[Jeff Boyce]

Create a new query. Add the table. Add the Customer field and the
ContactDate field and the ApptResultCode field.

Uncheck the "display" checkmark under ApptResultCode, then enter "SA" as the
criterion. If you run this query, you get all the records that are "SA".

Next, click on the toolbar button that does "Totals" queries (it looks like
the greek letter sigma).

Use "GroupBy" for Customer, use "Count" for ContactDate, and use "Where" for
ApptResultCode. Run it. You should get a count of ContactDates per
Customer, where ApptResultsCode = "SA".

NOTE: this depends on a customer not being contacted more than once on the
same ContactDate. If that can happen, you might want to consider adding a
unique row identifier (that you could then "count").

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Guest]

Jeff

Thanks for the response. I may not have been clear in what I am trying to
get. I would like to get a count of the visits that happened before the
sale. If you look at customer A0500751 from my original post you will see a
total of 6 rows (visits). What I would like to get is a count of the visits
before the two sales records that occurred on 4/12, which would be 2 the
records on 3/30 and 4/10. Hope this helps clarify what I am looking to do.

 

[Jeff Boyce]

Mark

When I look at the original sample data, I see two records for A0500751 that
happen before 20060412.

So, you want to count the number of rows where the DateField is Less Than
the DateField value for a "SA"-type record, per each CustomerID?

That sounds like a straight-forward query also.

Regards

Jeff Boyce
Microsoft Office/Access MVP