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.
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.