Hi all,
I have just joined the community and I have very basic Access skills. I am using Microsoft Access 2007 and I am trying to create the following database.
I have attached a picture of my relationships. Please ignore all the relationships that i have made as i know they are wrong. Ill try summarise what kind of database i am trying to make:
There are two ‘sides’ to the database. The one side is the ‘Stock’ side and the other side is the ‘Person’ side. I then used an affiliation table to link the two. If we start with Stock.
Stock is basically a ‘leasable’ or ‘saleable’ unit. There are 3 types of stock (Retail, Office and Industrial). Each stock has a status which is basically ForSale, ToLet and OffMarket. A stock could be for Sale AND To Let. What I did was create a Lookup under Status which is fine and I allowed multiple values as a stock can be ForSale AND To Let.
However, for Stock Types, each type brings new properties. If you see under the stock table I have listed all the properties that are common to ALL stock. Then where the properties of Retail, Office, and Industrial differ I have created new tables for each type. My problem here is that a stock could be Both Retail and Office, or Office and Retail, etc. Ideally what I would use is the same sort of option like with ‘Status’ as mentioned before, but if I choose for example 'Office' a pop-up for the properties of an office space should appear, and if I choose 'Industrial', the properties for Industrial should pop-up, or I can choose both ‘Office’ and ‘Industrial’ and the properties for both should pop-up. I hope Im not confusing. The reason why I want it like this is because if I need to search through all the stock for Offices, then all the ‘Stock’ that has offices part of it should be the result.
That is just the Stock side.
With the ‘Person’ table, I have done something similar, where a ‘Person’ could be a Client, Property Manager, Landlord, Tenant or Other. However, a ‘Person’ could be many of these types at the same time. Eg. A ‘Person’ could be a ‘Landlord’ for 1 ‘Stock’ and also a ‘Tenant’ for that stock. A ‘Person’ could be a ‘Tenant’ in 1 stock and then also a ‘Client’, but a ‘Client’ has no relation to stock at all. Anybody could be a client, as a client is a short term profile for a person. If a ‘Tenant’s’ lease is due to expire in 2 months and they wish to look for new premises, then he becomes a ‘Client’ but is also a tenant at the same time. So you can see where a ‘Person’ would have more than 1 type. And then depending on what the ‘Client’ wants (Retail, Industrial, Office).
Then the whole crux of this is that I should be able to input a new stock with or without an affiliated person, or a new person with or without an affiliated stock.
Man I have been struggling a lot with this. Got any ideas? I am starting to think that I have all my tables wrong.
I think it would be better to explain my use for such a database. Once it is complete I would like to use it as a tool to:
a) record all 'stock' in my area and search within the parameters of 'Area', 'Size', 'GrossRental', 'Status', etc.
b) record all 'person'(s) by their type and affiliate them to a stock (depending on their type.
c) be able to cross reference 'client' requirements with 'stock' and the stock type. eg. if a client is looking for industrial space, should be able to cross reference his requirements with all the industrial stock.
I have just joined the community and I have very basic Access skills. I am using Microsoft Access 2007 and I am trying to create the following database.
I have attached a picture of my relationships. Please ignore all the relationships that i have made as i know they are wrong. Ill try summarise what kind of database i am trying to make:
There are two ‘sides’ to the database. The one side is the ‘Stock’ side and the other side is the ‘Person’ side. I then used an affiliation table to link the two. If we start with Stock.
Stock is basically a ‘leasable’ or ‘saleable’ unit. There are 3 types of stock (Retail, Office and Industrial). Each stock has a status which is basically ForSale, ToLet and OffMarket. A stock could be for Sale AND To Let. What I did was create a Lookup under Status which is fine and I allowed multiple values as a stock can be ForSale AND To Let.
However, for Stock Types, each type brings new properties. If you see under the stock table I have listed all the properties that are common to ALL stock. Then where the properties of Retail, Office, and Industrial differ I have created new tables for each type. My problem here is that a stock could be Both Retail and Office, or Office and Retail, etc. Ideally what I would use is the same sort of option like with ‘Status’ as mentioned before, but if I choose for example 'Office' a pop-up for the properties of an office space should appear, and if I choose 'Industrial', the properties for Industrial should pop-up, or I can choose both ‘Office’ and ‘Industrial’ and the properties for both should pop-up. I hope Im not confusing. The reason why I want it like this is because if I need to search through all the stock for Offices, then all the ‘Stock’ that has offices part of it should be the result.
That is just the Stock side.
With the ‘Person’ table, I have done something similar, where a ‘Person’ could be a Client, Property Manager, Landlord, Tenant or Other. However, a ‘Person’ could be many of these types at the same time. Eg. A ‘Person’ could be a ‘Landlord’ for 1 ‘Stock’ and also a ‘Tenant’ for that stock. A ‘Person’ could be a ‘Tenant’ in 1 stock and then also a ‘Client’, but a ‘Client’ has no relation to stock at all. Anybody could be a client, as a client is a short term profile for a person. If a ‘Tenant’s’ lease is due to expire in 2 months and they wish to look for new premises, then he becomes a ‘Client’ but is also a tenant at the same time. So you can see where a ‘Person’ would have more than 1 type. And then depending on what the ‘Client’ wants (Retail, Industrial, Office).
Then the whole crux of this is that I should be able to input a new stock with or without an affiliated person, or a new person with or without an affiliated stock.
Man I have been struggling a lot with this. Got any ideas? I am starting to think that I have all my tables wrong.
I think it would be better to explain my use for such a database. Once it is complete I would like to use it as a tool to:
a) record all 'stock' in my area and search within the parameters of 'Area', 'Size', 'GrossRental', 'Status', etc.
b) record all 'person'(s) by their type and affiliate them to a stock (depending on their type.
c) be able to cross reference 'client' requirements with 'stock' and the stock type. eg. if a client is looking for industrial space, should be able to cross reference his requirements with all the industrial stock.
. 
. 
