Prev: Douglas Steele, Arvin Meyer, Tony Toews, John Spencer, Duane H
Next: Hide or Disable Add New Record Button
From: Dan on 27 May 2010 16:07
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
From: golfinray on 27 May 2010 16:12
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
From: Steve on 27 May 2010 16:56
Dan,

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

Steve
santus(a)penn.com


"Dan" <Dan(a)discussions.microsoft.com> wrote in message
news:C724F536-E7D2-4DB4-B326-5908DA59FDA8(a)microsoft.com...
> 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


From: Dan on 27 May 2010 17:49
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



"golfinray" wrote:

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

From: John W. Vinson on 27 May 2010 19:04
On Thu, 27 May 2010 16:56:47 -0400, "Steve" <notmyemail(a)address.com> wrote:

>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 [MVP]
 |  Next  |  Last
Pages: 1 2 3
Prev: Douglas Steele, Arvin Meyer, Tony Toews, John Spencer, Duane H
Next: Hide or Disable Add New Record Button