Re: Queries when the "one" table is used several times
- From: "Allen Browne" <AllenBrowne@xxxxxxxxxxxxxx>
- Date: Sun, 4 Sep 2005 15:38:12 +0800
The structure you have might be the best way to do it.
Or, it might be better to use a related table with fields:
Location the name of the city
LocationType One of the values 'Origin', 'Destination', etc.
Assuming we stay with your existing structure, that's the right way to
define the relationships.
When you create a query into this table, the problem will be with the joins.
When you add the Cities table, Access might produce 5 lines between Cities
and the original table. That won't work. Delete 4 of them. Then add the
Cities table again, and this time leave a different field linked. The query
will alias the 2nd copy of the table as Cities_1. You can right-click this
table in the upper pane in query design view, choose Properties, and give it
any alias you wish, such as DestinationCities.
The other likely problem occurs where some of thest fields are null, i.e.
there is no city. The default join type returns only the records that have a
match in all tables. To change that, double-click the line joining the
tables in query design. Access pops up a dialog with 3 choices. Choose the
one that says:
All records from [OriginalTableName], and any matches from Cities.
By making outer joins like this, the query returns all records from your
original table.
If the idea of outer joins is new, see:
The Query Lost My Records!
at:
http://allenbrowne.com/casu-02.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Rich" <Rich@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:71CB6B37-296D-4E17-B173-815298C526B2@xxxxxxxxxxxxxxxx
>I have a project where my "one" table has thousands of unique cities. In
>this
> table, I use a city code in the one-to-many relationship with the "many"
> table. Absolutely straight forward here...basic textbook stuff so far.
>
> What's different is the "many" table uses this city table several times
> (several as in five different fields refer to it). For instance, the
> "many"
> table has fields Origin, Destination, From, To, Residence, which are all
> cities and use the city codes to refer to the "one" table. How do I build
> queries in this situation and be able to distinguish between the city
> types?
> I tried a few things but every time the query would not work, I received
> no
> results.
>
> I should mention that I defined the one-to-many relationship as follows:
> In
> the relationships window, I went to the "Show Tables" menu and selected my
> Cities table five times, so they displayed as Cities, Cities_1, Cities_2,
> Cities_3, Cities_4. Then for each table I connecteds the city code with
> the
> appropriate field in the "many" table. Was this proper or not?
.
- Follow-Ups:
- References:
- Prev by Date: RE: How Populate empty fields with zeros..
- Next by Date: wild search
- Previous by thread: Queries when the "one" table is used several times
- Next by thread: Re: Queries when the "one" table is used several times
- Index(es):
Relevant Pages
|
