Many (City) to Many (Zip Code) help

[Dan]

I'm using Access 2007. I’m trying to design a many to many relationship.

3 tables, 2 fields per table:
tblCities = ID(pk), City
tblZipcodes = ID(pk), Zipcode
tblCitiesAndZips = CityID(fk), ZipcodeID(fk)

I have created 2 one-to-many relationships. I want to see all 327
combinations of cities and zip codes. I am having trouble setting up a query.

Field: ID
Table: tblCities
Show: Unchecked

Field: City
Table: tblCities
Show: Unchecked

Field: ID
Table: tblZipcodes
Show: Checked

Field: Zipcode
Table: tblZipcodes
Show: Checked

If I try to run this, I get no results.
If I delete the relationships AND the junction table, I do see some results,
but they’re not exactly what I expected. I see all 327 cities in the left
column, and the FIRST zip code in the right column repeated 327 times. Then
I see the same 327 cities repeated again, with the second zip code, etc. For
a total of 327x327 records.

I have tried to set the Fields and Tables in my query to the fields in the
junction table.
I have tried to write criteria, such as CityID=ZipcodeID or
tblCities.ID=tblZipcodes.ID but I am still not getting the results I’m
looking for.

I would be very grateful for any help.

Thanks.
Dan

 

[golfinray]

Allen Browne has an excellent example of just what you need at
www.allenbrowne.com

 

[Steve]

Dan,

Double check your data; each city should have its own unique zipcode.

Steve
(e-mail address removed)

 

[Dan]

Milton, I found a table of US zip codes at Allen's web site, but I'm sure
that's not what you meant. I already have such a table. I can't find the
example you're referring to. Do you know the document title?
Dan

 

[John W. Vinson]

Double check your data; each city should have its own unique zipcode.

What is the zipcode for Boise, Idaho? There are 12.
What city is zipcode 83660? There are 2.

You're dead wrong on this, Steve.

 

[John W. Vinson]

3 tables, 2 fields per table:
tblCities = ID(pk), City
tblZipcodes = ID(pk), Zipcode
tblCitiesAndZips = CityID(fk), ZipcodeID(fk)

I have created 2 one-to-many relationships. I want to see all 327
combinations of cities and zip codes.

SELECT tblCities.City, tblZipcodes.Zipcode
FROM (tblCities INNER JOIN tblCitiesAndZips
ON tblCities.ID = tblCitiesAndZips.CityID)
INNER JOIN tblZipcodes
ON tblZipcodes.ID = tblCitiesAndZips.ZipcodeID;

 

[Dan]

Steve, it sure would be simpler if that were the case, but it's not.

 

[Dan]

John,
Is this a single expression for the Row Source of a single combo box in
tblCitiesAndZips?
Dan

 

[Dan]

Ken, I appreciate the help. I will try this out as soon as I get a chance.

You would think it is pretty straightforward, but I have read MANY posts
here on this discussion group, to try to figure this out on my own, before
posting here. As well as the Access help files and other web sites with
tutorials, etc.

Part of what is making this more confusing for me is that nobody ever posts
the square brackets, and Access seems to require them. For example, you
wrote:

RowSource: SELECT ID, City FROM tblCities ORDER BY City;

But it seems that Access prefers that I enter something like this:

RowSource: SELECT [ID], [City] FROM [tblCities] ORDER BY [City];

Ok, I'm getting the hang of it myself, but sometimes it requires the table
name, and if I'm specifying a column, like for a bound text box, then even
the word "column" has to be in brackets. For example:

=[Combo24].[Column](2)

Like I said, I'm starting to figure out where brackets are needed and where
they're not, but some posts, like John's query in this thread - I spent hours
trying to get it right in Access, and I just can't figure out where the
brackets go, and where I should be inserting comma's, semicolons, periods, or
exclamation points, between the parts of the expressions.