If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Poor table design correction request
Hi,
I've gotten user and management requests to accommodate new data in a quote system I've developed. I imagine that I need to take historical data from the history table and populate a quote table and a quote detail table. Should the quote number be in both tables? How can I make sure the quote details are related to the appropriate quote? I'm afraid to break what is already working, but I need to deal with these new requests. I would appreciate someone's feedback or suggestions. Thanks, Dan Dungan Background: Per the owner's instructions, I developed a quoting application in Excel 2000. I use Access 2000 to output the quote and other reports. From Excel I create pipe delimited text file and import it to Access. All the reports and forms are derived from one table--tblHistory. The users want new functionality to add two different kind of comments and another data field, customer quote reference number. 1. A comment that will apply over the whole quote 2. A comment that will apply to a specific part number. I want to change the design to have a two tables, a quote table and a quote detail table. I want to append the data to the appropriate table. At this time, all the data resides in one table with the following fields: id PartNum Quantity UnitPrice Delivery RefNum Date Time Customer EAIRep QuoteNum QuotePrepared QuotePreparedDate Comments ImportDate ImportTime I think I need to split the data into two tables: tblQuote QuoteId QuoteNumber Customer QuoteComment EAIRep tblQuoteDetail PartNum Quantity UnitPrice Delivery RefNum DetailComment ImportDate ImportTime CreateDate CreateTime I have forms for correcting a quote, adding a part number to a quote, and deleting part numbers from quotes. I'll need to change these forms to reflect the parent/child table i'll implement. I confused about that, as well. |
#2
|
|||
|
|||
Poor table design correction request
I think that you have a good plan. Andit's relation nature will give you a
reason to ditch the bad idea of using Excel to store your data, I'd only use quote # as your linking field if you can control it. (such as making it verboten to reuse a number etc.). You're probalby better off using an autonumber field to link. "dan dungan" wrote: Hi, I've gotten user and management requests to accommodate new data in a quote system I've developed. I imagine that I need to take historical data from the history table and populate a quote table and a quote detail table. Should the quote number be in both tables? How can I make sure the quote details are related to the appropriate quote? I'm afraid to break what is already working, but I need to deal with these new requests. I would appreciate someone's feedback or suggestions. Thanks, Dan Dungan Background: Per the owner's instructions, I developed a quoting application in Excel 2000. I use Access 2000 to output the quote and other reports. From Excel I create pipe delimited text file and import it to Access. All the reports and forms are derived from one table--tblHistory. The users want new functionality to add two different kind of comments and another data field, customer quote reference number. 1. A comment that will apply over the whole quote 2. A comment that will apply to a specific part number. I want to change the design to have a two tables, a quote table and a quote detail table. I want to append the data to the appropriate table. At this time, all the data resides in one table with the following fields: id PartNum Quantity UnitPrice Delivery RefNum Date Time Customer EAIRep QuoteNum QuotePrepared QuotePreparedDate Comments ImportDate ImportTime I think I need to split the data into two tables: tblQuote QuoteId QuoteNumber Customer QuoteComment EAIRep tblQuoteDetail PartNum Quantity UnitPrice Delivery RefNum DetailComment ImportDate ImportTime CreateDate CreateTime I have forms for correcting a quote, adding a part number to a quote, and deleting part numbers from quotes. I'll need to change these forms to reflect the parent/child table i'll implement. I confused about that, as well. |
#3
|
|||
|
|||
Poor table design correction request
Thanks for your response, Fred.
I'm not storing data in Excel, I'm generating the data in Excel and storing it in Access. I would like to migrate the whole thing to access, but the management has an excel mind set. I'm storing pricing and price breaks in Excel. Well, I'm going to try to use the quote number as the pk. Dan |
#4
|
|||
|
|||
Poor table design correction request
I forgot to mention a few more thoughts.
Lots of ways to make your form changes, although the relational structure may make it new ground for you. The gist of most of them is to put your items list in as a subform. "dan dungan" wrote: Thanks for your response, Fred. I'm not storing data in Excel, I'm generating the data in Excel and storing it in Access. I would like to migrate the whole thing to access, but the management has an excel mind set. I'm storing pricing and price breaks in Excel. Well, I'm going to try to use the quote number as the pk. Dan |
#5
|
|||
|
|||
Poor table design correction request
Thanks, Fred.
I'm working on the tables now. My next challenge will be how to get the data split into the two tables. Each row in tblHistory contains a quote number. I'll need to copy that quote number once in tblQuote and then copy it to tblQuoteDetail for each record. I'm not sure what kind of query to use to do that. Dan |
#6
|
|||
|
|||
Poor table design correction request
My low tech easy way to split a table like yours would be to (after backup
up) make a copy of it. Name one copy as "quotes" and the other "items" and then wipe out the unwanted fields in each one. (Of course your quote number will remain in both of them.) "dan dungan" wrote: Thanks, Fred. I'm working on the tables now. My next challenge will be how to get the data split into the two tables. Each row in tblHistory contains a quote number. I'll need to copy that quote number once in tblQuote and then copy it to tblQuoteDetail for each record. I'm not sure what kind of query to use to do that. Dan |
#7
|
|||
|
|||
Poor table design correction request
In the copy named "quotes" I only want one instance of each quote
number. So need to somehow remove all but one of the quote number records. |
#8
|
|||
|
|||
Poor table design correction request
Sorry I did not realize that you are already dealing with multiple items per
quote. So I guess that you have been entering a record for each item, and repeating the quote information on each line. Then, if you still want to use my low tech method and continue along my low tech path, (and if you don't have too many quotes for this) you could run a "find duplicates" query in the new quotes table, and manually delete the extras I think that there's also "split table" wizard in Access for doing this, although I have never used it. If you go that way take care to pick the right one. There are two kinds of splits, the other being back end from front end which is not what you want. "dan dungan" wrote: In the copy named "quotes" I only want one instance of each quote number. So need to somehow remove all but one of the quote number records. |
#9
|
|||
|
|||
Poor table design correction request
Always back up your db before doing these procedures. If it all goes wrong
you must be able to ditch and try again! You can use a Unique query, an Append Query and an update query to do this. Create your Quotetable and do feel free to use an Autonumber field. This is also your chance to drop the # from the name (it's the symbol used for Date data and it will come back to haunt you some time). You can use it in labels to your hearts content but stick to short, sensible table names. Put QuoteNumber from TblHistory into a query. In Query Design View, use the query's (not the Field's) Properties to set Unique Values to yes. So now you will have only one of each Quote Number Use an Append query to add this to your new Quotetable. To make sure that TblHistory now contains your new AutoNumber field instead of the actual QuoteNumber, create a new field in TblHistory called eg QuoID or whatever you called your Primary Key field in your Quote table Add the TblQuote and TblHistory to the query's top area, joining by the QuoteNumber field that they do have in common. Change the Query into an Update query Add QuoID from TblHistory to the grid and then, in the Update To line put eg [TblQuote]![QuoID] Ensure that it doesn't have quote marks around it or it will copy that text into your field. When you have triple checked everything (sort the QuoID field in TblHistory to ensure there are no nulls) then you can delete it's QuoteNumber field. This will take you through the whole process with relative painlessness but only do it when you feel alert. It's easy to get in a real muddle . I usually make several backups as I go through, giving each one a version number so that I can always go back a step or 2 if I need to. Evi "dan dungan" wrote in message ... Thanks, Fred. I'm working on the tables now. My next challenge will be how to get the data split into the two tables. Each row in tblHistory contains a quote number. I'll need to copy that quote number once in tblQuote and then copy it to tblQuoteDetail for each record. I'm not sure what kind of query to use to do that. Dan |
#10
|
|||
|
|||
Poor table design correction request
Thanks from me too for pointing out the "unique values" property / capability
in queries. "Evi" wrote: Always back up your db before doing these procedures. If it all goes wrong you must be able to ditch and try again! You can use a Unique query, an Append Query and an update query to do this. Create your Quotetable and do feel free to use an Autonumber field. This is also your chance to drop the # from the name (it's the symbol used for Date data and it will come back to haunt you some time). You can use it in labels to your hearts content but stick to short, sensible table names. Put QuoteNumber from TblHistory into a query. In Query Design View, use the query's (not the Field's) Properties to set Unique Values to yes. So now you will have only one of each Quote Number Use an Append query to add this to your new Quotetable. To make sure that TblHistory now contains your new AutoNumber field instead of the actual QuoteNumber, create a new field in TblHistory called eg QuoID or whatever you called your Primary Key field in your Quote table Add the TblQuote and TblHistory to the query's top area, joining by the QuoteNumber field that they do have in common. Change the Query into an Update query Add QuoID from TblHistory to the grid and then, in the Update To line put eg [TblQuote]![QuoID] Ensure that it doesn't have quote marks around it or it will copy that text into your field. When you have triple checked everything (sort the QuoID field in TblHistory to ensure there are no nulls) then you can delete it's QuoteNumber field. This will take you through the whole process with relative painlessness but only do it when you feel alert. It's easy to get in a real muddle . I usually make several backups as I go through, giving each one a version number so that I can always go back a step or 2 if I need to. Evi "dan dungan" wrote in message ... Thanks, Fred. I'm working on the tables now. My next challenge will be how to get the data split into the two tables. Each row in tblHistory contains a quote number. I'll need to copy that quote number once in tblQuote and then copy it to tblQuoteDetail for each record. I'm not sure what kind of query to use to do that. Dan |
Thread Tools | |
Display Modes | |
|
|