ACCESS FEED: [MS_AccessPros] Re: Inspection Structure

John

So like in Animal Farm, everyone is equal. Some are just more equal than others. <g>

Bill

--- In MS_Access_Professionals@yahoogroups.com, "John Viescas" <john@...> wrote:
>
> 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
>