Martin:
Firstly it goes without saying that you should back up all the tables before
undertaking any restructuring!
1. To combine the 14 tables into one first create a new table of the same
structure as each of the 14. Then run a series of 'append' queries, one for
each of the 14 existing tables which inserts all the rows from the original
table into the new combined table.
What you do next depends on whether each Town is in only one District.
1.1 If so then the Dist column in your 14 tables, and consequently in the
new combined table is redundant as once we know the town we know the district.
The table is non correctly normalized, therefore. Normalization is the
process of eliminating redundancy in a table. This is not merely a question
of efficiency, a table which contains redundancy is at risk of inconsistent
data. You'll find a brief outline of what normalization involves at:
http://www.datamodel.org/NormalizationRules.html
To eliminate the redundancy you would 'decompose' it. To do this first
create a Towns table with columns TownID (an autonumber primary key), Town
and Dist. The reason for the autonumber key column is that town names can be
duplicated. Then run an append query which appends distinct values of the
Town and Dist columns form you new combined table (I'll refer to it as
NewTable for this example) like so:
INSERT INTO Towns (Town, Dist)
SELECT DISTINCT Town, Dist
FROM NewTable;
Next add a TownID column to NewTable. This column should be a
straightforward long integer number data type, not an autonumber. Run an
'update' query which fills the new TownID column with the correct values from
the Towns table for each town:
UPDATE NewTable INNER JOIN Towns
ON NewTable.Town = Towns.Town
AND NewTable.Dist = Towns.Dist
SET Newtable.TownID = Towns.TownID;
The reason for joining the tables on the two columns is to differentiate
between any towns of the same name in different districts. You can now
delete the Town and Dist columns from NewTable as the TownID column is all
that's required to relate the tables.
Next create a Districts table with just one column, Dist, which will be its
primary key, and append distinct values of Dist from Towns into it with:
INSERT INTO Districts(Dist)
SELECT DISTINCT Dist FROM Towns;
You can now create relationships between the tables, relating Districts to
Towns on the Dist columns, and Towns to NewTable on the TownID columns.
Enforce referential integrity in the relationships, and in that between
Districts and Towns also enforce 'cascade updates'. This because toy are
using the district names as 'natural' keys here, so if you ever change a
district name in Districts it will automatically change in the matching rows
in Towns.
If you now join all the tables in a query, Districts to Towns on the Dist
columns, and Towns to NewTable on the TownID columns you should find that you
can return a result table which will look much like NewTable before you
deleted the Dist and Town columns from it. Now, however, there is no
redundancy in the tables so the integrity of the data is protected by having
each 'fact' stored once and once only. Its what John Vinson has described as
the 'granny's cupboard' principle; a place for everything and everything in
its place.
1.2 If on the other hand each Town could be in more than one District, i.e.
a district boundary runs through a town (I once lived in a town where the
county boundary used to do that until it was rationalised; it had 2 town
halls, two MPs etc), then you'd need to introduce a further table to model
the many-to-many relationship between towns and districts. I won't go into
the details of that, however, as I suspect its an unlikely scenario and there
model described above is more likely to be correct.
3. As regards the Population table you almost certainly don't want this 'to
be similar' in the sense that you'd repeat the values from other tables; that
would again be introducing redundancy.
3.1 All you should need is a foreign key column in the Population table
which references the primary key of whatever table it relates to. My
assumption would be that this is the Districts table, so you'd simply need a
foreign key Dist column in Population, but as you haven't actually told us
what the Population table represents as a real world entity type I'm having
to second guess here. If my assumption is correct, though, then by having
the foreign key Dist column in population, each row in that table maps to one
in Districts and via the other relationships to multiple rows in Towns and
NewTable, so once again its just a question of joining whatever tables you
need in a query to pull everything together.
3.2 Its even possible that you don't need a separate Population table at all,
but whatever it represents can be accommodated simply by means of having
columns in Districts. But without knowing just what 'population' means in
terms of the overall model I can't offer more than that at present. So, the
ball's in your court for now.
Ken Sheridan
Stafford, England
Thank you Ken
You say
I should combine them all into one table and include a District column
How do I combine them all?
Similarly the Population table should be related either to your new combined
table or to the Districts table on the key columns; its not possible to say
which is appropriate on the basis of the information you've given, but if you
post more details of what these tables actually represent in terms of real
world entities and how these relate to each other we'd hopefully be able to
advise further.
The header row in district tables is
Dist, Com, No, Title, Family, FirstNames, Address, Town, Pcode, FWO.
The is also an Id column related to Family
I would like the population table to be similar
How would I achieve this?
[quoted text clipped - 29 lines]
How would I do this? I'm new to working with access
