Auto fill in and check data from other tables

[ixusss]

I am new to Access, and I need some help for this project I am working on. Please let me know if my question doesn't make sense.

I have created four tables: Clients, Tenants, Property, Contract


Clients
Client ID | Client Name |

Tenants
Tenants ID | Property ID | First Name | Last Name

Property
Property ID | Client Name | Contract ID

Contract
Contract ID | Clients ID | Client Name | Tenant ID | Tenant Name |Property ID |

Is there a way that when I enter Client Name in the Contract table, Client ID would automatically come up and check against other fields in the table if the information entered in Contract table is in sync and relevant against to data entered in Client, Tenants, and Property table?

Please let me know if this is not clear and I need explain more. Thank you.

 

[neileg]

Questions and clarifications
1. Do Clients own the Properties?
2. Can properties have more than one tenant?
3. Can tenants have more than one property

 

[ixusss]

Thanks for your reply.

1. Clients own the property.
2. Properties can only have one tenant.
3. Tenants can only locate in one unique property.

In addition, Clients can own more than one property.

Thanks.

 

[neileg]

This would be prefereable:

Clients
Client ID | Client Name |

Tenants
Tenants ID | First Name | Last Name

Property
Property ID | Client ID | Other Details

Contract
Contract ID|Tenant ID |Property ID |

Of course, since this will all change with time (clients buy or sell properties, tenants move into or out of properties) you need date references on all of these.

 

[ixusss]

Thank you Neileg.

The reason why I wanted the Contract table to be able to check other fields in the table because i wanted it to prevent mistakes and confusions such as wrong tenant ID for the Contract ID or wrong Property ID for the Tenant ID.

Does it mean I just have to manually check the data consistency and integrity from the suggestion you made? Thanks.

 

[neileg]

If you already have the data stored then why do you want to store it again? If you don't store it the second time, you don't need to check that it is correct.

Step away from your database for a second and back into the real world. Your written contract defines the property and the tenant, OK? The only way this changes is to have a new contract. Back in your database, so long as the contract table is correctly populated, then you have the link between the tenant table and the property table.

Now if your business model allows for a sequence of contracts where the tenant stays in the same property, then you should be validating the new contract record against the old one. Back in the real world, you would be copying from the old contract to the new one, so you model this process in your database.

Does this make sense? You should be doing anything in Access that doesn't tally with the real world.

 

[ixusss]

Thank you for your comment.

There will be other fields in the tables. For example, in the property table, there are property value, location, and return rate. In the Tenant table, there are income, job information...

To answer your question, the reason for information being stored again because the data will be vital for sales marketing purpose and for our different business model and approach. We are expecting high volume of properties entering into our business, therefore, with Access, we think it can help us to gather the data quickly.

Is my approach with the use of access correct? Suggestion?

Thanks

 

[neileg]

I don't think I understand what you are wanting to do. You seem to be suggesting that the data you collect from the potential tenant needs to be matched with data you hold on the property. Or have I musunderstood?

I'm sure Access can gather your information for you, I'm just trying to figure out what you want to do with it.

Another mini database lesson:
1. In a relational database like Access, you only hold data once, no matter how many places you use it. Therefore, when you hold property details in the Property table, you only need to know the PropertyID to access that data. So you only hold the PropertyID in the contract table.
2. When you are building your Access application, you really need to understand how your business model works. If you don't understand it enough to be able to write it down on paper, you won't be able to model it in Access. It's worth spending time with Post Its and a sheet of paper on the wall to map it all out.

 

[GaryPanic]

Neil has this spot on

can you draw the relationship between the tables
if you cannot write this down and make sense out of it then you cannot do it

Start with whats the most import table
I think its accutal the Property

the property can & will have
client (who ownes the property) and Tennant (who rents propert)

The contract then is either on the property or tennat

so the centre of your universe should the property
each proerty will have an autoid PK (Prime KeY)

this prime key will also be used in the customer table and the tennant table

your contract table is either to the property or tennant

(I know you have contracts with clients as well but they are differnt contracts - you could use 1 contract table for all contracts client to you and tennants to you ) or use a filter on the contract table T for tennants and C for clients


Property :- Fish Lodge
Tennant- A N Other
Owner Mr Big
contract 45

so Fish lodge has 3 children tennant/ownerr/contract

or it could be that Fish lodge has 2 children Tennat/owner and Tennant/owner has a child each

you need to draw this out and understand what the relationships arre
how mnay contracxt is their a contract between tennent and owner or is the contract with yourself

--