Prev: Douglas Steele, Arvin Meyer, Tony Toews, John Spencer, Duane H
Next: Hide or Disable Add New Record Button
From: John W. Vinson on 27 May 2010 19:06
On Thu, 27 May 2010 13:07:19 -0700, Dan <Dan(a)discussions.microsoft.com> 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]
From: Dan on 27 May 2010 20:05
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.

From: Dan on 29 May 2010 08:43
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;

From: KenSheridan via AccessMonster.com on 29 May 2010 09:27
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/Forums.aspx/access/201005/1

From: Dan on 30 May 2010 17:14
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.
First  |  Prev  |  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