Results 1 to 10 of 10
  1. #1
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31

    Normalization


    I've been tasked with designing a database to track cases for police investigations. I have a question regarding table design/normalization. Should I create a separate table for offender info and their address? My thinking is an offender can be linked to several address and/or several offenders can live at the same address. The problem I have with this is entering the data for the address. I would think it would be easier to type in the offender info then select an address from a combo box but what do you do if the address isn't in the table already? Seems like I would have to work backwards by entering in addresses first then enter in the offender information.

  2. #2
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    I like 1 table for Offender , tOffender only individual info and key: OffKey, (no addresses)
    tOffAddress, for all his/her addresses with an OffKey join
    tOffPhones for phones
    tOffCrimes

  3. #3
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I like that idea too, but when entering offender info, what is the best method for entering address to make sure you're not duplicating addresses? I want to force the user to choose an address.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You could have an "Add Address" button.
    Another idea is to use a combo box/list box and if the address is not in the list, use the "NotInList" event to add the new address.

  5. #5
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Address is hard. you could key street #, city, zip, but human error can creep in.
    Better to have dupes, than none.

  6. #6
    CJ_London is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    The way I handle it is to have the user enter the postcode first, a quick search of the db of address postcodes can return one of three results - 1) postcode already associated with offender, 2) postcode associated with other offenders or 3) no postcodes found

    Depending on the outcome, the user can select an address or continue to enter a new one.

    But still open to some risk - offender address might be '5 Mornington Crescent' and another address with the same postcode might be '3 Mornington Crescent', if user doesn't notice, the wrong address can be applied so additional 'are you sure' confirmation is required.

    If postcode is not known, then this process can be moved on by checking each other address line

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I've been tasked with designing a database to track cases for police investigations.
    When I read this, and then look at your question re recording address(es), I think you still have a lot of analysis to do. Do you know the range of potential offenses? Do you understand the attributes required for each? Do you have any sort of standard terminology set up/planned?
    Are you sure that Access is suited to your overall needs? More and more there is/are a lot of linkages/shareability across offender and enforcement type databases. And such linkages more and more are online.
    I don't mean to be negative, but it seems you have focused on a detail and may be missing the forest. I'm hoping that you have the proper scope to accomplish what you have been tasked to do.
    Why exactly were you chosen for the task?

    Do you have any specification? Or business rules/facts to help guide you?

    There are many facets of investigation, make sure you have accounted for those deemed relevant. Any you ignore or drop should be from a point of knowledge, not because you accidentally missed something.

    There are many links on the internet to help put some pieces together.
    http://bc.cb.rcmp-grc.gc.ca/ViewPage...1&contentId=-1
    http://www.calgary.ca/cps/Pages/Publ...on-checks.aspx
    http://qed.econ.queensu.ca/pub/cpp/March97/Schell.pdf
    http://en.wikipedia.org/wiki/Law_enf..._United_States
    http://www.rcmp-grc.gc.ca/nb/distric.../index-eng.htm

    Good luck with your project.
    You may get some ideas from this previous post.
    Or
    from stump the model approach.

    Get others involved in establishing your data model. And you don't have to invoke/act on the whole model all at once. It's much like designing a building or shopping center. Multi floors, parking garage, escalators, elevators.... you plan for them and include them in the blueprint --you have allowed for them, but you build the foundation and floors before you paint the trim etc. That's where priorities fit.

  8. #8
    ranman256's Avatar
    ranman256 is online now VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Heres my pick list form. Click image for larger version. 

Name:	pick3NY-lbls.jpg 
Views:	25 
Size:	87.8 KB 
ID:	20793
    dbl-click fires an append query to add to the tPicked table.

  9. #9
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I like the idea. Not sure if I have my tables set up correctly to do that.Click image for larger version. 

Name:	SIU.jpg 
Views:	20 
Size:	121.0 KB 
ID:	20800

  10. #10
    jzacharias is offline Advanced Beginner
    Windows Vista Access 2010 (version 14.0)
    Join Date
    Sep 2010
    Posts
    31
    I am still having a tough time figuring out what table to add to this so I am able to do the following. I currently have a form "Case" where I enter in information about the case. I have a subform where I can add multiple offenders. That part works fine. I want the ability to select any and all offenders in that particular case and add the offenses they are charged with. TblSection is a lookup table with all the offenses on the books. I have added another table tblOffenderCharges (OffenderId, CaseID and SectionId) and created a query using those tables. I created a subform from that query but I am unable to add charges to the offenders based on the case. I can add offenses to the offenders but isn't case specific.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Normalization
    By jlt199 in forum Database Design
    Replies: 10
    Last Post: 03-21-2014, 12:22 PM
  2. Need Help about Normalization
    By Book3s in forum Access
    Replies: 2
    Last Post: 10-24-2013, 02:03 AM
  3. Help with normalization for my first database
    By basstwo in forum Database Design
    Replies: 8
    Last Post: 10-02-2012, 10:25 AM
  4. Normalization
    By KPAW in forum Database Design
    Replies: 1
    Last Post: 06-09-2011, 06:24 PM
  5. Normalization
    By U810190 in forum Access
    Replies: 1
    Last Post: 03-30-2010, 04:55 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums