I work at an estimating company. I have been tasked with creating a new database in Access, which will allow easy tracking/reporting of Jobs (quote requests). Problem being, is that I am not very well-versed in Access, so I am kind of learning as I go.
1. My tables consist of:
-tblBranch (BranchID, BranchName, City, State, Zip, Phone, Fax)
-tblManager (ManagerID, ManagerName, BranchID, BranchName, Phone, Email)
-tblCustomer (CustomerID, ManagerID, ManagerName, CustomerName, Address, City, State, Zip, Phone)
-tblBuyer (BuyerID, CustomerID, CustomerName, BuyerName)
-tblPlanType (TypeID, PlanType)
-tblState (StateID, StateName)
-tblEstimator (EstimatorID, EstimatorName, City, State, Email)
-tblJobDetails (Quote_PK, QuoteNum, InputDate, JobName, City, State, DueDate, PlanTypeID, BranchID, ManagerID, CustomerID, BuyerID, Estimator ID)
-tblJobOptions (Options_PK, QuoteNum, Floors, OpenWeb, IJoist, Roofs, Walls, EWPFlush, EWPTouch, EWPEntire, Z4, TrusstoWall, NonTrussHW, FBlock) <<each of these are yes/no check boxes (aside from Options_PK & QuoteNum)
2. I have created a query that binds tblJobDetails with tblJobOptions, labeled it qry_Jobs. I only chose to do it this way because everyone I have spoken to says it is better to create forms off of queries instead of directly from tables.
3. I need to create a form as an easy way to add new jobs, so 'Data Entry' is set to YES in form properties. I used the Forms Wizard to create a form and set the ‘Record Source’ to qry_Jobs.
This populated the following fields:
1. QuoteID (textbox)
2. InputDate (textbox)
3. JobName (textbox)
4. City (textbox)
5. State (combo-box)
6. PlanType (combo-box)
7. BranchName (combo-box)
8. ManagerName (combo-box)
9. CustomerName (combo-box)
10. BuyerName (combo-box)
11. EstimatorName (combo-box)
4. My goal right now is to get the BranchName, ManagerName, CustomerName & BuyerName to work as cascading combo-boxes:
1. Select BranchName, populates list of Manager in that Branch in the ManagerName combo-box.
2. Select ManagerName, that populates a list of Customers under that Manager in the CustomerName combo-box
3. Select CustomerName, populates list of Buyers for that Customer in the BuyerName combo-box.
4. Select the buyer for that customer.
5. I would then like to insert a subform which will show all the checkbox options from tblJobOptions, so that I can assign Options to each job.
****I was able to get this exact form the way I wanted it, but I based it off of the Table tblJobDetails and the subform off of tblJobOptions. HOWEVER, people KEEP telling me that later on down the line, if I don’t create the form off of a query instead, that it is going to cause problems. So I am just trying to correct the problem now, before we even launch the database.
PROBLEMS:
1. On my original form that was based off of the table, everything works exactly as I want it to and all the data I entered went to all the right places. On this new form based off of the query, I can NOT for the life of me figure out why my combo boxes aren’t working. I have a feeling I am just telling it to pull from the wrong source or I have too many/not enough/incorrect relationships set up. I have tried so many ways to fix this but at this point I think I have just been confusing myself more and making it worse. PLEASE HELP!!!
2. Should I create a separate query for JobDetails/JobOptions for when I am creating the subform for Options? Right now the query the form is based off of consists of all the data from both tables in one query.
3. Can someone PLEASE explain to me, in English, not Access jargon (I am still learning), WHY it is so important to base forms off of queries in the first place?
I am sure I have 1,000,000 more questions, but I think these are my main problems for the time being.
Any help that you can provide would be EXTREMELY appreciated.
ForumHELP.zip
PS- I know I am asking for help and beggars can’t be choosers, but I would really like to understand how and why this whole database works. I am not looking for someone to quick fix it for me. So if you could please explain to me in as common-tongue language as possible, WHY it isn’t working and WHY whatever your suggestions are will help, that would make me VERY happy.
I have changed some of the work-specific information and attached the database I am working with for reference.
THANK YOUUUU!!!!!