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
|
|||
|
|||
Suggestion for database design
I have a customers table. I will be receiving an account balance each month
for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#2
|
|||
|
|||
Suggestion for database design
This is the table I would use:
tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#3
|
|||
|
|||
Suggestion for database design
If I am required to store a quarterly balance, would you recommend that I
store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#4
|
|||
|
|||
Suggestion for database design
If you need to store both quarterly and monthly balances, isn't one of your
monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#5
|
|||
|
|||
Suggestion for database design
The quarterly balance is not the same as one of my monthly balances. The
quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#6
|
|||
|
|||
Suggestion for database design
I would not store a quarterly balance if it can easily be queried from your
monthly balances. -- Duane Hookom MS Access MVP "Matt" wrote in message ... The quarterly balance is not the same as one of my monthly balances. The quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#7
|
|||
|
|||
Suggestion for database design
That makes sense. However, let's assume that this data will be exported and
imported into another application. If I were to store the quarterly balance, would you recommend it stored in account balances table or a separate table? Thanks for your input. I hope I'm not wasting your time with hypotheticals. I'm trying to consider all situations that may arise. Thanks again. "Duane Hookom" wrote: I would not store a quarterly balance if it can easily be queried from your monthly balances. -- Duane Hookom MS Access MVP "Matt" wrote in message ... The quarterly balance is not the same as one of my monthly balances. The quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#8
|
|||
|
|||
Suggestion for database design
You can always create a query that calculates the quarterly balances. You
can export the records from the query. Why would you import the quarterly balances if you can calculate them based on the monthly balances? -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... That makes sense. However, let's assume that this data will be exported and imported into another application. If I were to store the quarterly balance, would you recommend it stored in account balances table or a separate table? Thanks for your input. I hope I'm not wasting your time with hypotheticals. I'm trying to consider all situations that may arise. Thanks again. "Duane Hookom" wrote: I would not store a quarterly balance if it can easily be queried from your monthly balances. -- Duane Hookom MS Access MVP "Matt" wrote in message ... The quarterly balance is not the same as one of my monthly balances. The quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#9
|
|||
|
|||
Suggestion for database design
Here is a brief overview of my situation. I have a company who sends me a
text file of data that they have based on data entry in an in-house application. I need to update my database with this data, then calculate the quarterly balances and export the quarterly balances to update their system. Basically all of the manipulation of the data needs to be done on my side. Also, I need to be able to do a mail merge from my data for letters that we send to each customer with their monthly balance and then every quarter a letter with their quarterly balance. That is why I needed to keep the quarterly balance. However, if I can export from a query then that answers my question about keeping a quarterly balance for the export. Can I calculate the quarterly balance in a mail merge or do I need to have an actual field for that? Thanks again for your help. I know that it is easier to understand knowing the whole situation. You can always create a query that calculates the quarterly balances. You can export the records from the query. Why would you import the quarterly balances if you can calculate them based on the monthly balances? -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... That makes sense. However, let's assume that this data will be exported and imported into another application. If I were to store the quarterly balance, would you recommend it stored in account balances table or a separate table? Thanks for your input. I hope I'm not wasting your time with hypotheticals. I'm trying to consider all situations that may arise. Thanks again. "Duane Hookom" wrote: I would not store a quarterly balance if it can easily be queried from your monthly balances. -- Duane Hookom MS Access MVP "Matt" wrote in message ... The quarterly balance is not the same as one of my monthly balances. The quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
#10
|
|||
|
|||
Suggestion for database design
You can merge, export, report, print, ... from a query as well as a table. I
still see no reason to store quarterly totals. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... Here is a brief overview of my situation. I have a company who sends me a text file of data that they have based on data entry in an in-house application. I need to update my database with this data, then calculate the quarterly balances and export the quarterly balances to update their system. Basically all of the manipulation of the data needs to be done on my side. Also, I need to be able to do a mail merge from my data for letters that we send to each customer with their monthly balance and then every quarter a letter with their quarterly balance. That is why I needed to keep the quarterly balance. However, if I can export from a query then that answers my question about keeping a quarterly balance for the export. Can I calculate the quarterly balance in a mail merge or do I need to have an actual field for that? Thanks again for your help. I know that it is easier to understand knowing the whole situation. You can always create a query that calculates the quarterly balances. You can export the records from the query. Why would you import the quarterly balances if you can calculate them based on the monthly balances? -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... That makes sense. However, let's assume that this data will be exported and imported into another application. If I were to store the quarterly balance, would you recommend it stored in account balances table or a separate table? Thanks for your input. I hope I'm not wasting your time with hypotheticals. I'm trying to consider all situations that may arise. Thanks again. "Duane Hookom" wrote: I would not store a quarterly balance if it can easily be queried from your monthly balances. -- Duane Hookom MS Access MVP "Matt" wrote in message ... The quarterly balance is not the same as one of my monthly balances. The quarterly balance needs to be the sum of the previous 3 monthly balances from when the query is run to update the quarterly balance value. This is why I'm asking if I should have a separate table for the multiple quarterly balances. There will only be four quarterly balances for each customer per year while there will be 12 monthly balances per customer per year. Thanks for your help. Matt "Duane Hookom" wrote: If you need to store both quarterly and monthly balances, isn't one of your monthly balances, the same as your quarterly balance? If not, I would use the suggested table with a new text field [QuarterMonth] to store either Q or M. -- Duane Hookom MS Access MVP -- "Matt" wrote in message news If I am required to store a quarterly balance, would you recommend that I store that in the tblCustAcctBalances table or in a seperate table by itself. I imagine I will need to keep these quarterly balances for a significant amount of time also. Thanks for your suggestions. Matt "Duane Hookom" wrote: This is the table I would use: tblCustAcctBalances ================== CustAcctID autonumber primary key CustomerID link to the primary key of your customer table BalanceDate date/time BalanceAmount currency Create a unique index on CustomerID and BalanceDate. -- Duane Hookom MS Access MVP -- "Matt" wrote in message ... I have a customers table. I will be receiving an account balance each month for each customer. I need to be able to keep each monthly account balance stored for a considerable period of time, lets say 5 years. What would be the best way to store this data? It seems like keeping a column of each month/year would lead to a very large table. Is this the only/best way to do this? Suggestions would be very much appreciated. Thanks. Matt |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
One-to-Many relationship design suggestion | Shawn Fletcher SCC | Database Design | 3 | February 8th, 2005 05:09 AM |
design master problem | J. Vermeer | General Discussion | 0 | September 8th, 2004 03:23 PM |
Action queries changing when reopened in design view | Kendra | Running & Setting Up Queries | 2 | August 31st, 2004 12:34 AM |
Table Design Suggestion | JH | Database Design | 8 | June 23rd, 2004 01:36 AM |
Good Design Tutorials | DDM | Database Design | 0 | April 24th, 2004 05:19 PM |