Solved Help with Table Design! (1 Viewer)

[slharman1]

I have a table of estimate records (and related calculated fields on a form) and and need to add that record to a quote record, That's the easy part!, I need to be able to edit some of the quote records fields (price and sometimes description) with-out changing the related record in estimates. Also, I need to be able to add a record to the quote table without using the related estimates table (on the fly record)

I can't seem to get my head wrapped around where to start in creating the quote table
Thanks in advance

 

[theDBguy]

Hi. I deleted your duplicate post and moved this one to the Tables forum. There's no need to ask the same question more than once or in multiple forums.

 

[slharman1]

Hi. I deleted your duplicate post and moved this one to the Tables forum. There's no need to ask the same question more than once or in multiple forums.

Thanks, sorry about the double post. I wasn’t sure where to post the info.

 

[MajP]

You will have to give some more detail to get an answer. Can you describe the related fields and especially any calculated fields? Can you describe your process and maybe an example? A screen shot of your relationship window may help because it would show the tables, fields, and how related.

 

[Pat Hartman]

In my mind (such as it is), a quote and an estimate could be the same thing. Either would happen prior to a sale and both would give the client some idea of what the product/service would ultimately cost. Quote implies a firmer price than estimate. Otherwise I'm not sure what the difference would be. So, the fact that you have both is confusing. Are you using the estimates table as a way of establishing the quoted price for something? If that is the case, then the solution is to store the estimated price on the quote. Add the price to the RowSource of the combo that picks the product/service. Then in the AfterUpdate event of the combo, copy the price to the bound quote price field.

Me.QuotePrice = Me.cboService.Column(2)

The columns of the RowSource are a zero-based array. Therefore .Column(2) refers to the THIRD column. I'm assuming the first is ServiceID, the second is a description, the third is the EstimatedPrice.

This is not a violation or normalization rules since the QuotePrice is the EstimatePrice at a point in time. The point in time when the quote was made. There are other ways to do this but they tend to be more complicated. Using this method, changes to the price on the estimates table would not change the price on anything that has already been quoted but will be used as the price for future quotes.

Now, based on your business rules, you might want as a policy to update the quote price if it hasn't yet become a sale if the new price is lower. If so, you would need an update query to update the price on all pending quotes in the AfterUpdate event of the form that changed the estimates price.

 

[slharman1]

In my mind (such as it is), a quote and an estimate could be the same thing. Either would happen prior to a sale and both would give the client some idea of what the product/service would ultimately cost. Quote implies a firmer price than estimate. Otherwise I'm not sure what the difference would be. So, the fact that you have both is confusing. Are you using the estimates table as a way of establishing the quoted price for something? If that is the case, then the solution is to store the estimated price on the quote. Add the price to the RowSource of the combo that picks the product/service. Then in the AfterUpdate event of the combo, copy the price to the bound quote price field.

I should've been clearer in my explanation.
The estimate is in house info only.
We estimate custom fabricated products, like a cabinet, it has all of the materials and labor in it. Then we prepare a quote with the total for the cabinet on it.
I think I've moved ahead a little, I should not be calculating the fields on the form but in the underlying query so I can use that for the quote query.
Does that sound like a reasonable approach. I think that will solve all of my issues at least to start with.

Me.QuotePrice = Me.cboService.Column(2)

The columns of the RowSource are a zero-based array. Therefore .Column(2) refers to the THIRD column. I'm assuming the first is ServiceID, the second is a description, the third is the EstimatedPrice.

This is not a violation or normalization rules since the QuotePrice is the EstimatePrice at a point in time. The point in time when the quote was made. There are other ways to do this but they tend to be more complicated. Using this method, changes to the price on the estimates table would not change the price on anything that has already been quoted but will be used as the price for future quotes.

I think this is exactly what I need!

Now, based on your business rules, you might want as a policy to update the quote price if it hasn't yet become a sale if the new price is lower. If so, you would need an update query to update the price on all pending quotes in the AfterUpdate event of the form that changed the estimates price.

I also need to be able to add a record on the quote that does not even have a related estimate price on it - this is where it gets complicated for me.
Thanks Pat it has been 20 years since I wrote my one and only database.

 

