Quote Request/Entry Database

[DaytimSoup]

I'm working on a database that will be used for entering and tracking our customers quote requests. I may be overthinking it, but I am lost on how I should structure it.

There are different components to the quote requests:

• Customers
• Salesperson
• Estimator
• Dates
  
  • Date Requested
  • Date Completed
  • Quote Submitted
  • Price Submitted
  
  
• Project Info
  
  • Jobsite Name
  • Jobsite Location
  • State
  • Project Name
  
  
• Bid Types
• Market Types
• Tracker Number (for each quote request)
• Various other info (don't want to make such a long post)

What I am confused on is the workflow for the various users. I need a data entry person to enter certain information when we get a new quote request, then I need the estimators to be able to view all of the quote requests and assign themselves to them, then mark when they complete them.

Should I use a one table database that puts all of this information in one table and create a form for data entry. Or have multiple tables and create forms and sub-forms for data entry, viewing, and assignment? Again, this database will really only be for entering and tracking this information.



I have created 3 separate databases so far and find sticking points with each one.

[mike60smart]

Can you upload a zipped copy of the database without any confidential data?

[DaytimSoup]

They are just tables pretty much, hopefully the snippet will help. I get to a point where I add forms and get stuck/frustrated so I delete the forms or query and start over. I am new to this, so I realize forms are only for one table, and I'd have to do sub-forms, but this may not be as user friendly. I feel like I am just thinking about the process in the wrong way. The second database was similar to the first the fields in the tables are different.





This one I was just trying to do one table and create forms for data entry and estimator assignment.

[mike60smart]

The way to go is definitely Main Form for Customer
Subform for jxtCustomerQuoteRequest

You do not need the field Customer in the tblQuoteRequests

[NoellaG]

What I'm missing here is the story what you want to achieve. Before you can start thinking about structures, you need to know how you're going to use the data. Something as:

- I want to be able to report how many quotes a customer has asked for a certain period. For each quote I need to report: the customer, date in, date ready, the estimator, estimator notes, ...
- I want the estimators to be able to see all new quote requests and assign a quote to themselves. Estimators can only alter quotes assigned to them
- I want to be able to automatically create an order from a quote
- I want to be able to see how much time is spend on creating the quote

ect.

[DaytimSoup]

Apologies for the late response. Forgot my password, and just now reset it.

Let me try to be a little more concise:

-I would like an overall view of each of the quote requests entered. This view will have thousands of records by the year end.
-I would like for a data entry person to enter specific quote information as it comes in. This person will enter certain info, and the estimators will fill in the rest later in the process.
-I would like for a group of estimators to be able to assign themselves to the quote requests, while entering additional information during the process such as material types, market types, & date completed
-I would like each estimator to have their own view of what they are assigned to. So I would need to have those records from that view
-I would like to create reports for various salespersons to view which requests they have in the various stages of the process, such as date entered, date due, date complete etc.
-I would like to be able to view one customers list of quote requests. I would also like to view one salespersons list of quote requests. I know this is achieved through the relationships, which I am really struggling with.

I have done some more work on the database and attached the zip file.

Right now I am stuck with the relationships.
-One salesperson can have many customers
-One customer can have many quote requests but quote requests can also belong to many customers.
-One estimator can be assigned to many quote requests, but only one estimator can be assigned to a quote request.
-Quote requests can have many material types.

I think I am struggling with understanding relationships the most.

[mike60smart]

Hi

I only took a quick look at your database and highlight the following:-

You should not use Characters like !"£$%^&*()/ in Fielkd names
You have used Lookup Fields in Tables which is a NO NO in Access. Google "The Evils of Lookup Fields in Access Tables"
You have Hard Coded values into Lookups in Fields in table. ie Lead/Copy

You have 72 Records in tblQuoteRequests
You have 372 Records in tblCustomers and you have stored the CustomerID rather than the CustomerName

You have a table named "jtblQuoteRequestJunction" which links Customers to Requests, However
you have absolutely no data in this table ???

I take it you added all the data directly into the 2 tables manually without using a Data Input Main Form / Subform ?

[DaytimSoup]

You should not use Characters like !"£$%^&*()/ in Fielkd names
I can take out the / from Lead/Copy
You have used Lookup Fields in Tables which is a NO NO in Access. Google "The Evils of Lookup Fields in Access Tables"
I've read that, and watched some videos about this as well. I think I only have a Lookup Field for Lead/Copy.
You have Hard Coded values into Lookups in Fields in table. ie Lead/Copy
My thought process was, I will never need to sort, display, or do anything with a Lead/Copy. This information is to differentiate from which customers have requested a quote for the same project after the first customer.
You have 72 Records in tblQuoteRequests
These are the Quote Requests I have entered so far. We have thousands from this year in an excel sheet, what we use to track currently, but I don't have time to enter all of them. Those are from the beginning of last month.
You have 372 Records in tblCustomers and you have stored the CustomerID rather than the CustomerName
We have a set of permanent customers who are assigned to various sales personnel and who order from us on a regular basis. It's not like someone placing an order through an online shop, where there are different customers everyday. Can you please expand on storing the CustomerID vs CustomerName? I'm certain you are pointing out something I did incorrectly, but I am not sure what.

You have a table named "jtblQuoteRequestJunction" which links Customers to Requests, However you have absolutely no data in this table ???
I was doing some research on this last night and realized where I am going wrong for the relationships and junction tables. I didn't fully understand how they worked, I have a better idea now, but am still somewhat confused.
I take it you added all the data directly into the 2 tables manually without using a Data Input Main Form / Subform ?
You are correct, all of the data with the exception of what is in the QuoteRequests table. For that I used the NewRequest form.

Like I said before, the only new info we will get is new order requests. For the most part our customer base will remain the same, we only add new customers a few times a year.


This is the process I am trying to capture, we are currently using an excel for this.
-A customer who is assigned to a salesperson sends in a request for a quote.
-Data entry enters the information shown on the NewRequest form.
-An estimator will view the list of requests and assign themselves to on or many.
-They will then fill in additional information such as date completed and material information.
-Sales personnel will need to view the tracker to get an idea of where their customers quote requests are in the process.

[mike60smart]

Hi

Your Lookup field is on the field "SalespersonID" in tblQuoteRequests.

You can delete the table named "jtblQuoteRequestJunction" from your ER Diagram and just link Customer directly to Quote Requests

[DaytimSoup]

Got it, I have changed the SalespersonID from a lookup to Text, and also changed from ShortText to Number.

But I need a quote request to be able to be associated with multiple customers. Also, should I link Salesperson to directly to QuoteRequests or is it fine linked to customers?

Edit: Never mind, it seems to show properly now that I got the relationships set up correctly, I guess I was over thinking it.

[mike60smart]

How can 1 Quote be associated with Multiple Customers??

[DaytimSoup]

Because multiple customers can bid on the same project.

Say Walmart needs a building, they will send out this project for bid to multiple builders, Builder A and Builder B. Both A & B are our customers and need our material. They send the project information to us to get our pricing, which they will then include in their pricing and send to Walmart. Walmart will then award the project to either A or B who will then award us the contract for our material.

[mike60smart]

Well from the way I see this The Customer would send you a PurchaseOrder for Materials required for whichever Project.

I can't see how WalMart would award you the Contract when the Customer has won the Bid for the Contract.

[NoellaG]

Attachment 50852Hi,

I would work with a models type. If a quote needs to be used for different customer, I would start with creating a set of models. These models are the base of each new request and would have all properties that can be reused in the different request. In the actual request you add data like who's the customer, which estimator will handle the request ect.
The customer contains the link to the sales person (1 sales person can handle different customers, but a customer can only have one sales person.
Example:

[DaytimSoup]

Awesome, thank you. I will build this way and try to see if I can make it work.

I think I have been thinking about it wrong. We issue "Tracker Numbers" to each quote request. So I think I need to build around this tracker number because essentially each project will be associated with 1 tracker number regardless of the number of customers.

Thank you all for the help.

@mike60smart in my example Walmart wouldn't issue us a contract, Walmart would issue Builder A the contract, and Builder A would issue us a PO for our material.