ACCESS FEED: RE: [MS_AccessPros] Re: Inspection Structure

Bill-

I have no problem with AutoNumber, per se, but when there's a perfectly usable
unique value (like UnitNumber in Art's case), why not use that? And if you
propogate down a compound key, you can easily see the related items further up
the tree when looking at, for example, a record in tblUnitRoomInspection. If
you've defined the relationships properly, there's no problem joining on the
compound key in queries or setting the Link properties in a subform or
subreport.

Yes, a simple AutoNumber is somewhat more efficient as a Primary Key, but it's
not significant in performance terms when indexes and relationships are defined
correctly.

And yes, we've had this discussion before. There's really nothing terribly
wrong with either technique, I just like the information conveyed using my way
better.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
Sent: Monday, May 23, 2011 9:53 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Inspection Structure

John

Interesting. I'm a very big sissy then <s>. I use AutoNumbers as much as
possible. For one thing, there is less room for foreign key mess-ups if you only
have to join on one field. A unique index on the natural key fields will keep
everything kosher.

But then this kind of discussion never leads anywhere. The old "surrogate vs
natural key" topics never die.

Bill

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> Art-
>
> I'm firmly in the camp that advocates a "natural" Primary Key. What I mean by
that is when there are one or more fields that "naturally" form a unique
identifier, then that should be the PKey. AutoNumber is for sissies! <s>
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
> Sent: Monday, May 23, 2011 9:19 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Inspection Structure
>
> It's probably just me, When I think of a PK I think of a autonumber field...
>
>
>
>
> With warm regards,
>
> Arthur Lorenzini| SQL Server/Access Developer l alorenzin@...
> Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947
>
> 1316 E. 7th Street
> Sioux Falls, SD 57103
> SQL Server Development
> Database Adminstration Services
> Microsoft Access Development
> Grant Writing TA Services
> IT Assessment Services
> Software Application Training
>
>
>
>
>
>
>
> From: John Viescas <john@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, May 23, 2011 1:18 PM
> Subject: RE: [MS_AccessPros] Re: Inspection Structure
>
>
> Art-
>
> So? As long as the UnitNumbers are unique, it doesn't matter if they're text
or a number.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
> Sent: Monday, May 23, 2011 7:16 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Inspection Structure
>
> I will give this a try. The reason for the unitid and unitnumber is because
before all this started the assigned unit numbers were not all numerics some had
letters in them.
>
> With warm regards,
>
> Arthur Lorenzini| SQL Server/Access Developer l alorenzin@...
> Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947
>
> 1316 E. 7th Street
> Sioux Falls, SD 57103
> SQL Server Development
> Database Adminstration Services
> Microsoft Access Development
> Grant Writing TA Services
> IT Assessment Services
> Software Application Training
>
> From: John Viescas <john@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Monday, May 23, 2011 11:21 AM
> Subject: RE: [MS_AccessPros] Re: Inspection Structure
>
> Art-
>
> First of all, tblUnitDetail should be simply called tblUnit - a general
overview of each Unit. UnitNumber can be the Primary Key - you don't need the
separate ID field. (I assume unit numbers are unique!)
>
> You need a tblUnitRooms table:
>
> UnitNumber P1
> RoomID P2
> RoomTypeID -> pointer to a new tlkpRoomTypes
> RoomSize
> RoomNoDoors
> RoomNoWindows
>
> .. and anything else that's relevant to the definition of the room.
>
> tblUnitInspection looks OK - it denotes an overall inspection scheduled for a
particular unit. Then you need:
>
> tblUnitRoomInspection
> UnitInspectionID P1
> UnitID P2
> RoomID P3
>
> .. and then all the other fields you currently have in tblUnitEntryWay.
>
> The "trick" in your app is that when anyone creates a row in
tblUnitInspection, you should automatically load the rows in
tblUnitRoomInspection based on the rooms defined for the selected unit.
>
> John Viescas, author
> Microsoft Office Access 2010 Inside Out
> Microsoft Office Access 2007 Inside Out
> Building Microsoft Access Applications
> Microsoft Office Access 2003 Inside Out
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
> Sent: Monday, May 23, 2011 5:59 PM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: Re: [MS_AccessPros] Re: Inspection Structure
>
> I uploaded the structure to the needs assistance folder. Maybe someone can
take a look at what I got so far cause I am getting myself confused.
>
> With warm regards,
>
> Arthur Lorenzini| SQL Server/Access Developer l alorenzin@...
> Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947
>
> 1316 E. 7th Street
> Sioux Falls, SD 57103
> SQL Server Development
> Database Adminstration Services
> Microsoft Access Development
> Grant Writing TA Services
> IT Assessment Services
> Software Application Training
>
> From: Bill Mosca <wrmosca@...>
> To: MS_Access_Professionals@yahoogroups.com
> Sent: Friday, May 20, 2011 4:50 PM
> Subject: [MS_AccessPros] Re: Inspection Structure
>
> Then you need to add another row for that inspection of that unit in that room
for that part of the room.
>
> It's kinda like taking inventory of a room with shelves that contain multiple
products.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com, Art Lorenzini <dbalorenzini@>
wrote:
> >
> > Not quite because the rooms inspection point condition can have multiple
values (eg. Wall Condition type: Water spots, paint chipped, holesÃ‚ )
> >
> >
> >
> >
> > With warm regards,
> >
> > Arthur Lorenzini| SQL Server/Access Developer l alorenzin@
> > Office: 605-338-0947| Mobile: 605-857-9137 | Fax: 605-338-0947
> > Ã‚
> > 1316 E. 7th Street
> > Sioux Falls, SDÃ‚ 57103 Ã‚
> > SQL Server Development
> > Database Adminstration Services
> > Microsoft Access Development Ã‚
> > Grant Writing TA Services
> > IT Assessment Services
> > Software Application Training
> >
> >
> >
> >
> >
> >
> >
> > From: Bill Mosca <wrmosca@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Friday, May 20, 2011 2:52 PM
> > Subject: [MS_AccessPros] Re: Inspection Structure
> >
> > Ã‚
> >
> > Art
> >
> > You shouldn't need a separate table for each room. You would have one table
with a UnitInspectionID (to get the unit info/date/etc.), RoomID, inspection
point (i.e. Floor, ceiling, wall, etc.), condition.
> >
> > So you have an inspection of Unit #123. You go into the Entryway and mark
the floor's condition as poor.
> >
> > Wouldn't that cover it?
> >
> > Regards,
> > Bill Mosca, Founder - MS_Access_Professionals
> > http://www.thatlldoit.com
> > Microsoft Office Access MVP
> > https://mvp.support.microsoft.com/profile/Bill.Mosca
> >
> > --- In MS_Access_Professionals@yahoogroups.com, "Art" <dbalorenzini@> wrote:
> > >
> > > I am struggling setting up a table structure for a house inspection
database.
> > >
> > > I have it setup like this but it does not seems to be right
> > >
> > > tblUnitInspection
> > > UNitInspectionID PK
> > > UnitID
> > > InspectionDate
> > > TenantTypeID
> > > TentPresentFlag
> > >
> > > Now in the inspection form has each room in the house broken out
> > >
> > > Entryway, Kitchen, Dining room, Living room,Hallway, Bathroom #1, Bathroom
#2, Attic, Bedroom #1, Bedroom #2, Bedroom #3, Bedroom #4, Utility
room,Stairwell, Basement, Exterior.
> > >
> > > I am looking at putting each room in it seperate table (eg.
tblUnitEntryway)
> > >
> > > tblUnitEntryway
> > > UnitEntrywayID PK
> > > UnitInspectionID
> > > FloorsCleanConditionTypeID - Lookup table tlkpCleanCondtionType
> > > FloorsTypeID - Lookup table tlkpFloorType
> > > FloorConditionTypeID - This is my issue, this is a key to the
tlkpConditionType table but I found out that they can select mulitple items. So
I know multi- value fields are bad so I am not sure what to do.
> > > FloorRepairsDesc - text
> > > WallCleanConditionTypeId - Lookup table tlkpCleanCondtionType
> > > WallConditionTypeID - same issue as above.
> > > WallRepairsDesc - Text
> > > CeilingCleanConditionTypeId - Lookup table tlkpCleanCondtionType
> > > CeilingConditionTypeID - same issue as above.
> > > CeilingRepairsDesc - Text
> > >
> > > And so forth and so on. I was think I could put the Floor, Wall, Ceiling
into seperate tables that will make for a whole bunch of table I don't think I
need. Any ideas?
> > >
> > > Thank you,
> > >
> > > Art Lorenzini
> > > Sioux Falls, SD
> > >
> >
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> [Non-text portions of this message have been removed]
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>

------------------------------------

Yahoo! Groups Links