Help with ZIP Code field (1 Viewer)

[seamusnboo]

Hi. I'm a newbie to access so I was wondering if somebody could help me with this issue. We're trying to construct a database with information from visitors of a park. The information the visitors provide include the ZIP code where they're from. This information is stored on a field called ZIPCode. We're trying to create a report of the percentage of visitors from each region based on the ZIP Code. The first digit of each zip code represents the region they're from. For example the zip code 12345 belongs to region 1. We're having a problem creating a query and getting access to read only the first digit of the zip code to determine the region. How can we do that?

Thanks

 

[sportsguy]

create a query that strips off the first character, using the Left([FieldName],1) As RegionID function in a new query field. Then create a second query based upon the first query and link the RegionID to the Region Table with the PK as 1,2,3,etc

sportsguy

 

[ScottGem]

First, create a query adding a column with the expression:

Region: Left([ZipCode],1)

Then create another query using that query as your source. Make the Region column the first column and the Zipcode the second. Set the Region column to GroupBy and the ZipCode column to count. This will give you the totla from each region. You can then do a report on this and divide by a count of all records to get the percentages.

HTH
Scott<>

 

[seamusnboo]

Thanks for the reply. But I still have a couple questions, ScottGem. I created the first query as instructed with the expression Region: Left([ZipCode],1). I also created the second query, and made the first column Region. You said to create the second colum ZipCode. But, where is the source of this ZipCode? The query I created or the table where the data is from? You also said to set the first column to GroupBy and the second to count. How do I do that?

Thanks again

 

[ScottGem]

Thanks for the reply. But I still have a couple questions, ScottGem. I created the first query as instructed with the expression Region: Left([ZipCode],1). I also created the second query, and made the first column Region. You said to create the second colum ZipCode. But, where is the source of this ZipCode? The query I created or the table where the data is from? You also said to set the first column to GroupBy and the second to count. How do I do that?

Thanks again

Sorry I wasn't clearer. The first query should include the Zipcode field as well. In the second query, you place the Region first and the ZipCode second. To turn a query into a Group By query you press the Sigma icon on the Toolbar. This adds a GroupBy row to the query design where you can select Group By for Region and Count for Zipcode.

HTH
Scott<>

 

[seamusnboo]

Ok, done that and it seems that we're almost there, lol. It will give me the count of each region, but it is multiplied by the total number of entries. For example, I have a total of 29 entries. The region 9 occurs only one time, and my query shows region 9 with a count of 29, istead of 1. And the same happens to all the other regions. Why is that happening?

Once again thanks.

 

[seamusnboo]

Ok, nevermind, I just figured it out. Everything is working perfect now.

Thanks a lot.

 

[ScottGem]

glad to assist