[linxpatrick]

I have a table of estimate records (and related calculated fields on a form) and and need to add that record to a quote record, That's the easy part!, I need to be able to edit some of the quote records fields (price and sometimes description) with-out changing the related record in estimates. Also, I need to be able to add a record to the quote table without using the related estimates table (on the fly record)

I can't seem to get my head wrapped around where to start in creating the quote table
Thanks in advance

The way I imagine accomplishing what you are talking about is something like this.

You will have a table for the description of the project. At a minimum it will require a record ID that uniquely identifies the record so that you can link records in other tables to it. The record ID should not be used for any other purpose but it can be an auto-number and the primary key.

You may have another table that stores the client information or the client information may be stored with the estimate. This depends on if you want the ability to have repeat clients without having to enter client information more than once.

There will be another table that holds the selection of cabinets that the client wants (could hold other data like the counter tops and other hardware). Each cabinet, part or assembly being a different record.

Create a main form where you enter the main details about the estimate. The main details would include a description of the project, possibly the estimated start and completion dates, the actual completion dates, the record creation date and who created the record. Add other fields as you see fit.

In a sub form show the list of selected items (cabinets, assemblies and other hardware). The way I typically do this is to create a list box control that displays the basic information about each item. Beside the list box is a set of buttons for adding, editing and deleting items. All of the data entry is performed in a detail form that opens for adding and editing the item details. Item details would be the item and hours and possibly other information about how the item is used or where it will be installed.

With this configuration you would create the estimate record and only change it from time to time. The rest of the details would be held in a child table. You would enter the details for the estimate there. That table would contain the details for the quote. Summing up values from that table could then be displayed in the main form as the total for the quote and on reports for the same purpose.

This configuration would also set you up to be able to create multiple quotes for the same estimate. To do that you would create an additional, junction table that goes between the estimate and the details. There would be one junction table record that has its own ID, a description of the quote and the ID of the main estimate record. The detail records would then link to this table. Each set of detail records would link to a different quote (junction) table record. You could use a combo box control in the main form to select the quote you want to work on and pass that record's ID into each new detail record.

Hope that makes sense.

 

[Pat Hartman]

Here's a picture of a form that shows three methods of filling other form fields. Although the picture should give you what you need, I included the actual sample database so you can "touch" it. The sample also includes other techniques of interest.

 

[Pat Hartman]

I also need to be able to add a record on the quote that does not even have a related estimate price on it - this is where it gets complicated for me.

You probably need a table that defines the services/products you sell and possibly child tables with potential customization options. If you go with Options, even the items without options need at least one option record since that is where the default price and description would come from.

If your stuff is totally custom, you might not be able to make a "price list" type of table. In that case, you should still add the new items to the original estimate before copying them to the order. That just keeps the process clean. Otherwise, you have to code and manage at least two different ways to get items on a quote. Go with KISS.

 

[slharman1]

The way I imagine accomplishing what you are talking about is something like this.

You will have a table for the description of the project. At a minimum it will require a record ID that uniquely identifies the record so that you can link records in other tables to it. The record ID should not be used for any other purpose but it can be an auto-number and the primary key.

You may have another table that stores the client information or the client information may be stored with the estimate. This depends on if you want the ability to have repeat clients without having to enter client information more than once.

There will be another table that holds the selection of cabinets that the client wants (could hold other data like the counter tops and other hardware). Each cabinet, part or assembly being a different record.

Create a main form where you enter the main details about the estimate. The main details would include a description of the project, possibly the estimated start and completion dates, the actual completion dates, the record creation date and who created the record. Add other fields as you see fit.

In a sub form show the list of selected items (cabinets, assemblies and other hardware). The way I typically do this is to create a list box control that displays the basic information about each item. Beside the list box is a set of buttons for adding, editing and deleting items. All of the data entry is performed in a detail form that opens for adding and editing the item details. Item details would be the item and hours and possibly other information about how the item is used or where it will be installed.

