Results 1 to 15 of 15
  1. #1
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153

    Lightbulb Intersection Inspections (Involving Multiple Linked Images)

    Ok, so I am working on a database for my company. Beginning next week and over the next several years, we will be inspecting approximately 35 different intersections in OKC. Each intersection can have 3 or 4 corners (some of the intersections are T intersections). Each corner will be inspected and a rating given to each component. Then an overall rating given to the entire intersection.
    The inspectors will be taking multiple photos to put with their inspections. The city then wants to be able to view (reports) the inspection data by intersection. So I began designing the attached database about 2 weeks ago. Now that it's time to start putting together the reports (which is something I always seem to do last), I've come upon some issues that I had overlooked during the table design time.
    The relationships in the database are as follows:
    tblStates.StateID (PK) - one2many - tblCities.State (FK)
    tblCities.CityID (PK) - one2many - tblStreets.CityID (FK)
    tblCities.CityID (PK) - one2many - tblIntersections.IntersectionCity (FK)
    tblStreets.StreetID (PK) - many2many - tblIntersections.IntersectionNS (FK)
    tblStreets.StreetID (PK) - many2many - tblIntersections.IntersectionEW (FK)
    tblInspectionImages.InspectionID (FK) - many2one - tblInspections.InspectionID (PK)
    tblIntersections.IntersectionID (PK) - one2many - tblInspections.IntersectionID (FK)
    tblInspectors.InspectorID (PK) - one2many - tblInspections.InspectorID (FK)
    tblCorners.CornerID (PK) - one2many - tblInspections.CornerID (FK)

    tblCorners is a "static" data table that will never change. It is literally only there for the sake of lookup and I'm now thinking is kind of dumb. Literally 4 entries "NE", "NW", "SE", "SW".

    Also, each inspection is going to end up being 4 (or maybe 3 if it's a T intersection) records one record for each corner. All with the same InspectionNumber, InspectorID, InspectionDateTime, and IntersectionID.
    I'm now thinking that is flawed. I maybe should have created two tables, one as tblIntersectionInspections and one as tblCornerInspections? With a relationship like: tblIntersectionInspections.InspectionID (PK) - one2many - tblCornerInspections.InspectionID (FK).

    Is there anyone who can take a look at what I've done and let me know what they think about this? And maybe point me in the right direction?

    It just keeps saying upload failed when I try and upload a .zip of the project.


    This is the .zip I uploaded to OneDrive and enabled sharing via link.
    https://1drv.ms/u/s!AtD5mbQ07lu7geBcYoq8UpHItZPV9w


    The .PDF is what these Inspector's will be filling out and turning in.
    NOTE: The .PDF is what these Inspector's will be filling out. (The 2 Page Intersection Inspection Report)
    NOTE: When you try and open frm_InspectionInformation you will get a pop asking you to re-link the images. The images are stored in the data->Images folder.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I agree that tblCorners may be taking normalization a bit too far. The descriptive value is so short, think I would just save the text instead of complicating with another table link. Combobox RowSource can still use the table or eliminate table and build ValueList.

    Why do you have two fields for NS/EW in Intersections table?

    I did a project like this about 12 years ago for a university GIS certificate. I based db structure on data I found from Portland, Oregon. Unfortunately, I no longer have files. Kicking myself. The inspection purpose was to evaluate intersections for ADA compliance. I found this on the city's website today https://www.portlandmaps.com/metadat...&LayerID=52778. Seems I had more info and vaguely recall emailing a contact to obtain complete project files.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by June7 View Post
    Why do you have two fields for NS/EW in Intersections table?
    We have a strict naming system within our company. All streets have to be NS & EW. This is mainly so the guys don't accidentally name an intersection EW & NS.
    I believe we are only going to be inspecting the intersections that are already entered. That was what i was told. So...i'm putting a little stuff in for future use in case the city decides they want ALL intersections within the city or another city decides they want the same kind of inspections done.

    What about the tblInspections, should i break this up into two tables?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Two identical tables - NO.

    I still don't understand the two FK fields. Why not one field for the link? If NS/EW is an attribute of the street, shouldn't that designation be in tblStreets?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by June7 View Post
    Two identical tables - NO.
    No it would be:
    tblIntersectionInspections (MASTER TABLE)
    -InspectionID
    -IntersectionID
    -InspectorID
    -InspectionName
    -InspectionDateTime

    tblCornerInspections (DETAIL TABLE)
    -CornerInspectionID
    -IntersectionInspectionID (FK)
    -MastArmFt
    -AnchorBoltsCS
    -etc



    Edit: This database will be containing inspections spanning at least 5 years. So i'm trying to anticipate a large amount of data. So i'm worried about redundant data. (ie 4 records all containing the same information for the Intersection, Inspector, Date/Time, InspectionName)

    Quote Originally Posted by June7 View Post
    I still don't understand the two FK fields. Why not one field for the link? If NS/EW is an attribute of the street, shouldn't that designation be in tblStreets?
    Maybe I'm not understanding how it could only be one? I'm not the best with data normalization.
    NS/EW is a field in tblStreets
    But the tblIntersections will be two different streets (one being NS and one being EW)
    There has to be two streets to make an intersection.

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Oh, duh, of course. Don't remember how I identified the streets associated with intersection/corners. I do remember creating a point shapefile for the corners and entering attribute data. However, after that clarification, I think what you have is reasonable and may be what I did, only without the multi-city/states complication.

    Blast, wish I hadn't tossed that CD a few years ago.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    Quote Originally Posted by June7 View Post
    Oh, duh, of course. Don't remember how I identified the streets associated with intersection/corners. I do remember creating a point shapefile for the corners and entering attribute data. However, after that clarification, I think what you have is reasonable and may be what I did, only without the multi-city/states complication.

    Blast, wish I hadn't tossed that CD a few years ago.
    I don't think a shapefile is necessary lol
    at this point, we are only going to be doing 35 intersections inside OKC, OK only. But i suspect that we may need this in the future for other cities that we are under contract with.

    And really the only states would be OK and ARK, we don't service any cities outside of that.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Well, only 2 states then tblStates, like tblCorners may be overkill, unless you have attributes like ContactName, ContactAddress, etc for each state.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  9. #9
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    No, if i listed the contacts they would be per city.

    How can i make the "Intersection needs immediate attention" popup wait until the form loads and shows before it pops up?

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That 2.55MB zip contains more files than probably needed to review this issue. However, if you had run Compact & Repair on the db, probably could have uploaded the zip here. C&R reduced the db from 15+MB to 4.5MB and the zip is less than 2MB (barely).

    Should the InspectionNumber be set only for new record?

    If Me.NewRecord Then Call SetInspectionNumber

    As for the data validation, that should probably be done in form BeforeUpdate event or a button Click event. I have never used form BeforeUpdate. I disable the X close and use a button.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    well i decided not to set it in the form_current, it's now just in the after update event of the date/time.
    that line was backwards anyways, if it's a new record there is no data to set as the number. and since it's not a number i changed it to IntersectionName.

    it needs to be set at FML-YYYYMMDDHHNN.

    That's just the project folder. I didn't realize I hadn't compacted it before compressing it. I've had a long weekend lol. We were out til 1 AM for the company christmas party last night. Then i had to have my dog at the vet at 8 AM for his shots.

    I went ahead and split the inspection table in to master-detail.

    I've got to shut down for the night. My brothers are here.

    Can you look and see why when i open the main form dbl click on intersection then select the edit and it opens the frmInspectinInformation that the next image button doesn't work until i hit the previous image then it will work. And the image counter above the images box says 1 of 1 until i get it to change the images.
    Attached Files Attached Files

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Didn't modify the popup code.

    I am getting 2 errors for broken image links. Annoying. Errors associated with the code to set Picture property of Image control. I have never used that property.

    I assume all images will be in same folder. I removed file path from ImageFile field and just left the filename. Disabled the code and set Image control ControlSource to: =CurrentProject.Path & "\Data\Images\" & [ImageFile]

    Changed VBA to: OpenFile (CurrentProject.Path & "\Data\Images\" & Me.txtImageFile)

    If images folder will not be in the same filepath as the db (frontend if split) that can still be handled dynamically with a table or statically with path string.

    What is the 'main' form - frmDataSheetIntersections? Double click does nothing. Manually opened frmInspectionInformation.

    Don't see an image counter.

    frmCalendar has a compile error.

    I never set field alias names in table.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ooops, wrong thread.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    the main form is the frmMainMenu

  15. #15
    breakingme10 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2014
    Location
    Oklahoma
    Posts
    153
    I am not certain how they are going to store the images right now. I suggested keeping them in a folder per intersection.

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

Similar Threads

  1. Replies: 1
    Last Post: 07-03-2017, 11:49 AM
  2. Replies: 2
    Last Post: 01-16-2015, 01:25 PM
  3. Linked .emf images will not center align.
    By sonoamore in forum Access
    Replies: 5
    Last Post: 11-13-2014, 07:01 PM
  4. Replies: 3
    Last Post: 10-15-2012, 01:18 PM
  5. Print Linked Images in a Report
    By bosve73 in forum Reports
    Replies: 4
    Last Post: 02-02-2012, 11:12 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