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
|
|||
|
|||
table redesign
I have a table that consists of the following fields
name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. |
#2
|
|||
|
|||
table redesign
Don't know if I saw your original post but you have a big problem, namely
repeating columns (posted13, item13,etc.). We can't help until we know the "facts" your database is attempting to store. What is your table about? Evan "Mykas_Robi" wrote in message ... I have a table that consists of the following fields name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. |
#3
|
|||
|
|||
table redesign
From your description, you have ... a spreadsheet!
While you are pretty much limited to "adding columns" to add fields when you work with a spreadsheet, Access is a relational database. You won't get easy use of Access' relationally-oriented features/functions if you feed it 'sheet data. If the terms "normalization" and "relational database" don't make much sense, spend some time studying up on them before trying to design an Access database. Is there a reason you can't just use a spreadsheet? Regards Jeff Boyce Microsoft Office/Access MVP "Mykas_Robi" wrote in message ... I have a table that consists of the following fields name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. |
#4
|
|||
|
|||
table redesign
On Thu, 3 Apr 2008 11:17:00 -0700, Mykas_Robi
wrote: I have a table that consists of the following fields name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. You should certainly have at least two tables in a one to many relationship. Since you didn't indicate anything about the real-world situation or what information this database models (other that it evidently has to do with financial credits and debits) it's a bit hard to say specifically! What real-life Enitity (person, thing or event) does this table represent? What are debits and credits? Are debits and credits independent of one another, or does each debit have an associated credit? More info please! -- John W. Vinson [MVP] |
#5
|
|||
|
|||
table redesign
The repeating fields appear to be:
Date Posted Item Debit Credit Therefore you will need those fields in a related table, so that one of your orignal entries can have many rows in this related table. It's not clear where else the one-to-many relations may be needed. What's the "name" and "number"? Is this like a client and an account number? If so, could there be cases where one client could have multiple accounts? If so, you need to start with a tblClient (client information, with a ClientID primary key), and a tblAccount (with an AccountID primary key, and a ClientID field to tell which client this account belongs to.) Now your main table seems to be for transactions on these accounts. One account has many transactions over time. I'm not sure if there is any meaning to the different Item columns, or if you just use the next available one. If they are for recording different kinds of items, you would need a table of items with an ItemID primary key. Now the transaction table would have fields like this: - AccountID relates to tblAccount.AccountID - TransDate Date/Time when this transaction occurred. - Posted whatever this is (a date?) - TransDirection Number 1 or -1 (for credit or debit.) Required. - Amount Currency how much - ItemID relates to the Item table if you need to record this. You have now set up relationships so that: - one client can have many accounts; - one account can have many transactions; and possibly: - one type of item can appear in many transactions. You will interface this with a main form bound to the account table, and a subform bound to the transaction table. For each transaction, you enter another row in the continuous view subform. So you have solved the problem with many rows in the transaction table rather than many columns as in your spreadsheet-like table. The benefit of this relational design is that it's dead easy to query stuff. For example, you have just one column to search to find all transactions for a date. In query design, type an expression like this into the Field row: [TransDirection] * [Amount] Since TransDirection is either 1 or -1 (for credit or debit), this gives you a column you can sum to get the net transactions. Well, I've made lots of assumptions about your data, but hopefully that's enough of an example to set you thinking in a useful direction to redesign your table. BTW, when you redesign your tables, you want to avoid some of those field names, such as Name, Number, and Date: http://allenbrowne.com/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Mykas_Robi" wrote in message ... I have a table that consists of the following fields name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. |
#6
|
|||
|
|||
table redesign
this is actually an insurance policy administration database application that
i maintain and am running into problems adding additional features. the table i illustrated is called group accounts receivable another table is used to house tax information (group tax info), and the main table(physician rate information) houses policy information. all three tables are keyed off an "advice number" which is unique. I think I do have a big problem because I have to modify this thing to answer questions that would be easy to answer if the application was normalized or relational. You point me in the right direction because I think I should have the illustrated table have as fields the advice#, pmt amt (amount insured should pay), pmt due date, date posted, item (check #), debit(amount actualy paid), credit (returned check, etc) and then I would have a 1 to many relationship. The table I illustratied which is called the group accounts receivable table is very similar in design to the group tax info table. for instance in the group tax info table i house taxes for each debit. I will check back later on and submit my new table design. Based on the information you proved I can probably have the accounts receivable and tax info tables in one table. Thanks again all of you imparted extremly helpful information. I know I will need additional assistance. "Allen Browne" wrote: The repeating fields appear to be: Date Posted Item Debit Credit Therefore you will need those fields in a related table, so that one of your orignal entries can have many rows in this related table. It's not clear where else the one-to-many relations may be needed. What's the "name" and "number"? Is this like a client and an account number? If so, could there be cases where one client could have multiple accounts? If so, you need to start with a tblClient (client information, with a ClientID primary key), and a tblAccount (with an AccountID primary key, and a ClientID field to tell which client this account belongs to.) Now your main table seems to be for transactions on these accounts. One account has many transactions over time. I'm not sure if there is any meaning to the different Item columns, or if you just use the next available one. If they are for recording different kinds of items, you would need a table of items with an ItemID primary key. Now the transaction table would have fields like this: - AccountID relates to tblAccount.AccountID - TransDate Date/Time when this transaction occurred. - Posted whatever this is (a date?) - TransDirection Number 1 or -1 (for credit or debit.) Required. - Amount Currency how much - ItemID relates to the Item table if you need to record this. You have now set up relationships so that: - one client can have many accounts; - one account can have many transactions; and possibly: - one type of item can appear in many transactions. You will interface this with a main form bound to the account table, and a subform bound to the transaction table. For each transaction, you enter another row in the continuous view subform. So you have solved the problem with many rows in the transaction table rather than many columns as in your spreadsheet-like table. The benefit of this relational design is that it's dead easy to query stuff. For example, you have just one column to search to find all transactions for a date. In query design, type an expression like this into the Field row: [TransDirection] * [Amount] Since TransDirection is either 1 or -1 (for credit or debit), this gives you a column you can sum to get the net transactions. Well, I've made lots of assumptions about your data, but hopefully that's enough of an example to set you thinking in a useful direction to redesign your table. BTW, when you redesign your tables, you want to avoid some of those field names, such as Name, Number, and Date: http://allenbrowne.com/Ap****ueBadWord.html -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Mykas_Robi" wrote in message ... I have a table that consists of the following fields name, number, effect date, term date, date posted1, item1, debit1, credit1...date posted13, item13, debit13, credit13. I submitted a question on query design and was informed that my table is poorly designed. I need to know what to break this table up that so that it is designed properly. Please any assistance would be greatly appreciated. Thanks and have a nice day. |
#7
|
|||
|
|||
table redesign
On Fri, 4 Apr 2008 12:16:18 -0700, Mykas_Robi
wrote: I will check back later on and submit my new table design. Based on the information you proved I can probably have the accounts receivable and tax info tables in one table. You should consider this carefully. Those sound like two different kinds of data, which should be in two many-side tables related to the accounts table. -- John W. Vinson [MVP] |
#8
|
|||
|
|||
table redesign
I think John's right: it would be better as 2 tables. What the person should
pay, and what they actually pay don't always match. You will need to consider what possibilities you need to design for, e.g.: - a person makes multiple payments on one policy (e.g. when struggling financially) - a person makes a single payment that covers multiple policies (e.g. one check covering a guy and his wife.) - a person prepays his policy (e.g. one check covers the next 3 payments due, because he will be away.) - a payment is partly check and partly cash (or card.) and the really curly one: - you need to receipt a payment for a policy that has not been created yet. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "John W. Vinson" wrote in message ... On Fri, 4 Apr 2008 12:16:18 -0700, Mykas_Robi wrote: I will check back later on and submit my new table design. Based on the information you proved I can probably have the accounts receivable and tax info tables in one table. You should consider this carefully. Those sound like two different kinds of data, which should be in two many-side tables related to the accounts table. -- John W. Vinson [MVP] |
#9
|
|||
|
|||
table redesign
On Sat, 5 Apr 2008 10:38:18 +0800, "Allen Browne"
wrote: I think John's right: it would be better as 2 tables. What the person should pay, and what they actually pay don't always match. Oh... you've had those clients too, Allen? g -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|