Probably a simple question re: table design

[jsanderson]

I have two sets of data to store: Property Number and Unit Number. Property number is unique but Unit Number may duplicate across multiple properties. So in my table, I have a UnitID field set to autonumber to keep each unit separate. The problem is this: How do I keep the data entry form from allowing entry two unit number 1's for property number 7?

 

[KenReay]

Hi

You make the PK on the 'sub table' PropertyNo:UnitNo

You do have a sub table don't you?

Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[LittleSmudge]

You said Property number is unique

Can't you hear it screaming at you ?

I can hear it from here.

PropertyNumber is shouting out " Make ME the Primary Key".

Your table then stores
PropertyNumber Prime Key - cannot duplicate
UnitNumber Other data - can duplicate

So you get data looking like

PropertyNumber     UnitNumber
    1                 2
    2               143
    3                 2
    4                37
    5                 2
etc .. ..

( If you REALLY don't want PropertyNumber to be the PrimeKey then simply set the Index setting for the PropertyNumber field in the table to Indexed(No Duplicates) and you don't need to do anything to the form.


'ope-that-'elps.

G LS


PS See the FAQ FAQ700-2190 abount avoiding space characters in object names.

 

[gusbrunston]

Say, Ken:

Could you expand on that "sub table"? I've searched 'help' and my two Access prayer books, and I don't come across that term.

Thanks, Gus Brunston An old PICKer, using Access2000.

 

[KenReay]

Hi Gus

Not surprised you cannot find it, just my sloppy use of English

What I was getting at is that is sound like three tables:

Properties
PropertId PK
Etc

Units
UnitId PK
Etc

PropertiesUnits
PropertyId ) PK
UnitId )

OK? Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[LittleSmudge]

I've reread the original post again Ken and I still get to only needing one simple table with two fields.


G LS

 

[KenReay]

Hi

Could be you are right,

I was assuming you could have 'n' Units per property, otherwise why say:

"How do I keep the data entry form from allowing entry two unit number 1's for property number 7?"
Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[jsanderson]

Each Property Number has multiple Units (think Hilltop Estates, Unit 2, Redwood Estates, Unit 2)

So to copy one of the posts and modify:

PropertyNumber UnitNumber UnitID
1 2 AutoNumber
1 3 AutoNumber
(but no more unit 3's allowed for PN1)
2 143 AutoNumber
3 2 AutoNumber
etc .. ..
The problem is ... the Unit Number needs to have duplicates allowed as does PropertyNumber. How do I restrict PropertyNumber 1 from having more than one UnitNumber 1?

Thank you for all the posts, I appreciate it.

 

[gusbrunston]

Hi:

Is this database already loaded with records, or do you still have an opportunity to do some redesign?

When I began a property management database two years ago, I was lulled into using autonumber as a primary key for receipt and disbursement transactions. Now one of my ongoing projects is to figure a way to rid my database of autonumber.

I suggest you at least look at assigning long integer as the primary key data type for properties (there are ways to increment this number other than autonumber) and the "real" unit number within the properties to identify the units--might require text data type, since units are often "16A" or "B106".

Back at the ranch...You probably want to avoid storing the same data in multiple tables. I have 3 main tables:

1) tblClients;
2) tblProperties(your Units);
3) tblTenants.

These are all I need until I get into the maintenance and financial accounting aspects of the project. From what you've described so far, it looks to me like all the data you want to store and retrieve could be included in one table, "Units", with the fields, "Properties" (primary key) and "Units" (duplicates O.K.).

Then you can design queries to select e.g., all the units painted red in a particular property; or all the units where cats are permitted no matter what property they're in; etc.

I know that my antipathy for autonumber is not universally shared. But I myself am unanimous in this.

Cheers
Gus Brunston An old PICKer, using Access2000.

 

[jsanderson]

