STRUCTURE OF THE DATABASE
- I work for an estimating department and I am creating a database for work which will help us to track each quote request.
- 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)
- We process quote requests for 8 different branches, which include 100+ clients (lumber companies) that could be submitting the requests.
- Each client is assigned a Sales Person. (account manager)
- 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)
- 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:
- What happens when we have multiple requests for the same job from different clients, each needing different options.
- 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?
- 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
- Open db to frmSearchJobs > search for job
- If found, double click to open in frmTabbed
- 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
- Hopefully include a listbox on frmTabbed to toggle between the active requests without having to re-search.
- I will probably change the subforms so each page will be designated to each option.
- 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.
- 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)
- 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.
- ***I CANT add jobs using the frmBidLog right now and don't know why***
- The date pickers will not work.
- I cannot get the VBA code to open frmTabbed to current record on frmBidLog on click of btnSave
- 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