Results 1 to 8 of 8
  1. #1
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24

    street and subdivision table


    I have a database with a street name table and a subdivision table in it. I need to associate each street with the subdivision it belongs it. A street can be belong to multiple subdivisions. The street name table is very clean, it has an id field (autonumber) and a street field. The subdivision table lists each subdivision multiple times. An entry is made for each phase of the development. I was thinking of adding a new table named StreetAndSub with two fields: street and subdivision. I would have the street field be a query to the street name table. I would have the subdivision field query the subdivision table with a SELECT DISTINCT. Then I would just select each combination as needed. I will need two reports at the end of this. One will be a list of all streets and its associated subdivision. The other will be a list of subdivisions and what streets it contains. I have 375 streets and 60 unique subdivisions. Is this a good way to achieve my goal?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Some things requiring more info or examples:

    -A street can be belong to multiple subdivisions.
    -The subdivision table lists each subdivision multiple times

    Are these things(streets and subdivisions) all within a specific City or Region?


    I'm not sure where this fits in your environment:
    "each phase of the development".

  3. #3
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    How about this?
    It's a Many-to-many arrangement.
    The street/subdivision details are stored in the junction table, which is the recordsource of the subform.
    Click image for larger version. 

Name:	SNS.JPG 
Views:	12 
Size:	29.4 KB 
ID:	35473
    Last edited by davegri; 09-13-2018 at 02:08 PM. Reason: add image

  4. #4
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by dsmithe View Post
    The subdivision table lists each subdivision multiple times. An entry is made for each phase of the development.
    You need a subdivisions table with a single entry for every subdivision!
    Then you can have a SubdivisionDevelopments table (one Subdivision > many Developments).

    Quote Originally Posted by dsmithe View Post
    A street can be belong to multiple subdivisions.
    Can a Subdivision have multiple streets or single one?. I.e is a Subdivision an independent category, or a subcategory of Street?
    You need a StreetSubdivisions table anyway, but how you go from there on may depend on your answer on previous question. With first option, you have to set up the boundaries (to define Street objects - e.g. house numbers - range belonging to Subdivision), or assign all Street objects to Street and to Subdivision. With second option, you assign all Street objects to Subdivision only.

  5. #5
    dsmithe is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2018
    Posts
    24
    @orange

    Code:
    
    STREET                                                 SUBDIVISION
    ABBEY RD                                             THREE SPRINGS
    ACORN CT                                            HIDDEN LAKE
    ACORN DR                                            HIDDEN LAKE
    ADLER CV                                             THE GATES
    ALLISON LN                                          THREE SPRINGS
    ALVINA ROSE CT                                  ROSEWOOD ESTATES
    AMBERLEY LN                                      CLARMONTE RIDGE
    
    Subdivision                                                   Street
    Hidden Lake                                                 Acorn Ct
    Hidden Lake                                                 Acorn Dr
    Hidden Lake                                                 Cranberry Ln
    Hidden Lake                                                 W 89th Pl
    

    They are all a part of 1 municipal corporation which has 5 different cities for mailing addresses.

    If a developer has 1 square mile of land he wants to put 1,000 homes on he must install all of the utilities: watermains, sewer, storm sewer, street lights, streets, sidewalks, etc. before they can sell lots to people. The developer can't afford to put in all of the utilities at once so they break the subdivision in phases. They install all of the utilities for that phase only. Rolloing Meadows Phase 1 has 25 homes, Phase 2 has 25 homes, Phase 3 has 15 homes, etc until the subdivision is built out. So my subdivision table has fields for subdivision name and phase number.

    @Davegri
    I will take a look at the database shortly.

    @ArviLannemets
    I didn't design the original database I have to make it work. It has 21 years of data in it. There is a lot more wrong with it than just this. I have been cleaning it up for the last 3 months with no end in sight. I just finished pulling the StreetNames from the masterpermittable. All of the street names are now selectable from a Combo Box. No more misspellings.

    A subdivision can have 1 street or 15 streets. A street can belong to more than 1 subdivision. Orland Av is an example of this. It runs through subdivisions: Cardinal, Pines, and Rosewood. I don't have to worry about street number ranges for this database. That is handled on the GIS side.
    Last edited by dsmithe; 09-13-2018 at 08:56 AM. Reason: fix formating

  6. #6
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    Do you already have a table (or source of data) that contains street and subdivision? i.e.

    street subdivision
    Maple St Sub1
    Maple St Sub2
    Main St Sub1
    Elm St Sub3
    Cherry St Sub3
    Cherry St Sub4

  7. #7
    JSR is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2018
    Location
    Reno, NV
    Posts
    41
    It sounds like you need these tables:

    1) streets (one row for each street)
    2) subdivisions (one row for each subdivision)
    3) subdivision phases (one row for each phase of a subdivision)
    4) street subdivisions (one row for each street AND subdivision, which is just one if the street exists in only one subdivision, but N rows if the street exists in N subdivisions)

    Primary key for 1) and 2) are foreign keys in 4). Primary key for 2) is a foreign key in 3).

  8. #8
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by dsmithe View Post
    I didn't design the original database I have to make it work. It has 21 years of data in it.
    When you can't rename current Subdivisions table, let it be. But it really isn't Subdivisions table - it is really a SubdivisionBuilds table whatever it's name currently is. You really need a registry of all subdivisions! You can create a real Subdivisions table with whatever name you like, fill it with append query from current subdivisions table using DISTINCT clause, and use it in your part of work as a real Subdivisions registry.

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

Similar Threads

  1. Replies: 10
    Last Post: 02-13-2017, 01:36 PM
  2. Replies: 4
    Last Post: 09-01-2015, 05:33 AM
  3. Street Address with alphanumeric
    By ampstar in forum Access
    Replies: 4
    Last Post: 08-07-2014, 11:28 AM
  4. Replies: 2
    Last Post: 10-27-2009, 07:09 AM
  5. Separating Street Number from Street
    By NGallone in forum Queries
    Replies: 0
    Last Post: 10-29-2008, 08:51 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