Advanced report code or underlying query help

[connecttodaniel]

MS Access -- Complicated Design Problems

I’ve designed a table that we’ll call “Deductions.“ In
this table, there are, among others, five columns that
contain the data: [Name], [StartingDate], [EndingDate],
[Amount], [Explanation]. The Name is a lookup field
related to another table. The other columns contain data
that I want automatically deducted each month from at
currency total.

An example of the Data:

Smith, John R. / 01-01-2000 / 02-28-2003 / $100.00/ Car
Insurance

Jones, Betty T. / 05-01-2004 / 06-30-2004 / $258.79 /
Retirement

Brown, Heather G. / 07-01-2004 / 12-31-2006 / $50.00 /
Company Health-Care

Brown, Heather G. / 09-01-2004 / 09-30-2004 / $45.00 /
Media Center Fee

Essentially, the table indicates the John R. Smith had
100.00 taken out of his account every month for car
insurance from 1/1/00 to 2/28/03, etc...

The problem is I want a report to list for each person
(first column) the amount deducted each month in the
current year (up to today’s date).

The report data should look like this:
Brown, Heather G.

2004 Jan $50.00
2004 Feb $50.00
....etc $50.00
....etc $50.00
2004 Sept $95.00

Problem: I can write formulas / set up the query for
everything except the range of dates. Brown’s 2004 - 2006
monthly deduction needs to be listed individually. I have
a query that uses the DateDiff() function and that works
fine (tells me she had monthly deductions for Company
health care AND one Deduction for the media center fee).

Questions
1)The report is printed monthly - how do i get Brown to
list ONLY july, aug, sept when her ending date is set for
2006?
2)How do I list each month seperately --- 50 for july, 50
for aug, 50 + 45 for sept rather an a conglomerate total?

It seems there needs to be some sort of “replication”
or “duplication” function. Using the DateDiff to get the
number of months, there should be some way to have that
expanded so it lists each month individually. This could
be either in query or some code or special function.


Note: Right now the process “works” by Append queries
moving the appropriate data to a table once a month, using
AutoExec. I don’t like the way it works now because if you
change a value, it doesn’t change all the tables it copied
the data into during past months...

--- Using Office XP Pro.


HELP!!!

Thanks, “connecttodaniel”

 

[Allen Browne]

There are several issues here, but the heart of it seems to be the repeating
payment schedule.

If these are all monthly payments, it would be possible to generate the
payment dates in a query by using a Cartesian Product. This involves having
a table of dates, with an entry for the 1st of each month. Include this
table in your query, with no join. You can then list the payment dates by
entering a calculated field:
DateSerial(Year([tblDate].[TheDate], Month([tblDate].[TheDate],
Day([StartingDate])
and setting the Criteria to:
(Between [StartingDate] And [EndingDate]) And <= Date()

If the payment date periods vary (e.g. weekly, monthly, every 30 days, ...),
it is probably worth creating a PaymentSchedule table, with a record for
each date a payment is due. The code to do that would use a VBA loop to
OpenRecordset, AddNew, and keep DateAdd() the number of days/weeks/months
until the ending date is reached. It is then easy to query this table.

Either way, once you have the list of payment dates, you can then query that
to GROUP BY Year(PaymentDate), Month(PaymentDate). Perhaps even easier, you
can create a report that lists all the payments in the detail section, and
has a group footer by month. In the report's Open event, decide whether you
want to toggle the Visible property of the Detail section to hide the
details, or leave it visible to show the details.

Hope that's enough to get you going.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

MS Access -- Complicated Design Problems

I’ve designed a table that we’ll call “Deductions.“
In
this table, there are, among others, five columns that
contain the data: [Name], [StartingDate], [EndingDate],
[Amount], [Explanation]. The Name is a lookup field
related to another table. The other columns contain data
that I want automatically deducted each month from at
currency total.

An example of the Data:

Smith, John R. / 01-01-2000 / 02-28-2003 / $100.00/ Car
Insurance

Jones, Betty T. / 05-01-2004 / 06-30-2004 / $258.79 /
Retirement

Brown, Heather G. / 07-01-2004 / 12-31-2006 / $50.00 /
Company Health-Care

Brown, Heather G. / 09-01-2004 / 09-30-2004 / $45.00 /
Media Center Fee

Essentially, the table indicates the John R. Smith had
100.00 taken out of his account every month for car
insurance from 1/1/00 to 2/28/03, etc...

The problem is I want a report to list for each person
(first column) the amount deducted each month in the
current year (up to today’s date).

The report data should look like this:
Brown, Heather G.

2004 Jan $50.00
2004 Feb $50.00
....etc $50.00
....etc $50.00
2004 Sept $95.00

Problem: I can write formulas / set up the query for
everything except the range of dates. Brown’s 2004 - 2006
monthly deduction needs to be listed individually. I have
a query that uses the DateDiff() function and that works
fine (tells me she had monthly deductions for Company
health care AND one Deduction for the media center fee).

Questions
1)The report is printed monthly - how do i get Brown to
list ONLY july, aug, sept when her ending date is set for
2006?
2)How do I list each month seperately --- 50 for july, 50
for aug, 50 + 45 for sept rather an a conglomerate total?

It seems there needs to be some sort of “replication”
or “duplication” function. Using the DateDiff to get the
number of months, there should be some way to have that
expanded so it lists each month individually. This could
be either in query or some code or special function.


Note: Right now the process “works” by Append queries
moving the appropriate data to a table once a month, using
AutoExec. I don’t like the way it works now because if you
change a value, it doesn’t change all the tables it copied
the data into during past months...

--- Using Office XP Pro.


HELP!!!

Thanks, “connecttodaniel”