With this configuration you would create the estimate record and only change it from time to time. The rest of the details would be held in a child table. You would enter the details for the estimate there. That table would contain the details for the quote. Summing up values from that table could then be displayed in the main form as the total for the quote and on reports for the same purpose.

This configuration would also set you up to be able to create multiple quotes for the same estimate. To do that you would create an additional, junction table that goes between the estimate and the details. There would be one junction table record that has its own ID, a description of the quote and the ID of the main estimate record. The detail records would then link to this table. Each set of detail records would link to a different quote (junction) table record. You could use a combo box control in the main form to select the quote you want to work on and pass that record's ID into each new detail record.

Hope that makes

You probably need a table that defines the services/products you sell and possibly child tables with potential customization options. If you go with Options, even the items without options need at least one option record since that is where the default price and description would come from.

If your stuff is totally custom, you might not be able to make a "price list" type of table. In that case, you should still add the new items to the original estimate before copying them to the order. That just keeps the process clean. Otherwise, you have to code and manage at least two different ways to get items on a quote. Go with KISS.

Thanks! I am just now looking at this. Work has been kicking my but and I need to get this database finished to simplify my job.
Thank you.

 

[slharman1]

You probably need a table that defines the services/products you sell and possibly child tables with potential customization options. If you go with Options, even the items without options need at least one option record since that is where the default price and description would come from.

If your stuff is totally custom, you might not be able to make a "price list" type of table. In that case, you should still add the new items to the original estimate before copying them to the order. That just keeps the process clean. Otherwise, you have to code and manage at least two different ways to get items on a quote. Go with KISS.

I have actually come into another issue I need to fix first:
I have a form based on a table, it has various combo boxes for related tables, i used the Dlookup to copy certain fields to a new record in the same form so i wouldn't have to fill in "all of the data when creating a new record that shares some related table data.

Now I have the same situation for my estimates form except the estimates form has two subforms on it, one for labor qty and price for various work types, and one for all of the materials from my parts table that go on that estimate record.
I need to copy an Estimate record and it's related child table data from both labor and materials to a new record with a new PK because a lot of my products are very similar I can just modify a few items on the child tables to get the "new" record adjusted correctly.
Dlookup isn't working so well for that (or I just don't know how to reference the object's methods and properties correctly.

 

[Pat Hartman]

I would NOT be using parallel structures. Use a single set of tables so you don't have to keep copying stuff. Make an estimate and have a status field so you can convert it to a Quote and then a Sale.

Look at the example I posted. DLookup() are the worst way to populate look up fields on a form.

 

[slharman1]

I would NOT be using parallel structures. Use a single set of tables so you don't have to keep copying stuff. Make an estimate and have a status field so you can convert it to a Quote and then a Sale.

Look at the example I posted. DLookup() are the worst way to populate look up fields on a form.

The reason I am copying is because the same materials and labor types are used on both estimates for a particular type of product. Then I don’t have to lookup each field in the related table to get the list of item for the 5 foot cabinet as I used in the 4 foot cabinet. Only the quantities change.

 

[Pat Hartman]

You are using parallel data structures for estimates and quotes. My suggestion was to use a single structure and change the status from estimate to quote and finally to sale.

 

[slharman1]

My estimates are for coming up with material and labor for a product to get a price for the product. Then the product, model #, desc, and price go in the quote table for my customer. We don’t tell them all of the labor and material involved in getting to the price. Only the model number and price. The kicker is the quote has to stand on its own. No tie to the estimates and the price field in the quote table has to be editable without referring to the original price in the custom fabricated product estimate. I hope this all makes sense. Thanks in advance for any ideas on getting this done.

 

[Pat Hartman]

I posted an example earlier of how to get data onto a form. It shows three methods. did you try any of them?

 

[slharman1]

I posted an example earlier of how to get data onto a form. It shows three methods. did you try any of them?

Yes I got that form populated. Thank you.

You are using parallel data structures for estimates and quotes. My suggestion was to use a single structure and change the status from estimate to quote and finally to sale.

An estimate does not have the same data. An estimate is for figuring the labor and material with various other calculations to arrive at a product price. Quote quote can dot be related to the estimate table.