A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Many (City) to Many (Zip Code) help



 
 
Thread Tools Display Modes
  #1  
Old May 27th, 2010, 09:07 PM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Many (City) to Many (Zip Code) help

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
  #2  
Old May 27th, 2010, 09:12 PM posted to microsoft.public.access
Golfinray
external usenet poster
 
Posts: 1,597
Default Many (City) to Many (Zip Code) help

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

  #3  
Old May 27th, 2010, 09:56 PM posted to microsoft.public.access
Steve[_77_]
external usenet poster
 
Posts: 1,017
Default Many (City) to Many (Zip Code) help

Dan,

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

Steve



"Dan" wrote in message
...
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



  #4  
Old May 27th, 2010, 10:49 PM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Many (City) to Many (Zip Code) help

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


  #5  
Old May 28th, 2010, 12:04 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Many (City) to Many (Zip Code) help

On Thu, 27 May 2010 16:56:47 -0400, "Steve" 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]
  #6  
Old May 28th, 2010, 12:06 AM posted to microsoft.public.access
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Many (City) to Many (Zip Code) help

On Thu, 27 May 2010 13:07:19 -0700, Dan wrote:

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;

--

John W. Vinson [MVP]
  #7  
Old May 28th, 2010, 01:05 AM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Many (City) to Many (Zip Code) help

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

"Steve" wrote:
Dan,

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


  #8  
Old May 29th, 2010, 01:43 PM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Many (City) to Many (Zip Code) help

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


"John W. Vinson" wrote:
SELECT tblCities.City, tblZipcodes.Zipcode
FROM (tblCities INNER JOIN tblCitiesAndZips
ON tblCities.ID = tblCitiesAndZips.CityID)
INNER JOIN tblZipcodes
ON tblZipcodes.ID = tblCitiesAndZips.ZipcodeID;


  #9  
Old May 29th, 2010, 02:27 PM posted to microsoft.public.access
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Many (City) to Many (Zip Code) help

Dan:

John's query will give you a list of all city/zip combinations after rows
have been inserted into tblCitiesAndZips. For a form for viewing these and
for adding new records to tblCitiesAndZips (don't do it directly in the table
in datasheet view) you'll need two combo boxes set up as follows:

ControlSource: CityID

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

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

And:

ControlSource: ZipcodeID

RowSource: SELECT ID, Zipcode FROM tblZipcodes ORDER BY Zipcode;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

A better option, however, would be form (in single form view) based on
tblCities (or better a still a query based on tblCities which orders the
records by city name) and within it a subform (in continuous form view) based
on tblCitiesAndZips. Link the parent form and subform by setting the
LinkMasterFields property to ID, and the LinkChildFields property to CityID.
In the subform you need just one control, the second combo box above. As you
navigate to each city in the main form you'll see its zipcodes in the subform,
and can add a new one by inserting a row in the subform.

Ken Sheridan
Stafford, England

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

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


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1

  #10  
Old May 30th, 2010, 10:14 PM posted to microsoft.public.access
dan
external usenet poster
 
Posts: 1,408
Default Many (City) to Many (Zip Code) help

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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 07:04 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.