Number of Sub Tables linked to Main Tables

[chris11590]

How many tables is the limited when linking subtables to a main table.


ie, i have one many table that has 36 subtables linked to it. will this have an effect on the db in any way?

 

[KenHigg]

Wow. That's a lot. Can you post a picture of the relationships? I'd have to bet you could normalize something...

(As far as a limit, I'm not sure - )

 

[chris11590]

im at work.. im blocked from uploading. i posted the DB yesterday, can you be so kind and search "DB"- posted yesterday by me. i attached it

 

[chris11590]

i think i was able to do it

 

[chris11590]

incident is defined as any city event such as Thanksgiving Parade, 4th of July, and during this event ff , emts and paramedics are resources who assigned radios trucks etc...

i many have to create junction table for radios.

 

[KenHigg]

Hum... You have to ask a question similar to the following for every table relationship you have:

For each incident there can be multiple branchs. If the is true the you'll need the branch table, if not, if each incident can have only one branch, then you can put the branch field in the incident table.

 

[chris11590]

i am looking to create statistical data regarding number of events for the year, and amount of resources and equipment allocated.

 

[chris11590]

the answer is i need that each incident can have numerous branches. these are large scale events.

 

[KenHigg]

Then you do need the branch table. Have you gone through this drill with each relationship?

 

[chris11590]

incident has different types and assorted messages for the event and it has different hospital locating and reporting location, etc...

 

[chris11590]

yes i did. thats why i ended up with so many relationships. the ones standing alone. arent related.

as far as the weather tble. not sure i need it...

 

[boblarson]

Your lookup tables are incorrect. You should not have IncID in most of them (for example Areas, Facilities, Hospitals, etc. These should be providing you values to use in your incident, not putting the incident in them. If you do it that way you will end up with the same hospital name in the hospital table many, many times where it should only be in there once.

 

[chris11590]

dont i need to have incid to related the tables? iam not sure what you mean by lookup tbles.

 

[chris11590]

each incident has different area/facilitis for the troops to meet. each incident has different hospital to take patient to during that incident

 

[chris11590]

lookup tables to me when i go into the properties and set up lookup tables.

 

[chris11590]

Did you mean have the Ids from Areas, Facility tbls insert into the Incident tble, do I need to reverse it?

 

[neileg]

dont i need to have incid to related the tables? iam not sure what you mean by lookup tbles.

When you have a one to many relationship, you hold the primary key value of the one side as a foreign key in the record from the many side. You have this the wrong way round. If you hold the incident ID in the hospital table you can only associate one incident with the hospital. If you hold the hospital ID in the incident table, you can associate the hospital with many incidents.

You are right that Access supports table level lookups, but that's not what Bob is talking about (table level lookups are a nightmare and should be avoided). Typically, when you are building a form that deals with a one to many relationship like you have, you would use a combo box or a list box control so that the user of the form can pick the value they want from a list. The table that provides the data for the list is referred to as a lookup table.