[MS_AccessPros] Re: Inspection Structure

 

Art

Yes, indices on numerical fields are faster, But the typical Access database does not usually have enough records to make a difference. That said, and index on one field is going to be faster than one on multiple fields.

Now you've done it! You've dragged me into that stupid eternal debate. I'm not saying another word on this topic. I've got better things to do like re-arranging my sock drawer.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Art Lorenzini <dbalorenzini@...> wrote:
>
> 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@...
> 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: 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]
>

__._,_.___

Reply to sender | Reply to group | Reply via web post | Start a New Topic

Messages in this topic (27)

Recent Activity:

• New Members 4
• New Files 2

Visit Your Group

Switch to: Text-Only, Daily Digest • Unsubscribe • Terms of Use

.

__,_._,___