Results 1 to 11 of 11
  1. #1
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81

    Help organizing a table or tables

    My company is updating their computers and our current DOS based software won't work anymore. We're upgrading to Access! Woohoo! Therefore this year, I have been working to learn Access and create the system that everyone will use. We are a trucking company which exclusively hauls US mail. We dispatch drivers out of 9 different cities in 4 states. My coworkers who will be using the system are not very good with computers, so the DOS based system was great for them.



    So far, I have Drivers, Post Offices, Vendors, and Purchase Orders forms and tables. They're not done, but started. I'm starting to work on a table(s) and form(s) for the Trips. I've attached a screenshot of what our current Alpha 4 Trips database looks like.

    Click image for larger version. 

Name:	Alpha 4 Screenshot.JPG 
Views:	27 
Size:	67.5 KB 
ID:	24632
    I have a tblPOFacility table which lists all the Post Offices we go to and tblContract which lists all the contracts we have.

    The fields I am sure I would like in my tblTrips would be:
    TripNumber
    TripRate
    Contract_ID (FK related to tblContract.ID)
    POTripNumber
    Trip Title
    TimeZone
    LoadTime
    LoadPO (somehow related to tblPOFacility)

    I know I'll need Leave and Arrive times and that will need to be at different Post Offices. It seems my table gets really confusing when I add Time1, Time2, Time3, Time4... Time26, Time27... and then there's the Post Offices... PO1, PO2, PO3... Should I make a TripTimes table? Will I need to make a many to many relationship between tblPOFacility and tblTrips?

    I think I'm over-complicating things in my brain and just need someone to say the magic word that makes it all come together. Anyone got that magic word?

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've attached a screenshot of what our current Alpha 4 Trips database looks like.
    Do you have a current backup of the Alpha 4 database?
    Can you use a copy of the backup and look at the tables in the database?

    That is where I would start........

  3. #3
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Unfortunately, I don't know enough about Alpha 4 to be able to find anything. I can use the forms and it looks like whoever made this particular database for us used a lot of redundancy. I am hoping to avoid having that much redundancy in the new system I am building. For instance, I can see where they have over 100 different fields for the trip times. And rather than being linked to the Post Office table, the current trips form just has the user type in Post Office names. Sometimes a Post Office will go by a few different names and all three names will show up on different trips.

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was hoping you could get a jump on table design by looking at the Alpha 4 design.


    I can see where they have over 100 different fields for the trip times
    Definitely in need of redesign!

  5. #5
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    After your post, I tried going into Alpha 4 to find something that would make sense. I found nothing. It looks like rather than tables, it uses what it calls databases. Each form has it's own database. Each form has it's own table. They can reference one another though.

    Anyway, yes, definitely in need to a redesign. I'm basically starting from scratch. Up until now, the tables I've made have been pretty easy to organize. This is the first one that really has me stumped. Do I do a junction table with TripNumber, TripTime, and POFacility? I just don't know where to start!

  6. #6
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Good afternoon everyone. I've been thinking and working and thinking and working. Here's what I got goin'. Please tell me if I'm even heading in the right direction.

    tblTrips
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.48.12 PM.png 
Views:	18 
Size:	45.9 KB 
ID:	24643

    Contract
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.48.24 PM.png 
Views:	18 
Size:	27.7 KB 
ID:	24644

    tblTripTimes
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.48.00 PM.png 
Views:	17 
Size:	36.5 KB 
ID:	24645

    tblPOFacility
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.47.50 PM.png 
Views:	19 
Size:	71.8 KB 
ID:	24646

    jctTimesPOs
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.47.41 PM.png 
Views:	17 
Size:	33.7 KB 
ID:	24647

    I've created relationships between them, but I can only add 5 photos per post, so I'll add the screenshot separately.

  7. #7
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    And here are the relationships:
    Click image for larger version. 

Name:	Screen Shot 2016-05-18 at 3.47.20 PM.png 
Views:	20 
Size:	72.9 KB 
ID:	24648

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Before populating your tables with all of your data, you should get a data model created and tested. You really should write a narrative describing your business; add details as they are uncovered/discovered, then show us the narrative and the associated model.You build and test a model with typical data to validate the model. You make adjustments as "incidents" arise (bad assumption, missing entity, additional attributes...) Then go back and retest the model till it works as per your requirements.
    Now you have a blueprint for your database.

    Good luck.

  9. #9
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Quote Originally Posted by orange View Post
    you should get a data model created and tested.
    Do you know of any good tutorials on how to create and test a data model? Crazy as it may sound, I have never heard these words before . This is my first db and though it is difficult, I really enjoy all I am learning.

  10. #10
    orange's Avatar
    orange is online now Moderator
    Windows 8 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Yes. Work through 2 or 3 of these tutorials. They each start with a well written narrative/description, and lead you through a process that can be used with any database. It will lead you to a well designed data model that will support the business requirements.
    You must work through the tutorials. I suggest 2 or 3 just for practice and familiarity with the process.
    Each tutorial will take 30-60 minutes (or less). If you have any issues post back. There is other info that may be useful to you.

    Good luck.
    Class info
    Consolidated Widgets
    Entity Relationship Diagramming

    You should also read this material on Normalization.

  11. #11
    CharissaBelle is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Mar 2016
    Posts
    81
    Thank you orange. I'll go through those this afternoon.

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

Similar Threads

  1. Grouping, categorizing and organizing Access objects
    By Access_Novice in forum Access
    Replies: 1
    Last Post: 10-30-2014, 10:33 AM
  2. Re-organizing records in a Table
    By swb1 in forum Access
    Replies: 1
    Last Post: 07-28-2014, 05:03 PM
  3. Need help re-organizing data
    By saccolicious in forum Access
    Replies: 2
    Last Post: 10-13-2012, 07:15 AM
  4. Replies: 17
    Last Post: 01-12-2012, 11:25 AM
  5. organizing serial numbers and quantities
    By Diomeneus in forum Access
    Replies: 0
    Last Post: 11-14-2008, 03:17 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