Poor table design correction request

[dan dungan]

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.

 

[Fred]

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]

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

 

[Fred]

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]

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

 

[Fred]

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]

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.

 

[Fred]

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.

 

[Evi]

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

 

[Fred]

Thanks from me too for pointing out the "unique values" property / capability
in queries.

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

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