Actually, this database has an extremely simple task and, it may be short-sighted to say so but, it never will be extended. My main form is a payment entry form in which the property number is entered in cboPropertyNumber, the unit number (not ID) is chosen from cboUnitNumber, the payment amount, payment date, and check number are recorded. I'm trying to get this to be an extremely speedy form with no mouse interaction needed. I'm using a linked combo box on a subform fsubPaymentEntry to choose the UnitID (determined by query from values matched between PropertyNumber and UnitNumber) and am trying to link child and master fields to the main form which contains the payment amount, date, and check number fields. That data is stored in tblPayments with PK - PaymentID(autonumber - yeah yeah, I know). The results of the Payments data is queried by date and filtered for the chosen date and exported to a text file which is programatically imported into accounting software.
My two immediate hurdles are a: PropertyNumber "1" can have more than one UnitNumber "1" and b: while the subform fsubPaymentEntry loaded alone works to select the UnitID when loaded as a subform on the main form with UnitID set as child and master field links I get a parameter entry dialog box and cannot enter data in the UnitNumber field.

 

[KenReay]

Hi

I think we have lost the thread.

While my suggested three table approach would prevent duplicate unit numbers within the property, reading the subsequent posts suggests to me you need two tables:

tblProperty
PropertyId PK
..etc

tblUnits
PropertyId ) PK
UnitId )
...etc

thus you can have:

tblProperty
PropertId Desc
1 XYZ Business Park
2 The Workplace

tblUnits
Propertyid UnitId Desc
1 1A Acme Car Wash Ltd
1 1B Vacant
2 1 Vacant
2 2 The XYZ Co Ltd

The compound PK of Propertyid / UnitId in table tblUnits will prevent duplicates of UnitId within PropertyId but will allow n units with the same id accross dirfferent properties.

The arguement about autonumber / long integer etc is surely dependant on the possible values of UnitId, if you can have things like 17A, then you cannot use Autonumber or longinteger (unless you have a 'hidden' key field and a unitnumber as a string field).


Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[LittleSmudge]

At it's simplest level you have a single table containing two fields.

Each field can, in itself, contain duplicate. However you do not want to allow duplicate pairings.

THEREFORE, simple make BOTH fields a joint Primary Key.



You'll have to think through yourself if this solution fits into the wider schema ( & Normalisation Rules etc.) - but it seem to fit what you're asking.


G LS

 

[SgtJarrow]

I have to second LittleSmudge.....a single table with a joint Primary Key would be my choice.... If we knew what it was we were doing, it would not be called research, would it? - Albert Einstein

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[KenReay]

Hi

I have grown bored with this, but, taking a prahse from an earlier post:

"Each Property Number has multiple Units (think Hilltop Estates, Unit 2, Redwood Estates, Unit 2)"

with a one table approach the Property Description will be repeated in each row, ie it is not normalised




Hope this helps

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[LittleSmudge]

Who said anything about putting the property description in the table under discussion ?

It was Property NUMBER that jsanderson mentions in the original post - and that's what I thought we were talking about - TWO numbers - just two numbers.

I was under the impression that the term "think" -
"Each Property Number has multiple Units (think Hilltop Estates, Unit 2, Redwood Estates, Unit 2)"
indicated that it was by way of explanation - rather than a statement of what needs to be stored.

HOWEVER,
In concept, your second post was proposing a classic two table Many-to-Many relationship with a linking table between.
Now, if the
tblProperty
PropertyId PK
PropertyDescription ( Indexed , No Duplicates )
..etc
contains no more fields than that :-
Then storing PropertyId into the linking table is all fine and usual.
However, there is no loss of Normalisation if you make the PropertyDescription it's own primary key and store that directly into the linking table - thus doing away with tblProperty entirely and changing the linking table into a table on the Many end of a relationship.
Now, years ago - I know people would have argued that the repeated text would take up more memory than repeated numbers - etc. However, that's a completely different argument.


At the end of the day - these differences in approach stem from differeces in interpretation of incomplete information provided at the start of the thread.
- Just like working for real clients


G LS