Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    1

    Question Unanswered: State to State search

    I am trying to learn Access, and I am not much of a book person, so I figured I'd try to build a database to get hands on. So any help you can offer would be appreciated!
    I am working on building a database of trucking companies that want to go from point A to point B (state to state is a good enough criteria) and I am finding that Access isn't quite as easy as I had thought.
    I know very little about Access... well databases in general, so I am needing some help. Here is what I am wanting the end user to experience (I need help putting the guts together):

    I want the end user to select origin state and destination state and get a query/report of the top 5 trucking companies in my database sorted by price, but listing their contact info also.

    I have built a table of Carrier Information (Carrier Name, phone#, Address, etc), and I am using the Carrier Name as my primary key.

    Here is what I need help with. If a CarrierA only wants to originate in CA, OR, and WA, and only wants to go to NJ, DE, and MD, and CarrierB wants to go only out of TX to IA, how do I build a table/query to capture that?
    I also need to be able to store their rate somewhere. Should that go in the same table, or in a different table all together?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just a few hints:

    1. Build a table with all states (2 columns: PK + Full Name, ex. 'OR' + 'Oregon').

    2. Build a table that will connect a carrier with each of its Origins and Destinations (3 columns: Carrier + State + Type -'O' or 'D' exclusively-, ex. 'Johnson Trucking' + 'WA' + 'O', 'Johnson Trucking' + 'WA' + 'D', 'Johnson Trucking' + 'OR' + 'D'). In this example 'Johnson Trucking' starts from Washington and goes to Washington and Oregon.
    Note that for a better efficiency I would add a surrogate key to the Carrier table and use it instead of a natural primary key (Carrier Name), so we would have:
    Carrier: 19, 'Johnson Trucking', <Phone>, <Address>, <other carrier info>...
    Links: 19 + 'WA' + 'O', 19 + 'WA' + 'D', 19 + 'OR' + 'D'

    3. Create a composite key based on the 3 columns of the Links table (or at least a UNIQUE Constraint).

    4. Beyond Indexes you can use Relationships and Constraints (UNIQUE, Validation Rules, Foreign Key) to enforce the integrity of the data model.

    5. To retrieve the PK of carriers from TX or WA:
    Code:
    SELECT Tbl_Carrier.SysCounter
    FROM Tbl_Carrier INNER JOIN Tbl_Links ON Tbl_Carrier.SysCounter = Tbl_Links.FK_Carrier
    WHERE ((Tbl_Links.Type="O") AND (Tbl_Links.FK_State="WA")) 
       OR ((Tbl_Links.Type="O") AND (Tbl_Links.FK_State="TX"))
    GROUP BY Tbl_Carrier.SysCounter;
    6. To retrieve the PK of carriers to OR or WA:
    Code:
    SELECT Tbl_Carrier.SysCounter
    FROM Tbl_Carrier INNER JOIN Tbl_Links ON Tbl_Carrier.SysCounter = Tbl_Links.FK_Carrier
    WHERE ((Tbl_Links.Type="D") AND (Tbl_Links.FK_State="OR")) 
       OR ((Tbl_Links.Type="D") AND (Tbl_Links.FK_State="WA"))
    GROUP BY Tbl_Carrier.SysCounter;
    7. You can combine or associate both sorts of queries (Origin and Destination) to search on both criteria.

    8. I don't know enough as far as Rates are concerned. I guess that they depend on several factors (Distance, Weight, Value, etc.), but more tables and more rules will certainly be necessary.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •