Need a query to count number of times a customer visits each region

I have a table that has an Account_Number field and a Region field, so it looks something like this:

Account_Number ... Region
 12345678          Region1
 12345667          Region2

I need to count the number of times an account number visits each region, so that the output is something like:

Account_Number ... Nbr_Visits ... Region
 12345678             3          Region 4
 45678923             6          Region 2

So, the account number can occur multiple times if that customer visits different regions in the same month. This isn't really the way I would have set it up, but a 3rd party is requiring this format and I'm not sure the best way to go about it. I'm using MS Access 2010.

Answers


SELECT Account_Number,Region,COUNT(*) AS Nbr_Visits
FROM myTable  
GROUP BY Account_Number,Region

I would do something along the lines of:

SELECT Account_Number, COUNT(*) AS Nbr_Visits, Region
FROM myTable
WHERE Account_Date between **date1 goes here** and **date2 goes here**
Group by Account_Number, Region

I added the date logic so that you can pull records back between a certain time frame. I think in this case you stated a month. This should be a good sample to get you going.


Need Your Help

Passing multiple values to SUMIFS function in Excel

excel sumifs

I have Twocolumns A, B ..I am trying to get the sum of A like below.

Specifying :class of embedded ruby form

ruby-on-rails-3

If I would like to assign a class to my embedded ruby form, like so?: