Relationship - Three Tables - No Duplicates

[Huddle]

I have two tables, Building Stats and Contacts Table. I want to build a releationship so that I can create queries for reports and forms.

The problem I have is the building stats table should have no duplicate buildings but could have multiple contacts. The contact table should have no duplicate contacts but each contact could have multiple buildings.

I created a third table to try and connect the two but it only loads the building number and not the contact ID. (I've attached my relationship screen). There may be an easier way to do this.



My main objective is to have a building form where they can enter the info and have a command button that will take them to a contact form to enter the contact (s) if need be without duplicating the contacts (I'd perfer not to have the same person in there with various ways people enter the address or incomplete adress/phones which is how it is currently being done)

I'm totally confusing myself.

[jzwp11]

Overall, your structure between the building stats and the contacts looks correct. To display them, you will need a form based on the building stats table with a related subform based on the junction table (the building stats-contact). You would use a combo box in that subform to populate/display the contact. The combo box would be based on your contact table.

A couple suggestions, using spaces and special characters (i.e. # sign) in your table and field names is generally not recommended. In fact, the # sign is used a a date delimiter in queries. Secondly, I would recommend having in autonumber primary key field in every table and use that to join related tables.

It does not look like your evaluation table is structured correctly. Restaurants, laboratories etc are use types which means that they should be records not fields in a table.

Also, depending on your application, the floor stats table might not be structure correctly either.

[Huddle]

I made the corrections of the # and spaces...rookie mistake...I should have known better. It's been too long since I've been in Access.

I also added AutoNumber to all the tables.

The evaluation table should be right. The resturants, labs..etc are a evaluation check list using yes and no data type. Each building may have multiple evaluations; Same with the floor stats, each building may have multiple floors. I have them a subforms on the main Building Summary Form

The relationships on the doc attached are what the computer created. I haven't changed anything and I'm not sure I need to.

I get the jest of what you are saying but I can't think it through in the user's perspective. I have to much on the Building Summary Form to put a contact subform and I'm not sure if that is what I should do.

As you can see by my attached doc I have commands going to forms and reports regarding the contacts. My main concern is that when someone enters a new contact the contact may be in the table under another building number and they will be entered twice. I'm not so concern about the dups because I can work around those. I'm more concern about the mess made by different ways people may enter the information i.e. Jane Smith may be entered with a PO Box on one record and a street address on another.

Am I making any sense?

[jzwp11]

What happens if you have to add something in addition to laboratories, restaurants etc.? You would have add a new field to your table, and then, all forms, queries and reports that reference the table would also have to be changed. I wouldn't want to do that. It would be better to have the following:

tblUsage (you would have a record for each type of usage such as a laboratory, storage, retail etc.)
-pkUsageID primary key autonumber
-txtUsageName

tblEvaluation
-pkEvaluationID primary key, autonumber
-fkBuildingID foreign key to tblBuildingStats
-fkUsageID foriegn key to tblUsage

This structure will allow you to have multiple evaluations per building.

The relationships on the doc attached are what the computer created.

Don't assume that what was created is correct.

I have to much on the Building Summary Form to put a contact subform and I'm not sure if that is what I should do

You might look at the tab control to help with organization on your form.

Regarding tbl_FloorStats, can't a floor have multiple suites? If so, do you want to track those?

tbl_FloorStats
-pkFloorStatsID primary key, autonumber
-bldgNumber foreign key to building stats table
-floor

tbl_FloorSuites
-pkFloorSuitesID primary key, auotnumber
-fkFloorStatsID foreign key to tbl_FloorStats
-SuiteNumber


Also, I would not use the word autonumber as a field name, I would guess that it is a reserved word in Access.

[Huddle]

You've been a great help to me. With your advice and some guidance from a IT guy here at work I've went back to the drawing board to reign in this messy monster.

I've attached my schema. I would appreciate your comments on the structure to point me in the right direction. I'm especially concern with the contacts, building and meetings because they will have interaction with all three types. Also with the Pre-Lease because of the multiple numbers. I was told they may have 4 to 10+... but no less than 3.

I'm more of a tree person rather than a forest so at times things become overwhelming.

[jzwp11]

Typically a schema relative to databases is the table/fields and how they are related, similar to what you would see in the relationship window in Access. Your schema looks to be related to forms. To do this properly, we have to set forms aside (for now) and concentrate soley on the table structure. Usually the forms are designed around the table structure not the other way around.

From what I can gather from your schema, you work for a company that leases space in various buildings, correct?

When you lease, do you lease the whole building? floor(s)? suite(s)? or just about any space within a building?

You capture information about each building (esthetic items, dimensions, year built, year last renovated etc.. ) Do you also capture similar information about a floor or space or suite within a building (last year renovated, esthetics, dimensions etc. but obviously not the year built since that pertains to the overall building)?

Apparently you lease to agencies and for each agency there are contacts. Can there be multiple contacts per agency? Are there any other people involved in your application (employees working on the lease etc.)?

It looks like you have a 3 phase process: looking/bidding, pre-lease and lease. Can you explain, in detail, what is involved in each of these 3 phases? How are they similar? How are they different? Apparently, you can have meeting relative to any or all of these phase for which you want to capture details. As a result of the meetings are there action items that must happen? Who does them? Do you want to track them?

I apologize for being so wordy, but this appears to be a somewhat complicated application that will require some effort to understand and then to translate into an approporiate table structure.

[Huddle]

Typically a schema relative to databases is the table/fields and how they are related, similar to what you would see in the relationship window in Access. Your schema looks to be related to forms. To do this properly, we have to set forms aside (for now) and concentrate soley on the table structure. Usually the forms are designed around the table structure not the other way around.

I thought it would be easier to work backwards since I've already started on the database because I know what I want on the forms and I can build the table once I have determined what I need

From what I can gather from your schema, you work for a company that leases space in various buildings, correct?

When you lease, do you lease the whole building? floor(s)? suite(s)? or just about any space within a building?

I work for homeland security. We work hand in hand with GSA who technically own the building or space. GSA usually has ownership of the building and they lease it to government agencies. The agency could lease one office upto the whole building.

You capture information about each building (esthetic items, dimensions, year built, year last renovated etc.. ) Do you also capture similar information about a floor or space or suite within a building (last year renovated, esthetics, dimensions etc. but obviously not the year built since that pertains to the overall building)?

The esthetics from my understanding are only based on the building. It gives us the information needed to assess security needs.

Apparently you lease to agencies and for each agency there are contacts. Can there be multiple contacts per agency? Are there any other people involved in your application (employees working on the lease etc.)?

Each building can have multiple contacts from consturction contractors to owners to government officials, etc. And each contact could be working with multiple buildings.

It looks like you have a 3 phase process: looking/bidding, pre-lease and lease. Can you explain, in detail, what is involved in each of these 3 phases? How are they similar? How are they different?

The phases begin with an agency requesting space giving GSA the deminision, location and other needs related to the agency's needs.

GSA puts the request out for bid (how this is done I'm not sure). They are required to accept at least three bids. Once the bids have been received and one is selected it goes into the Pre-Lease stage.

The Pre-Lease is the stage where construction, renovation, installations, etc. is done. Once the space is ready to be occuppied it is considered to be leased.

Apparently, you can have meeting relative to any or all of these phase for which you want to capture details. As a result of the meetings are there action items that must happen? Who does them? Do you want to track them?

Meetings / Objectives would be a part of every stage. This part could be anything from a meeting to be attended to a phone call that should be made. It isn't really set up with 'Action Items'. Most of the time I get an email saying someone will do something and my boss wants me to determine when we need to check with them to make sure they do it.

I apologize for being so wordy, but this appears to be a somewhat complicated application that will require some effort to understand and then to translate into an approporiate table structure.

I really appreciate your help. I hope I'm not asking to much for you to help me get this set up properly. Since this will be used by "Big Dog" it would be good to have it as good as it can be.

[jzwp11]

If GSA is doing the leasing, what is your role in all of this? Do you have to maintain a "catalog" of what space (and the dimensions thereof) is being leased by which agency?

[Huddle]

In regard to the purpose of the database it is so there is a central hub for all the information. Homeland security and GSA need a better way to communicate what projects are in the mix and what is going on with the processes.

Our role is we provide inital assement of security installation needed. We at time install the security and may maintain it.

[jzwp11]

I think that it will be easier to break this into 2 sections to start. First, we'll set up the tables to define the buildings and the spaces within the buildings. The second aspect of dealing with the leases we'll set aside for now.

With respect to defining a space, what is the smallest space in a building that you deal with and how do you define the location of a space within the building? Since the space occupied by an agency can vary depending on how they arrange/construct/alter the space, a specific area can be dynamic. I assume you use floor as a primary way to locate a space within a building, but do you divide the floor into smaller, equal subdivisions and how do you locate a space within a floor (Northeast, northwest, southwest etc. part of the building)?

[Huddle]

I'm going to talk to the director today. What general types of questions will you need to ask? Such as your last posting. I'm not sure that they will be breaking the space down as you suggested. Once the general space is determined it is in someone elses hand and we don't need to track it. I'll verify that.

[jzwp11]

Without knowing more about what you are doing, it is hard to come up with additional questions. I would guess that you have to be able to locate a certain area on a floor to prevent 2 lessees from occupying the same space, so you will have to get clarification on that.

I would assume that since you are part of Homeland Security, you may need to document any security features/measures necessary for each space leased as well as for each building.

[Huddle]

OK, I've pinned down some better direction of where we want to go with this.
The three things my boss wants to know is
1. When things are coming [what requests are out there, what space is being looked at]
2. Whats going on [Are people doing what they need to in order to get a space occupied; are people following through on actions]
3. Has there been closure on that project.

So the basics are
1.The building numbers, address and rather it is in the looking, leasing or occupied status
2.Contacts info - by building and general
3.Objective, Meeting, Action Items....with results
4.Various reports and search options

2nd tier needs
1. Information on space requirements, i.e total sq footage, location, occupancy dates

Everything else is just fluff. GSA, Security, Ect. have their own tracking systems. This will be used more for an informational hub.

Does this give you a better insight.

[jzwp11]

Based on the information you have provided, this is the structure that I would propose. It will need additional refining.

tblBuildings
-pkBuildingID primary key, autonumber
-BldgNumber
-Address
-City
-State
etc.

You mentioned items that describe the building such as esthetic items

tblEstheticItems
-pkEstheticItemsID primary key, autonumber
-txtEstheticItem

Since a building can have many esthetic items and an esthetic item can apply to many buildings, we need a junction table

tblBuildingEsthetics
-pkBuildingEstheticID primary key, autonumber
-fkBuildingID foreign key to tblBuildings
-fkEstheticItemID foreign key to tblEstheticItems (long number field)

You also mentioned something about dimensional aspects of a building

tblDimensionalAspects
-pkDimAspectID primary key, autonumber
-txtDimAspect

tblBuildingDimensionals
-pkBldgDimID primary key, autonumber
-fkBuildingID foreign key to tblBuildings (long number field)
-fkDimAspectID foreign key to tblDimensionalAspects (long number field)
-spDimension



Assuming that a designation for a floor may apply to many buildings, I would have a table that has the various floor designations in a table. These designations can apply to many floors.

tblFloorNames
-pkFloorID primary key, autonumber
-txtFloorName



tblBuildingFloors
-pkBuildingFloorID primary key, autonumber
-fkBuildingID foreign key to tblBuildings (long number field)
-fkFloorID foreign key to tblFloorNames (long number field)
-spFloorArea

tblLocationNames (a designation for location on a floor)
-pkLocationNameID primary key, autonumber
-txtLocationName

tblBuildingFloorSpace (just a smaller subdivision of your choosing to correspond to the space available for lease)
-pkBldgFloorSpaceID primary key, autonumber
-fkBuildingFloorID foreign key to tblBuildingFloors (long number field)
-fkLocationID foreign key to tblLocation (long number field)
-spSpaceArea


Your process involves many people

tblPeople
-pkPeopleID primary key, autonumber
-txtFName
-txtLName

tblContactMethods (home phone, work phone, cell phone, e-mail etc.)
-pkContactMethodID
-txtContactMethod

A person can have many contact methods
tblPeopleContacts
-pkPeopleContactID primary key, autonumber
-fkPeopleID foreign key to tblPeople (long number field)
-fkContactMethodID foreign key to tblContactMethods (long number field)
-txtContactInfo (a field to hold the actual phone # or email address)

tblAgencies (a table to hold the agencies)
-pkAgencyID primary key, autonumber
-txtAgencyName

You probably have many people associated with an agency

tblAgencyPeople
-pkAgencyPeopleID primary key, autonumber
-fkAgencyID foreign key to tblAgencies (long number field)
-fkPeopleID foreign key to tblPeople

It sounds like the leasing process is a multistage process, so when an agency first comes to you a project can be set up

tblProjects
-pkProjectID primary key, autonumber
-fkAgencyID foreign key to tblAgencies (long number field)
-dteStart (start date)

A table to define the various stages (bid, pre-lease, lease)

tblStages
-pkStageID primary key, autonumber
-txtStage

A project will go through the various stages
tblProjectStages
-pkProjStageID primary key, autonumber
-fkProjectID foreign key to tblProject (long number field)
-fkStageID foreign key to tblStages (long number field)

At each stage, the space in which the agency is interested may change, and they may be interested in multiple spaces

tblProjectStageSpaces
-pkProjStageSpaceID primary key, autonumber
-fkProjStageID foreign key to tblProjectStages (long number field)
-fkBldgFloorSpaceID foreign key to tblBuildingFloorSpace (long number field)
-fkUseID foreign key to tblUses (long number field)
-dteOccupancy (if stage<>leased, the date would be a proposed "wanted" date otherwise it is the actual occupancy date)


tblUses (to define how the space is to be used office, lab, retail etc.)
-pkUseID primary key, autonumber
-txtUse

You might have specific agency people involved in the project

tblProjectPeople
-pkProjectPeopleID primary key, autonumber
-fkProjectID foreign key to tblProject
-fkAgencyPeopleID foreign key to tblAgencyPeople (long number field)

Regarding the meetings relative to a project (I assume)

tblProjectMeeting
-pkProjectMeetingID primary key, autonumber
-fkProjectID foreign key to tblProjects (long number field)
-dteMeeting

At each meeting you will have many people attending (from various agencies--yours, from the agency interested in the space, GSA etc.)

tblProjectMeetingPeople
-pkProjMeetPeopleID primary key, autonumber
-fkProjectMeetingID foreign key to tblProjectMeetings (long number field)
-fkAgencyPeopleID foreign key to tblAgencyPeople (long number field)

You also might have action items resulting from a meeting and that action is probably assigned to someone

tblProjectMeetingActions
-pkProjMeetActionID primary key, autonumber
-fkProjectMeetingID foreign key to tblProjectMeetings (long number field)
-txtAction
-fkAgencyPeopleID foreign key to tblAgencyPeople (long number field)
-dteDue (when item needs to be completed)

If you wanted to detail the progress of each action item, then you would need a detail table to handle that.

[Huddle]

I really do appreciate your help in this. Your information will give me a focused direction. Thank You.