Setting up data to pull info from dates not stored in fields

[Guest]

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.

 

[Guest]

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?

 

[Guest]

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.

 

[Guest]

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.

 

[Guest]

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]);