Allen Browne has an excellent example of just what you need at
www.allenbrowne.com
--
Milton Purdy
ACCESS
State of Arkansas


"Dan" wrote:

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