Can you add a table twice to the relationship window

[JRB]

Question:
Can you add a table twice to the relaitonship window? Ex:
Table1 fields
Location
Rescue first due
Rescue Second due
Rescue Third due

Table2 fields
Rescue Company (list of all units in county)

A different recue company (from Table2) must be linked to the fields in in
Table1

The relationships can be created but it adds Table1 again every time a new
link is created.

Thanks in advance for any help.
J

 

[Tim Ferguson]

Question:
Can you add a table twice to the relaitonship window? Ex:
Table1 fields
Location
Rescue first due
Rescue Second due
Rescue Third due

Yes you can: simply continue to do right-click Add Table, and the new ones
will be aliased Table1_1, Table1_2 and so on, which you can ignore.

But you might also like to consider redesigning in order to model the many-
to-many relationship properly:

Rescues
=======
Location foreign key references Table1.Location
RescueCo foreign key references Table2.RescueCompany
Priority integer

constraint Primary Key (Location, RescueCo)
constraint Unique Index (Location, Priority)

The priority field is optional unless you need to keep the first, second,
third order.

This design allows you as many rescues as neccessary, because one time you
are sure to need a fourth one; and you can query for Locations that can be
rescued by companies A and C, etc.

Hope that helps


Tim F

 

[LeAnne]

It can be done...but I'd suggest you pull up and take another look at
your database design first. Now, I have no idea what is meant by "Rescue
first due", "Rescue second due" and so forth. But I can tell you that
whenever you have a table like this that expands "wide'n'fat" with
repeating fields (e.g. Rescue 1, 2, 3: Year 1997, Year 1998, Year 1999;
Station A, B, C), you probably have an irrelational database design.
Properly normalized database tables expand down ("tall'n'skinny"), not
across. Rescues, or Years, or Stations are data, not fields. This
business of adding multiple instances of tables to the Relationship
window (I assume, one instance for each company??? Yeesh.) is another
bad sign.

Ask yourself, what are your entities (real-life persons, places, things
or events) and their attributes (categories of information relevent to
your entities)? At a glance, it looks like you're tracking Rescue
companies and rescues, for starters. But how are table1 and table2
related? On what field(s) are they joined?
I'm assuming that each company can respond to one AND ONLY ONE rescue at
a time, but each rescue can be responded to by one OR MORE companies,
right? That looks like a one-to-many relationship to me. Without knowing
more about what specific information you are trying to get out of your
data, I'd suggest redesigning your tables: tblCompanies (CompanyID,
Location, Contact Info, and other relevent information); and tblRescues
(RescueID, CompanyID, RescueDate, RescueTime, LocationOfRescue,
NatureofRescue). Join these tables on CompanyID. You can then easily
track which companies (or how many companies) responded (or didn't
repond) to a particular emergency, or which companies responded to the
greatest number of emergencies in a given period, or in a given area,
etc. using queries. Post back if you need clarification or have other
questions.

Good luck,

LeAnne

 

[JRB]

Thanks. That help quite a bit.

It can be done...but I'd suggest you pull up and take another look at
your database design first. Now, I have no idea what is meant by "Rescue
first due", "Rescue second due" and so forth. But I can tell you that
whenever you have a table like this that expands "wide'n'fat" with
repeating fields (e.g. Rescue 1, 2, 3: Year 1997, Year 1998, Year 1999;
Station A, B, C), you probably have an irrelational database design.
Properly normalized database tables expand down ("tall'n'skinny"), not
across. Rescues, or Years, or Stations are data, not fields. This
business of adding multiple instances of tables to the Relationship
window (I assume, one instance for each company??? Yeesh.) is another
bad sign.

Ask yourself, what are your entities (real-life persons, places, things
or events) and their attributes (categories of information relevent to
your entities)? At a glance, it looks like you're tracking Rescue
companies and rescues, for starters. But how are table1 and table2
related? On what field(s) are they joined?
I'm assuming that each company can respond to one AND ONLY ONE rescue at
a time, but each rescue can be responded to by one OR MORE companies,
right? That looks like a one-to-many relationship to me. Without knowing
more about what specific information you are trying to get out of your
data, I'd suggest redesigning your tables: tblCompanies (CompanyID,
Location, Contact Info, and other relevent information); and tblRescues
(RescueID, CompanyID, RescueDate, RescueTime, LocationOfRescue,
NatureofRescue). Join these tables on CompanyID. You can then easily
track which companies (or how many companies) responded (or didn't
repond) to a particular emergency, or which companies responded to the
greatest number of emergencies in a given period, or in a given area,
etc. using queries. Post back if you need clarification or have other
questions.

Good luck,

LeAnne