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

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance


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.

.

Relevant Pages

  • RE: Setting up data to pull info from dates not stored in fields
    ... Create a table named CountNumber with number field CountNUM filled from 0 ... but rather for the period in which they are entitled to that amount. ... Your examples are not 'payment records.' ... Scenario 1: ...
    (microsoft.public.access.tablesdbdesign)
  • RE: query question
    ... If we assume a simple scenario where each payment record refers to one ... invoice only, by means of an invoiceno foreign key ... GROUP BY Invoices.invoiceno,, amount; ... Tale note of Allen's caveats, however, if the scenario is more complex. ...
    (microsoft.public.access.queries)
  • Re: Calculating mortgage payments
    ... mortgage, which looks like being only a few years before I switch. ... Both assume that you actually pay ... One method assumes that if, say, the amount outstanding at ... them back, taking into account the time at which each payment is made, ...
    (uk.finance)
  • Re: How are STRIPS for lottery jackpots taxed?
    ... How are STRIPS for lottery jackpots taxed? ... the amount actually received for any payment. ... to include an item in income even though the taxpayer has not yet received ...
    (misc.taxes)
  • Re: Date/If function for past dues
    ... Seems to me that a payment is due ... > THe data that is on sheet 2 is below with column headings. ... > Date Amount Cr. ... this borrower is not past due. ...
    (microsoft.public.excel.worksheet.functions)