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 Design with Many Fields
I am using Access 2007. I have a table ACCOUNTS, with fields for account
number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don’t think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. |
#2
|
|||
|
|||
Table Design with Many Fields
Perhaps these 5 tables would do what you need:
a) AccountType: one record for each type of account, with AccountTypeID primary key. b) Process: one record for each type of process you may need to handle, with ProcessID primary key. c) AccountTypeProcess: One record for each combination of account type and process. (For example, if the "Accounts Payable" has 5 processes associated with it, there will be 5 *records* in this table for that account type.) d) Account: one record for each account, with an AccountID primary key, and AccountTypeID foreign key. e) AccountProcess: one record for each process associated with this account. So, if it's an "Accounts Payable" type, it will have 5 records to cover each of the processes. With table (c) above, you have taught your database what processes are associated with each type of account. So, when you enter a new account into (d), you can use the AfterInsert event of the form to Execute an Append query statement to append the appropriate records automatically into table (e.) -- 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. "JD McLeod" wrote in message ... I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don’t think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. |
#3
|
|||
|
|||
Table Design with Many Fields
Thanks, Allen, I will give this a try. Can you explain what is meant by
"foreign key" in item d. of your post. I understand primary key, but i am not sure what is the foreign key. thanks again. "Allen Browne" wrote: Perhaps these 5 tables would do what you need: a) AccountType: one record for each type of account, with AccountTypeID primary key. b) Process: one record for each type of process you may need to handle, with ProcessID primary key. c) AccountTypeProcess: One record for each combination of account type and process. (For example, if the "Accounts Payable" has 5 processes associated with it, there will be 5 *records* in this table for that account type.) d) Account: one record for each account, with an AccountID primary key, and AccountTypeID foreign key. e) AccountProcess: one record for each process associated with this account. So, if it's an "Accounts Payable" type, it will have 5 records to cover each of the processes. With table (c) above, you have taught your database what processes are associated with each type of account. So, when you enter a new account into (d), you can use the AfterInsert event of the form to Execute an Append query statement to append the appropriate records automatically into table (e.) -- 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. "JD McLeod" wrote in message ... I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don’t think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. . |
#5
|
|||
|
|||
Table Design with Many Fields
Primary key is a unique identifier in one table.
Foreign key is the matching field in a related table. It's not unique (typically it's the many side of a one-to-many relation), but it relates to the primary key of another table. -- 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. "JD McLeod" wrote in message news Thanks, Allen, I will give this a try. Can you explain what is meant by "foreign key" in item d. of your post. I understand primary key, but i am not sure what is the foreign key. thanks again. "Allen Browne" wrote: Perhaps these 5 tables would do what you need: a) AccountType: one record for each type of account, with AccountTypeID primary key. b) Process: one record for each type of process you may need to handle, with ProcessID primary key. c) AccountTypeProcess: One record for each combination of account type and process. (For example, if the "Accounts Payable" has 5 processes associated with it, there will be 5 *records* in this table for that account type.) d) Account: one record for each account, with an AccountID primary key, and AccountTypeID foreign key. e) AccountProcess: one record for each process associated with this account. So, if it's an "Accounts Payable" type, it will have 5 records to cover each of the processes. With table (c) above, you have taught your database what processes are associated with each type of account. So, when you enter a new account into (d), you can use the AfterInsert event of the form to Execute an Append query statement to append the appropriate records automatically into table (e.) -- 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. "JD McLeod" wrote in message ... I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don’t think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. . |
#6
|
|||
|
|||
Table Design with Many Fields
Thanks Allen & Steve
Since I am still new to Access, I decided to start small and see if i could understand how to do this or something like it. I created two tables, one for Acct and one for Process. Then i created a third table,AcctProcess, which has a field for the account and the process. I used a form with combo boxes to lookup the values in the Acct and Process tables and match them and then fill in the values in table three. In table three, it stores the primary ID for each the account and processs. If i have 4 processes for AcctsPayable, then i have 4 separate records in the AcctProcess table. Is this a way to do it and still have a "normal" data structure to the table. I got to thinking, that there are only 4 types of accounts and an account can only have one type assigned to it, so i just made that a field in the Acct table and used the data entry form to restrict what the users could enter. I figured that would eliminate the need for a separate table. I look forward to your thoughts. "Steve" wrote: You would have: TblAccountType AccountTypeID etc TblAccount AccountID Account AccountTypeID etc AccountTypeID is the primary key in TblAccountType and AccountTypeID is the foreign key in TblAccount. A foreign key relates a record in some table to a record in another table. In your case, AccountTypeID identifies what AccountType each Account in TblAccount is. Steve "JD McLeod" wrote in message news Thanks, Allen, I will give this a try. Can you explain what is meant by "foreign key" in item d. of your post. I understand primary key, but i am not sure what is the foreign key. thanks again. "Allen Browne" wrote: Perhaps these 5 tables would do what you need: a) AccountType: one record for each type of account, with AccountTypeID primary key. b) Process: one record for each type of process you may need to handle, with ProcessID primary key. c) AccountTypeProcess: One record for each combination of account type and process. (For example, if the "Accounts Payable" has 5 processes associated with it, there will be 5 *records* in this table for that account type.) d) Account: one record for each account, with an AccountID primary key, and AccountTypeID foreign key. e) AccountProcess: one record for each process associated with this account. So, if it's an "Accounts Payable" type, it will have 5 records to cover each of the processes. With table (c) above, you have taught your database what processes are associated with each type of account. So, when you enter a new account into (d), you can use the AfterInsert event of the form to Execute an Append query statement to append the appropriate records automatically into table (e.) -- 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. "JD McLeod" wrote in message ... I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don't think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. . . |
#7
|
|||
|
|||
Table Design with Many Fields
Beautiful: what you've built may be all you need.
You can now create a query combining the 3 tables. It will give you each process for each account. -- 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. "JD McLeod" wrote in message ... Thanks Allen & Steve Since I am still new to Access, I decided to start small and see if i could understand how to do this or something like it. I created two tables, one for Acct and one for Process. Then i created a third table,AcctProcess, which has a field for the account and the process. I used a form with combo boxes to lookup the values in the Acct and Process tables and match them and then fill in the values in table three. In table three, it stores the primary ID for each the account and processs. If i have 4 processes for AcctsPayable, then i have 4 separate records in the AcctProcess table. Is this a way to do it and still have a "normal" data structure to the table. I got to thinking, that there are only 4 types of accounts and an account can only have one type assigned to it, so i just made that a field in the Acct table and used the data entry form to restrict what the users could enter. I figured that would eliminate the need for a separate table. I look forward to your thoughts. "Steve" wrote: You would have: TblAccountType AccountTypeID etc TblAccount AccountID Account AccountTypeID etc AccountTypeID is the primary key in TblAccountType and AccountTypeID is the foreign key in TblAccount. A foreign key relates a record in some table to a record in another table. In your case, AccountTypeID identifies what AccountType each Account in TblAccount is. Steve "JD McLeod" wrote in message news Thanks, Allen, I will give this a try. Can you explain what is meant by "foreign key" in item d. of your post. I understand primary key, but i am not sure what is the foreign key. thanks again. "Allen Browne" wrote: Perhaps these 5 tables would do what you need: a) AccountType: one record for each type of account, with AccountTypeID primary key. b) Process: one record for each type of process you may need to handle, with ProcessID primary key. c) AccountTypeProcess: One record for each combination of account type and process. (For example, if the "Accounts Payable" has 5 processes associated with it, there will be 5 *records* in this table for that account type.) d) Account: one record for each account, with an AccountID primary key, and AccountTypeID foreign key. e) AccountProcess: one record for each process associated with this account. So, if it's an "Accounts Payable" type, it will have 5 records to cover each of the processes. With table (c) above, you have taught your database what processes are associated with each type of account. So, when you enter a new account into (d), you can use the AfterInsert event of the form to Execute an Append query statement to append the appropriate records automatically into table (e.) -- 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. "JD McLeod" wrote in message ... I am using Access 2007. I have a table ACCOUNTS, with fields for account number, account description, and account balance. There are 12 other fields for various assertions/characteristics related to the accounts. Each account will have at least one of the 12 assertions apply, and many will have more than one. Each characteristic has its own field. I have a form that is used to enter all of the account information including combo boxes to select either high, mod, or low as the value for each of the other 12 fields. Next, I have to determine whether or not each account is significant and what business processes are related to it. I thought of using another table for this. For example, the table ACCOUNTS2 would pull in the account information from the first table and then have a field for 5 questions that must be answered in determining whether or not the account is significant. In addition, the user needs to identify all business processes related to this account. For example, if the account were Accounts Payable, the processes might include Vendor Setup, Invoice Approval and Check Signing. All accounts will have at least one process, but many will have more than one. I thought about doing it all in one table, but was concerned about having too many fields. I need help designing this table. I don't think the way I am doing it right now is correct. Can any of this be done in a form and not saved in a table? Can you create reports and queries off of data in forms? Any advice would be helpful. Thanks. . . |
Thread Tools | |
Display Modes | |
|
|