CODEWITHSUNDEEP
sqldatabasems-access
user3229528
user3229528

Reputation: 29

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.

Upvotes: 0

Views: 3493

Answers (2)

j03z
j03z

Reputation: 171

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.

Upvotes: 0

Menno
Menno

Reputation: 12621

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

Upvotes: 3

Related Questions