Prev: Grouping Form
Next: Access 2000 Slowdown
From: Dan on 9 Mar 2010 12:44
So I thought I figured out how to create relationships and run queries but
now I am running into a new problem. I will do my best to describe it.

I have a table that I am using from prior months with all the Regions,
Sub-regions, cities, and zipcodes. It has multiple months of data. After I
create a relationship between two tables at the zipcode level I then create a
query to fill in the Region, sub region etc, I end up getting 1000's of
duplicates. The table that I am mapping from has many months of data while
the table i'm mapping to has 1 month. How do i tell access to only map the
information once instead of brining over ever instance of a match?
From: J_Goddard via AccessMonster.com on 9 Mar 2010 13:36
Hi -

You have not given us much to go on, but it sounds as if your data is not
properly normalized. When you say that a table contains sub-regions, cities
and zip codes, and then say it contains months of data, something isn't right
(I think).

Can you post the basic structure of your tables, and what you want the
relationships to be? You are getting extra rows in your queries because one
of the tables contains many occurances of the same zip code, when you should
only have one.

John


Dan wrote:
>So I thought I figured out how to create relationships and run queries but
>now I am running into a new problem. I will do my best to describe it.
>
>I have a table that I am using from prior months with all the Regions,
>Sub-regions, cities, and zipcodes. It has multiple months of data. After I
>create a relationship between two tables at the zipcode level I then create a
>query to fill in the Region, sub region etc, I end up getting 1000's of
>duplicates. The table that I am mapping from has many months of data while
>the table i'm mapping to has 1 month. How do i tell access to only map the
>information once instead of brining over ever instance of a match?

--
John Goddard
Ottawa, ON Canada
jrgoddard at cyberus dot ca

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1

From: KenSheridan via AccessMonster.com on 9 Mar 2010 13:58
You appear to be trying to join two tables on non-key columns, i.e. zipcodes,
so the result will that every instance of a zipcode in one table it will join
to every instance of the same zipcode in the other table. This is why so
many rows are returned.

The fundamental problem is that you are storing information redundantly in
incorrectly normalized tables. The tables consequently need decomposing into
related tables.

Postal codes like zip codes are problematic in that, in the short forms
commonly used, they do not generally relate to a single geographical or
administrative unit. While there may be a straightforward hierarchical
'chain' from cities to sub-regions to regions, a postal code may well be
located in than one city or town. This is the case in the UK with the first
part of our alphanumeric postal codes, though not with the full code, and
from previous discussions here I understand the situation to be similar with
zip codes.

So while you can have tables in a set of linear relationship such as:

Cities>----Sub-regions>----Regions

postal codes do not fit neatly into this set of linear relationships.
Instead there is a many-to-many relationship to cities:

Cities---<CityZips>----ZipCodes

From which it follows that a CityZips table is needed to model the
relationship. I've assumed that neither a city nor zip code can be in more
than one of your sub-regions, and that a sub-region can only be in one region.


When it comes to storing individual locations in a table, you can see from
the above that a CityID column (not city name as these can legitimately be
duplicated) is all that's needed as a foreign key in the Locations table.
Sub-region and Region columns in this table would be redundant as these are
determined by the city, so knowing the city is all that's necessary to know
the Sub-region and Region. The introduction of redundancy into the table by
having these columns would leave it open to inconsistent data.

The Locations table, or any table which records specific location data, also
needs a ZipCode column as this is not determined by the city as discussed
above. So you can see that the CityID and ZipCode columns in the Locations
table are in fact a composite primary key referencing the composite primary
key of CityZips, so an enforced relationship can be created between Locations
and CityZips.

Its not as difficult task to decompose an incorrectly normalized table into a
set of correctly normalized tables as might be thought. Essentially it's a
question of executing a set of 'append' queries to insert rows into each new
table. First a DISTINCT set of Region values would be INSERTed INTO Regions;
then a distinct set of Sub-region and Region values in to Sub-regions; and so
on down the line.

Once you have a set of correctly normalized and related tables, queries will
mirror the relationships. In fact, having created the relationships, when
you add the tables to a query in design view Access will automatically JOIN
them to reflect the relationships.

The other point here is that you appear to have two tables to represent
'data', i.e. the fact that one is for prior months and the other for the
current month. Data should only be represented as values at column positions
in rows in tables (it was Codd's Rule #1, The Information Rule when he first
put forward the relational database model back in 1970). Usually a date/time
column in a single table would do this.

Ken Sheridan
Stafford, England

Dan wrote:
>So I thought I figured out how to create relationships and run queries but
>now I am running into a new problem. I will do my best to describe it.
>
>I have a table that I am using from prior months with all the Regions,
>Sub-regions, cities, and zipcodes. It has multiple months of data. After I
>create a relationship between two tables at the zipcode level I then create a
>query to fill in the Region, sub region etc, I end up getting 1000's of
>duplicates. The table that I am mapping from has many months of data while
>the table i'm mapping to has 1 month. How do i tell access to only map the
>information once instead of brining over ever instance of a match?

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1

From: Jerry Whittle on 9 Mar 2010 14:01
There's only one practical way for us to know: Show us the SQL. Open the
query in design view. Next go to View, SQL View and copy and past it here.

Information on primary keys and relationships would be a nice touch too.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Dan" wrote:

> So I thought I figured out how to create relationships and run queries but
> now I am running into a new problem. I will do my best to describe it.
>
> I have a table that I am using from prior months with all the Regions,
> Sub-regions, cities, and zipcodes. It has multiple months of data. After I
> create a relationship between two tables at the zipcode level I then create a
> query to fill in the Region, sub region etc, I end up getting 1000's of
> duplicates. The table that I am mapping from has many months of data while
> the table i'm mapping to has 1 month. How do i tell access to only map the
> information once instead of brining over ever instance of a match?
 | 
Pages: 1
Prev: Grouping Form
Next: Access 2000 Slowdown