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
