Results 1 to 5 of 5
  1. #1
    NewUser2Database is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    2

    Trouble normalizing data from excel to Access


    I am working on a project where I have to create a database based on a template from excel. The issue that I have is that there are multiple rows of duplicated data and I am not really sure how to normalize it properly. Here is a small sample of the data.
    From To Side Street Quadrant Dir Stype
    2000 2031 Odd Cornelia 1 West Ave.
    2032 2099 Odd Cornelia 1 West Ave.
    2100 2131 Odd Cornelia 1 West Ave.
    2132 2199 Odd Cornelia 1 West Ave.
    2200 2231 Odd Cornelia 1 West Ave.
    2232 2299 Odd Cornelia 1 West Ave.
    2300 2331 Odd Cornelia 1 West Ave.
    2332 2399 Odd Cornelia 1 West Ave.
    2000 2031 Even Roscoe 1 West St.
    2032 2099 Even Roscoe 1 West St.
    2100 2131 Even Roscoe 1 West St.
    2132 2199 Even Roscoe 1 West St.
    2200 2231 Even Roscoe 1 West St.
    2232 2299 Even Roscoe 1 West St.
    2300 2331 Even Roscoe 1 West St.
    2332 2399 Even Roscoe 1 West St.
    2000 2031 Odd Roscoe 1 West St.
    2032 2099 Odd Roscoe 1 West St.
    2100 2131 Odd Roscoe 1 West St.
    2132 2199 Odd Roscoe 1 West St.
    2200 2231 Odd Roscoe 1 West St.
    2232 2299 Odd Roscoe 1 West St.
    2300 2331 Odd Roscoe 1 West St.
    2332 2399 Odd Roscoe 1 West St.

    As you can see I have multiple rows of Street named Roscoe with different address ranges. for example from 2000-2031 even until 2332-2399 even and then right below that I have the same ranges but in odd. My question is it possible to just use one row of data from 2000-2399 Roscoe to minimize redundant data and still lookup an address in that range?

  2. #2
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    That depends upon what you are trying to do. It depends entirely on the usage for the database. My gut reaction is "probably not".

    Those record are not INHERENTLY redundant, not duplicate, and not un-normalized. They each represent a different, valid, real-world entity - a section of one side of a street. The valid reason for differentiating between odd and even on a section of street is that postal carrier routes sometimes go down one side of a street, (say the odd ones), then return later up the other side of the street (say the even ones).

    Given that someone at your organization thought the distinction was important in making the excel spreadsheet, it would be foolhardy to make a design decision like merging those records without first interviewing the people who use the data, and find out why that information was significant.

    Look for business use cases - how did they use the information? That will tell you whether you have to retain that distinction.

  3. #3
    NewUser2Database is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    2
    Thank you for your reply Dal Jeanis. So for what your telling me I am most likely going to have to use all of the rows of data which is not a problem I just needed to be sure. My next problem is how do I create a table id for my street table? Should I just use auto increment for each row of data? Just to give you a little background to help you better understand what I am exactly doing. My project is to create a database that will allow one to input an address into a form and then return information from the database such as police district, street sweeping, garbage collection, etc.. here is a sample of the data that I will need pulled from the address that was entered.
    Enter street number: 2657 Information for address: 2657 N Clybourn Ave.
    Enter street name: Clybourn Chicago, IL 60614
    Police Information Street Sweeping
    Located in Beat 1931 of the 19th Police District Section 3
    Station address Station phone number Next Street Sweeping
    2452 W Belmont (312) 744-5574 The schedule for 2011 will be available in March
    CAPS Meetings 2011 Street Sweeping Dates
    Hamlin Park Thu, Apr 07, 2011 & Fri, Apr 08, 2011
    3035 N Hoyne Fri, May 27, 2011 & Tue, May 31, 2011
    Held every 1st Thursday of the month quarterly at 7:00 PM Wed, Jul 20, 2011 & Thu, Jul 21, 2011
    Next CAPS meeting Fri, Sep 09, 2011 & Mon, Sep 12, 2011
    Done for 2011
    Community Organizations Garbage Collection
    Neighborhood Groups Regular Garbage Pickup Day
    West DePaul Neighbors
    (773) 620-9269
    denoj@remax.net
    President: Deno Jefferes
    Tuesday
    Please note that the actual pickup day can vary due to volume, weather, special circumstances and holidays.
    Blue Cart Recycling Pickup
    #NUM!
    None Other
    Elections
    Precinct 24
    Polling Place
    Wrightwood Park - Gym
    2534 N Greenview
    Chambers of Commerce
    Lincoln Park Chamber of Commerce
    www.lincolnparkchamber.com
    1925 N Clybourn, Suite 30
    Chicago, IL 60614
    (773) 880-5200
    info@lincolnparkchamber.com
    President: Kim Schilf
    Property Tax Township
    Lakeview
    CPS Attendance Areas
    Elementary School
    William H. Prescott Elementary School
    www.prescott.cps.k12.il.us
    None Other 1632 W Wrightwood Ave. Principal Mr.Erin Roche
    Chicago, IL 60614 (773) 534-5505
    High School
    Lincoln Park High School
    www.lincolnparkhs.org
    2001 N Orchard St. Principal: Dr. Bessie Karvelas
    Chicago, IL 60614 (773) 534-8130
    I have all of my other tables created already (police district, street sweeping, garbage collection) except for street table. This table is the most important one because it brings all of my other data together.

  4. #4
    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
    It would be helpful to readers if you posted a jpg of your tables and relationships. I recommend you start with a data model of what you are trying to do. And that would include identifying all tables and relationships. Hopefully you have a list of "business rules" which your database is intended to support. Getting the design correct and matching/supporting the business rules is key.

    There are a number of free data models at http://www.databaseanswers.org You may get some ideas from these general models.
    http://www.databaseanswers.org/data_...all_models.htm
    Good luck with your project.

  5. #5
    Dal Jeanis is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    In general, YES, a surrogate key that is just an autonumber field would be preferable to creating some kind of composite key for the street section table.

    And, second, I didn't tell you that you have to use all the rows of data - I told you you have to understand what the data is used for before you make that decision. Is garbage pickup or recycling based upon odd/even numbers of the street? Then you have to keep it. Is police beat based on side of the street? then you have to keep it. Does school attendance break in the middle of the street? You have to keep it.

    On the other hand, if the side of the street isn't relevant to the other database tables, or to any of the usage cases for your database, then you DON"T have to keep it.

    I'm just some guy on the Internet. I don't know your data. You make the call, based on you understanding your data, and based on the business processes that your application and data is supposed to support.

    By the way, a brief look at the report you mocked up tells me that each of your other data sources is probably going to have to be married up somehow to this table. A section of street on one side might be part of multiple garbage routes or police beats, unless it's already been divided finely enough in your excel spreadsheet to cover that.

    You should probably look carefully at your data, and do an entity relationship diagram to prove to yourself how it should work. Check out http://www.rogersaccesslibrary.com/ for a tutorial on database design and some sample entity relationship exercises so you understand how to analyze your own needs.

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

Similar Threads

  1. Normalizing Data - Just help me talk it through
    By hrenee in forum Database Design
    Replies: 15
    Last Post: 10-18-2013, 04:47 PM
  2. Replies: 1
    Last Post: 05-23-2013, 10:00 AM
  3. Normalizing data
    By snowboarder234 in forum Forms
    Replies: 1
    Last Post: 10-24-2012, 05:02 PM
  4. Trouble with importing Excel Data
    By Monterey_Manzer in forum Access
    Replies: 2
    Last Post: 06-28-2012, 04:32 PM
  5. Trouble printing labels (data from Access) in Word
    By Austruck in forum Import/Export Data
    Replies: 2
    Last Post: 08-08-2011, 10:23 AM

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