ACCESS FEED: Re: [MS_AccessPros] Defining property ownership

Rabu, 30 Maret 2016

Re: [MS_AccessPros] Defining property ownership

ps,
you may want to store pcShare as currency instead of double if you don't need more than 4 decimal places

On 3/30/2016 11:52 AM, crystal 8 strive4peace2008@yahoo.com [MS_Access_Professionals] wrote:

Hi Jan,

Property and Properties are both reserved words so be sure not to use them for any names.

http://allenbrowne.com/AppIssueBadWord.html#P

I usually call the table Props ... perhaps something like this

Props
- PropID, PK (Primary Key)
- PropName -- however you will refer to the property in lists, ie: address1, city
- address1
- address2
- city
- st (State is also reserved)
- zip1
- zip2

Owners
- OwnerID, autonumber, PK (Primary Key)
- OwnerName
- etc

PropSales
- PropSaleID, autonumber, PK (Primary Key)
- PropID, Long, FK (Foreign Key) to Props
- DateSold, date of sale
- AmtSale, currency, amount of sale
- etc

PropOwners
- PropOwnID, autonumber, PK (Primary Key)
- PropSaleID, Long, FK (Foreign Key) to PropSales
- OwnerID, Long, FK (Foreign Key) to Owners
- pcShare, double, percent of ownership

warm regards,
crystal

New Release: free Analyzer for Microsoft Access
http://analyzer.codeplex.com

~ have an awesome day ~

On 3/30/2016 11:14 AM, jan.hertzsch@gmail.com [MS_Access_Professionals] wrote:

I have a PROPERTY table and an OWNER table. I want to keep a history of who owned what property, when, and who is the current owner.

I was planning on using a third table with 4 fields. Property, Owner, Date Purchased, Date Sold. With the obvious relationships to the PROPERTY and OWNER tables. I had assumed a blank Date Sold field would allow me to indicate the current owner. However, I want to make sure I don't have active two Property/Owner relationships. That is, the same PROPERTY with an open Date Sold field for two owners.

I am not wedded to this design but I cannot think how I could do a validation for this on data entry.

This has to happen in databases all the time. How would you suggest I design or validate my way out of this?

Thanks