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
|
|||
|
|||
Setting up data to pull info from dates not stored in fields
This is probably a bit of a basic question but since I am new to both
database design and access it has been a challenge at times to get my head around thinking “normalize” versus the usual cram everything into a spreadsheet mindset. This a new database I am creating to consolidate information that either doesn’t exist yet or is inefficiently stored in far too many places. The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Each employee receives a monthly payment that can change once or twice a year. There is no set rule as to how many changes will occur or in which month they will happen. John Doe Monthly Payment: $275 Start Date: 8/1/06 End Date: 3/30/2007 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/07 End Date: 7/31/08 The issue is that I need to be able pull the total payments made in 2006, 2007 etc. Can I do this with just the dates above, or will I need to break this down further into calendar years like below: JohnDoe Monthly Payment: $100 Start Date: 7/1/2006 End Date: 12/31/06 Monthly Payment: $100 Start Date: 1/1/2007 End Date: 6/30/07 Monthly Payment: $150 Start Date: 7/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 6/30/2008 I know I can get what I need out of the 2nd option, but if I can get the same results using the first, it will make user input simpler. |
#2
|
|||
|
|||
Setting up data to pull info from dates not stored in fields
Oops, realized my examples did not contain consistant data (not helpful huh),
please refer to the scenario's here when answering question Scenario 1: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 3/31/2007 Monthly Payment: $125 Start Date: 4/1/2007 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 7/31/08 Scenario 2: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 12/31/06 Monthly Payment: $275 Start Date: 1/1/2007 End Date: 3/31/07 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 7/31/2008 Question is, can I create queries to pull monthly data just for 2006 or 2007 using dates set up in scenario 1 or do I need to set up data to look more like scenario 2? "Adrian" wrote: This is probably a bit of a basic question but since I am new to both database design and access it has been a challenge at times to get my head around thinking “normalize” versus the usual cram everything into a spreadsheet mindset. This a new database I am creating to consolidate information that either doesn’t exist yet or is inefficiently stored in far too many places. The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Each employee receives a monthly payment that can change once or twice a year. There is no set rule as to how many changes will occur or in which month they will happen. John Doe Monthly Payment: $275 Start Date: 8/1/06 End Date: 3/30/2007 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/07 End Date: 7/31/08 The issue is that I need to be able pull the total payments made in 2006, 2007 etc. Can I do this with just the dates above, or will I need to break this down further into calendar years like below: JohnDoe Monthly Payment: $100 Start Date: 7/1/2006 End Date: 12/31/06 Monthly Payment: $100 Start Date: 1/1/2007 End Date: 6/30/07 Monthly Payment: $150 Start Date: 7/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 6/30/2008 I know I can get what I need out of the 2nd option, but if I can get the same results using the first, it will make user input simpler. |
#3
|
|||
|
|||
Setting up data to pull info from dates not stored in fields
The database will include payment records that overlap calendar years but I
will need to run queries or reports for a specific year. Your examples are not 'payment records.' Payment records would be a separate record for each payment show the exact date received or posted. Then you could easily pull your data. -- KARL DEWEY Build a little - Test a little "Adrian" wrote: Oops, realized my examples did not contain consistant data (not helpful huh), please refer to the scenario's here when answering question Scenario 1: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 3/31/2007 Monthly Payment: $125 Start Date: 4/1/2007 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 7/31/08 Scenario 2: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 12/31/06 Monthly Payment: $275 Start Date: 1/1/2007 End Date: 3/31/07 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 7/31/2008 Question is, can I create queries to pull monthly data just for 2006 or 2007 using dates set up in scenario 1 or do I need to set up data to look more like scenario 2? "Adrian" wrote: This is probably a bit of a basic question but since I am new to both database design and access it has been a challenge at times to get my head around thinking “normalize” versus the usual cram everything into a spreadsheet mindset. This a new database I am creating to consolidate information that either doesn’t exist yet or is inefficiently stored in far too many places. The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Each employee receives a monthly payment that can change once or twice a year. There is no set rule as to how many changes will occur or in which month they will happen. John Doe Monthly Payment: $275 Start Date: 8/1/06 End Date: 3/30/2007 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/07 End Date: 7/31/08 The issue is that I need to be able pull the total payments made in 2006, 2007 etc. Can I do this with just the dates above, or will I need to break this down further into calendar years like below: JohnDoe Monthly Payment: $100 Start Date: 7/1/2006 End Date: 12/31/06 Monthly Payment: $100 Start Date: 1/1/2007 End Date: 6/30/07 Monthly Payment: $150 Start Date: 7/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 6/30/2008 I know I can get what I need out of the 2nd option, but if I can get the same results using the first, it will make user input simpler. |
#4
|
|||
|
|||
Setting up data to pull info from dates not stored in fields
True, I should have been clearer. The records are essentially benefit amounts and the dates cover the period where the employee receives that monthly amount. I do not want or need to enter in a separate record for each month, but rather for the period in which they are entitled to that amount. However since the benefit will overlap calendar years I run into an issue when trying to calculate the amount paid within that specific year. I am trying to figure out how to set up the records to make queries easy to create without adding alot of extra records/work for the user. "KARL DEWEY" wrote: The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Your examples are not 'payment records.' Payment records would be a separate record for each payment show the exact date received or posted. Then you could easily pull your data. -- KARL DEWEY Build a little - Test a little "Adrian" wrote: Oops, realized my examples did not contain consistant data (not helpful huh), please refer to the scenario's here when answering question Scenario 1: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 3/31/2007 Monthly Payment: $125 Start Date: 4/1/2007 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 7/31/08 Scenario 2: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 12/31/06 Monthly Payment: $275 Start Date: 1/1/2007 End Date: 3/31/07 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 7/31/2008 Question is, can I create queries to pull monthly data just for 2006 or 2007 using dates set up in scenario 1 or do I need to set up data to look more like scenario 2? "Adrian" wrote: This is probably a bit of a basic question but since I am new to both database design and access it has been a challenge at times to get my head around thinking “normalize” versus the usual cram everything into a spreadsheet mindset. This a new database I am creating to consolidate information that either doesn’t exist yet or is inefficiently stored in far too many places. The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Each employee receives a monthly payment that can change once or twice a year. There is no set rule as to how many changes will occur or in which month they will happen. John Doe Monthly Payment: $275 Start Date: 8/1/06 End Date: 3/30/2007 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/07 End Date: 7/31/08 The issue is that I need to be able pull the total payments made in 2006, 2007 etc. Can I do this with just the dates above, or will I need to break this down further into calendar years like below: JohnDoe Monthly Payment: $100 Start Date: 7/1/2006 End Date: 12/31/06 Monthly Payment: $100 Start Date: 1/1/2007 End Date: 6/30/07 Monthly Payment: $150 Start Date: 7/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 6/30/2008 I know I can get what I need out of the 2nd option, but if I can get the same results using the first, it will make user input simpler. |
#5
|
|||
|
|||
Setting up data to pull info from dates not stored in fields
Create a table named CountNumber with number field CountNUM filled from 0
(zero) through your maximum spread. Use this query editing for your table and field names. SELECT Adrian.EmployeeID, Adrian.Monthly_Payment, DateAdd("m",[CountNUM],[StartDate]) AS [Payment Month], DateAdd("m",[CountNUM],[StartDate]) AS Expr2 FROM Adrian, CountNumber WHERE (((DateAdd("m",[CountNUM],[StartDate])) Between CVDate([Period start]) And CVDate([Period end]) And (DateAdd("m",[CountNUM],[StartDate]))=[EndDate])) ORDER BY Adrian.EmployeeID, DateAdd("m",[CountNUM],[StartDate]); -- KARL DEWEY Build a little - Test a little "Adrian" wrote: True, I should have been clearer. The records are essentially benefit amounts and the dates cover the period where the employee receives that monthly amount. I do not want or need to enter in a separate record for each month, but rather for the period in which they are entitled to that amount. However since the benefit will overlap calendar years I run into an issue when trying to calculate the amount paid within that specific year. I am trying to figure out how to set up the records to make queries easy to create without adding alot of extra records/work for the user. "KARL DEWEY" wrote: The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Your examples are not 'payment records.' Payment records would be a separate record for each payment show the exact date received or posted. Then you could easily pull your data. -- KARL DEWEY Build a little - Test a little "Adrian" wrote: Oops, realized my examples did not contain consistant data (not helpful huh), please refer to the scenario's here when answering question Scenario 1: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 3/31/2007 Monthly Payment: $125 Start Date: 4/1/2007 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 7/31/08 Scenario 2: John Doe Monthly Payment: $275 Start Date: 8/1/2006 End Date: 12/31/06 Monthly Payment: $275 Start Date: 1/1/2007 End Date: 3/31/07 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 7/31/2008 Question is, can I create queries to pull monthly data just for 2006 or 2007 using dates set up in scenario 1 or do I need to set up data to look more like scenario 2? "Adrian" wrote: This is probably a bit of a basic question but since I am new to both database design and access it has been a challenge at times to get my head around thinking “normalize” versus the usual cram everything into a spreadsheet mindset. This a new database I am creating to consolidate information that either doesn’t exist yet or is inefficiently stored in far too many places. The database will include payment records that overlap calendar years but I will need to run queries or reports for a specific year. Each employee receives a monthly payment that can change once or twice a year. There is no set rule as to how many changes will occur or in which month they will happen. John Doe Monthly Payment: $275 Start Date: 8/1/06 End Date: 3/30/2007 Monthly Payment: $125 Start Date: 4/1/07 End Date: 7/31/07 Monthly Payment: $150 Start Date: 8/1/07 End Date: 7/31/08 The issue is that I need to be able pull the total payments made in 2006, 2007 etc. Can I do this with just the dates above, or will I need to break this down further into calendar years like below: JohnDoe Monthly Payment: $100 Start Date: 7/1/2006 End Date: 12/31/06 Monthly Payment: $100 Start Date: 1/1/2007 End Date: 6/30/07 Monthly Payment: $150 Start Date: 7/1/2007 End Date: 12/31/07 Monthly Payment: $150 Start Date: 1/1/2008 End Date: 6/30/2008 I know I can get what I need out of the 2nd option, but if I can get the same results using the first, it will make user input simpler. |
Thread Tools | |
Display Modes | |
|
|