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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
Many (City) to Many (Zip Code) help
|
#4
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|