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
|
|||
|
|||
A Temporary Table or One Long Array
Hi -
I am doing a database that keeps track of insurance policies for an insurance agent. The Invoice table (parent) contains the total premium (for a policy), the Invoice Company table (child) contains premiums which add up to the total premium. For example, in the Invoice table, Car Premium = $500. In the Invoice Company table 1st record - company is State Farm, premium is $200 2nd record - company is ING, premium is $300 The operator is entering this information onto a form and subform. We do not want to save the records in the subform if the premium amounts do not add up to the total premium, because we don't want the database to be out of balance, ever. One way we thought of doing this was to extend the Invoice record with say 10 multiples of the 5 fields in the Invoice Company table, in the unlikely event that the total premium was spread over 10 companies. Most of the time it is one company, some times it is 2 or 3 companies. This way when the Invoice record is written, we know it is in balance, but this method wastes space. Another choice would be to store the information on the form into a temporary table and when everything added up properly, then write the records to the Invoice Company table. In both of these ways, we do not save information that is not in balance. So can you give me some advice on how to go? And if temporary tables are to be used, then - briefly - how do I do it? Do you know of any examples I can look at? Thanks so much! Joy |
#2
|
|||
|
|||
comments inline.
"Joy M" wrote in message ... Hi - I am doing a database that keeps track of insurance policies for an insurance agent. The Invoice table (parent) contains the total premium (for a policy), the Invoice Company table (child) contains premiums which add up to the total premium. For example, in the Invoice table, Car Premium = $500. In the Invoice Company table 1st record - company is State Farm, premium is $200 2nd record - company is ING, premium is $300 The operator is entering this information onto a form and subform. where is the operator getting the total premium amount? from paper media? and likewise the company premiums? in what circumstances would the company premiums not add up to the total premium? We do not want to save the records in the subform if the premium amounts do not add up to the total premium, because we don't want the database to be out of balance, ever. so if the company premiums DON'T add up to the total premium, you don't want to save the company premium records? how will that keep your database "in balance"? if you write a query to compare the total dollar amount in the Invoices table with the total dollar amount in the InvoiceCompany table, the two tables will be MORE out of balance, not less. the solution is to either not save *any* invoice company records - or, save them all and resolve inconsistencies in the total-company-premiums / invoice-premiums at the point of data entry. One way we thought of doing this was to extend the Invoice record with say 10 multiples of the 5 fields in the Invoice Company table, in the unlikely event that the total premium was spread over 10 companies. Most of the time it is one company, some times it is 2 or 3 companies. This way when the Invoice record is written, we know it is in balance, but this method wastes space. the parent/child table setup is correct for your situation. you absolutely should *not* de-normalize the Invoice table in the way you describe above. to do so would take the dangerous step of limiting your business process based on arbitrary physical limitations imposed by the database design. what happens if/when your business grows/expands/changes, and you have even one invoice record that needs to reflect eleven companies? your database cannot support the business process at that point, and you're forced to rely on a paper tracking system to supplement the database in the short-term, and rewriting the database for the long term. Another choice would be to store the information on the form into a temporary table and when everything added up properly, then write the records to the Invoice Company table. In both of these ways, we do not save information that is not in balance. again, whether you write the Invoice Company data directly to a child table, or to a temporary table and then to a child table, not saving records that are "out of balance" will not bring the data into balance - unless you *also* don't save the total premium record in the parent Invoice table. So can you give me some advice on how to go? since you considered moving the child table's fields into the main table, i'll assume that you need to store the child data as part of the permanent records. and you imply that putting the data in the parent table would allow you to balance the total premium with the sum of the company premiums for a specific record. so i must also assume that you're able to adjust the dollar amounts in some appropriate manner to make the two totals balance. if you can do that in a single record, you can do it between parent/child records. the issue is not your table design, it's the design of the user interface. you need to design a parent/child form that supports a check of the parent record's premium against the total of the child records' premiums. or is your operator simply manually adding the premiums of the child records, and entering that total manually into the parent record? if so, you might write code in the form to automatically add all the premiums entered in the subform and write that total into the main form's "money" field - then there's no issue of input error, and no "balance" errors. hth And if temporary tables are to be used, then - briefly - how do I do it? Do you know of any examples I can look at? Thanks so much! Joy |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help again from Ken Snell (Query) | Randy | Running & Setting Up Queries | 22 | August 29th, 2005 08:15 PM |
Is Powerpoint still a single instance app? | Howard Kaikow | Powerpoint | 99 | June 16th, 2005 04:09 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |
unable to repair inobox | Sudheer Mumbai | General Discussion | 1 | February 20th, 2005 11:55 AM |
Update - If statement | Dan @BCBS | Running & Setting Up Queries | 13 | December 14th, 2004 06:02 PM |