Queries when the "one" table is used several times

R

Rich

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?
 
A

Allen Browne

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
 
R

Rich

Allen,
Thank you for the help. Problem solved.

If you don't mind, I have another issue where I'd welcome your advice. Next
week, I have to give this file to several people at work. Just the basic
query you helped me finish is complicated and time consuming for people who
have never seen it before. How should I handle this problem? Should I tell
them to Copy/Paste the basic query, then add whatever criteria they need?

I know for a fact if they try to create new queries from scratch, the same
issue that you helped me with will be a recurring headache for me as the
office phone will be ringing off the hook. Any ideas to avoid this?
 
J

John Vinson

Should I tell
them to Copy/Paste the basic query, then add whatever criteria they need?
Click to expand...

No.

Users should never need to see the query design window, or manually
enter criteria onto the criteria line.

Instead, use Parameter Queries. Rather than a criterion of

"Dalles"

on the City_3 field, use

[Enter destination city:]

or, even better, have a Form with a combo box allowing the user to
select a city, and a criterion of

=[Forms]![CriteiraForm]![cboCity_3]


John W. Vinson[MVP]
 
Top