Results 1 to 3 of 3
  1. #1
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52

    Table/Relationship Structure- Please help.

    STRUCTURE OF THE DATABASE

    1. I work for an estimating department and I am creating a database for work which will help us to track each quote request.
    2. Once we receive a request, we input it into an outside system, which will spit out an automatic quote #. (########B = the format of the quote #; ie. 16071195B)
    3. We process quote requests for 8 different branches, which include 100+ clients (lumber companies) that could be submitting the requests.
    4. Each client is assigned a Sales Person. (account manager)
    5. Depending which client submits the request, we could be estimating multiple different options/combinations of options per client per quote. (walls, roofs, floors, ewp, zone4, hardware)
    6. Once a job is complete, we assign a dollar amount to it and mark it as completed. (but leave it in our system)


    I have been self-teaching how to create this database, and have learned so much. However, every time I think I have the structure of the database I realize there is another road block, usually having something to do with my relationships that are set up. I have attached a copy of the current structure of my database. I am sure that I will have many more questions as I progress through this, but right now my main concern is:

    1. What happens when we have multiple requests for the same job from different clients, each needing different options.
      1. Is there a way to set up a form so when creating a duplicate request I can have the system automatically attach "-1" to the end of the quote # if it already exists? At which point I can assign a separate branch/client/account manager/options?
      2. When using frmSearchJobs, will all "starting with" quote # appear even if the +1 was added?


    I also listed below a brief overview of how I would like to navigate through the forms.
    Could someone please take a look at how I have my tables/relationships set up and tell me what I am doing wrong here?

    NAVIGATING THROUGH DB

    1. Open db to frmSearchJobs > search for job
      1. If found, double click to open in frmTabbed
        1. I want to add a button that will allow me to copy this job to another client (in case of multiple requests); adding the +1
          1. Hopefully include a listbox on frmTabbed to toggle between the active requests without having to re-search.

        2. I will probably change the subforms so each page will be designated to each option.
          1. Ie: CompanyA is requesting a quote #: 16015532B with walls & floors for branch 221 but CompanyB is requesting the same quote #: 16015532B with roofs & floors for branch 301.
            1. I will need to be able to have each version of the quote have it's own records in the Options tables (tbl2Floors, tbl2Roofs, tbl2Walls, tbl2EWP, tbl2Zone4, tbl2Hardware)

      2. If not found, click Add New button to open frmBidLog to add a new record to tbl1Projects > save button > open to frmTabbed to set options.
        1. ***I CANT add jobs using the frmBidLog right now and don't know why***
          1. The date pickers will not work.
          2. I cannot get the VBA code to open frmTabbed to current record on frmBidLog on click of btnSave


            1. I think this is because it isn't actually copying into tbl1Project?


    **One particular Company (Company2) also has their own estimator assigned to each request, I use tbl1SLEstimators to track that.FORUM-Structure.zip
    Last edited by AishlinnAnne; 03-21-2017 at 01:29 PM.

  2. #2
    AishlinnAnne is offline Advanced Beginner
    Windows 7 32bit Access 2016
    Join Date
    Aug 2016
    Location
    Mass
    Posts
    52
    I was thinking maybe in my main table, tbl1Projects, that I change the PK to automatically be assigned, then use QuoteNumber as a unique identifier (still adding the +1 or +i as integer = 1) and create multiple jobs linked to the auto-filled PK with varying quote #s in tbl2JobHistory. But I still feel like that will not correct the problem I am having getting multiple options/branches assigned to one job. I need to keep most recent previous, present and future quote data logged in the system by client.

  3. #3
    HiTechCoach's Avatar
    HiTechCoach is offline MS MVP - Access Expert
    Windows 10 Access 2013 32bit
    Join Date
    Jul 2010
    Location
    Oklahoma, USA
    Posts
    702
    Quote Originally Posted by AishlinnAnne View Post
    I was thinking maybe in my main table, tbl1Projects, that I change the PK to automatically be assigned, ...
    I agree. IMHO, Best practice is to always use a system assigned number as the primary key. In my Access applications the end user rarely sees the actual primary key.


    Quote Originally Posted by AishlinnAnne View Post
    ... then use QuoteNumber as a unique identifier (still adding the +1 or +i as integer = 1) and create multiple jobs linked to the auto-filled PK with varying quote #s in tbl2JobHistory. But I still feel like that will not correct the problem I am having getting multiple options/branches assigned to one job. I need to keep most recent previous, present and future quote data logged in the system by client.
    I would expect a separate quotes table. Which would probably replace the tbl2JobHistory.

    If this were mine I would have

    Project (1) -> (many) Quotes [quote number in this table]. Quotes would have all the detail (related tables) that you currently link to a project.



    I have done database applications similar to this in the past. I have always combined everything (Roof, floors, walls, etc) in a single table with one row per item. In your case, this allows any new "feature" to be added without any design changes to any table. Only adding data. With your current design, this will be very difficult.

    In my experience, with tables properly normalized you are able to create multiple workflows (navigation) as needed without a lot of extra work (VBA code etc).

    The opposite is also true with poorly normalized tables. They greatly limit the flexibility of the workflow (navigation) you can create without lots of extra VBA coding and greater programming skills.

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

Similar Threads

  1. Table Design - Relationship Structure
    By djspod in forum Database Design
    Replies: 2
    Last Post: 03-09-2017, 10:43 AM
  2. Replies: 3
    Last Post: 07-02-2015, 09:15 AM
  3. Replies: 4
    Last Post: 11-04-2013, 02:24 PM
  4. Relationship Structure
    By buckwheat in forum Access
    Replies: 1
    Last Post: 07-12-2013, 01:12 PM
  5. Schedule: Table/relationship structure
    By capnponcho in forum Access
    Replies: 1
    Last Post: 12-18-2011, 01:24 AM

Tags for this Thread

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