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

Isn't there something to be said on the performance side where number keys will perform faster than strings?

With warm regards,

Arthur Lorenzini| SQL Server/Access Developer l alorenzin@live.com
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@comcast.net>
To: MS_Access_Professionals@yahoogroups.com
Sent: Monday, May 23, 2011 2:52 PM
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
>

[Non-text portions of this message have been removed]