C
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 the underlying query I'm trying to build or
on the report.
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”
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 the underlying query I'm trying to build or
on the report.
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”