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.

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.

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.

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.

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.