Query

[Lloyd]

Good Morning Everyone,



I am new to using an Access Forum because I have always been able to look at other threads or posting and work out what I need using Access. I do not use VBA and try to stick to the very basics of Access. I have come upon a problem that I just cant figure out so I am looking for help.

I have two tables, first table has various personal records which includes addresses with zip codes, 450 records. I have a second table that has all the zip codes and counties in my state. I am trying to do a count to see how many people live in each county. When I create a query using the 2 tables and I join them by the zip code fields I get back 699 records. I just can not figure out what I am doing wrong. Any help would be greatly appreciated. Thank You in advance for your help.

Lloyd

[weekend00]

you must have duplicate zip code in your second table.

[Lloyd]

I had thought about that and checked the zip code table and removed any duplicates, there were some due to zip code in two counties.

[weekend00]

that is the reason why you got 699 records from 450 records.
if a people have that zip code, they belong to 2 or more countries.

[NTC]

Lloyd, one can simply perform an AggregateQuery on your 1st table, grouped by zipcode - and set up to count...and this will give you the count per zip code and will total to 450.

But what this will not do is show you zip codes with no record - because of course they are not in this 1st table. You would need to extrapolate that yourself.

Or another approach is to join the 2 tables and in doing so be sure to set it up so it pulls ALL records of 2nd table and those of the 1st table. Your relationship line will be an arrow from 2nd to 1st on the zip code line. This will result in a different record quantity because you have your 450 plus those with no record. Perhaps that is indeed 699 - I can't say via a forum. Lets call this QueryALL

Now make a new AggregateQuery using QueryALL as your source; again group on zipcode and set up to count; now you will have all zipcodes listed and a value for each.

Hope this helps.

[weekend00]

NTC: Lloyd need the number of people live in each county.

if there are same zip code for different counties, even god doesn't how many people live in these counties.

for example:
people zip code
a 12345
b 12345

zipcode county
12345 NY
12334 NN

who can tell how many people live in NY and how many in NN? there are 3 posible answer:
NY NN
1 1
0 2
2 0

but nobody knows which one are the truth.

[NTC]

ah - well counties or zipcodes - - the advice is the same; just replace the word zipcode with the word county.

obviously if you don't record the county in the 1st table - then there is no way to find this information....from that table alone and you will need to do the second method with the join.

as an aside; I am unaware of the alignment of zip to county. Isn't such a thing an approximation? some zips are inside a county while others straddle multiple counties don't they? ... not a field I am that close to.

[weekend00]

All right, suppose tbl_study_names has two fields: study and studyName

Good solution.