PDA

View Full Version : linking to multiple tables Access 2000



tvg70
14-10-2005, 11:22 PM
Hello,

I want to link one table to multiple tables in Access 2000.

I can best explain this by showing what is in the multiple tables.

Table 1
Drawing Number
Description
No of sheets

Table 2
Drawing Number
Description
No of sheets

Table 3
Drawing Number
Description
No of sheets

Linking Table
Street

Explanation:

I want to link various drawings which have unique numbers sequence to a location eg street so that I can do a search to find all drawing to that location from multiple tables. Some of the drawing will have multiple locations ie more than 1 street location.

Can this be done using multiple tables or will I have to merge or the drawings into 1 database.

Has anyone got any ideas!!!

Any replies will be apreacatied.

Terry

qyiet
15-10-2005, 12:14 AM
I would add a number called streetID to each of the tables of drawings, and store a reference to the street in question's data there.

Table:Streets
ID (autonumber)
streetName (text)
streetDescription (memo)
... (any additional info about your street, like average house price or whatever)

Then add a streetID to the "Table X" tables

Table:Table1
ID (autonumber)
description (memo/text)
numberOfSheets (number)
streetID (number)
... (any additional data)


Table:Table2
ID (autonumber)
description (memo/text)
numberOfSheets (number)
streetID (number)
... (any additional data)


Then create a union query on table 1 and 2 where streetID = (whatever the streets.ID for the street you wanted is).

Hmm.. I wanted to create a more detailed description, but I think I'm getting a bit tired for this.. I promise I'll come review this again in the morning terry.

-Qyiet

beama
15-10-2005, 09:30 AM
From what infomation you supply you could do this with two tables.

One holding image inforrmation using a primary key identifing each image which in turn becomes a foreign key in the secound table which links the image to a address in this table. The address feild in this table would also have a primary key.

Foreign keys are not primary keys ( in a foreign table ) therefore they can be repeated in another table but not the table they are native to.

You would have to consider how you are going to query the tables to retrieve the data you wish ie by [Street Address] or by [Picture Id].



You may wish to consider the main reason for using a data base

To store data in such a manner that it does not get repeated therefore may become redundant.

andrew93
15-10-2005, 03:10 PM
What is the difference between the 3 tables in your example? Why have 3 tables with essentially the same information? As beama suggested, you may be better off combining these into one table - there is a useful website on table design and database normalisation here. (http://datamodel.org/NormalizationRules.html)
A