Query: Advanced Help Needed to "repeat" rows / split months

[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.


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”

 

[[MVP] S.Clark]

First, please note that there is a Reports newsgroup, dedicated to Reporting
in Access. This one is geared towards SQL and the Query object.

Reports allowing grouping of data. Choose View / Sorting and Grouping from
the main menu.

Next, to restrict the data, you can apply criteria to the query.

HTH,
--
Steve Clark, Access MVP
FMS, Inc.
www.fmsinc.com/consulting

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.


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”

 

[Gary Walter]

Hi Daniel,

If I understand correctly,
I would first create a table "tblNum"
with one field "Num"
with values from 1 to 12 (or even
larger if you might be enumerating
bigger spans later).

Use this table to enumerate your dates
in the following query:

qryDeductDates


SELECT DateSerial(Year(Date()),[Num],1) AS DeductDate
FROM tblNum
WHERE (((DateSerial(Year(Date()),[Num],1))
Between DateSerial(Year(Date()),1,1)
And
Date();

resulting in (today is Sept 14, 2004):

DeductDate
1/1/2004
2/1/2004
3/1/2004
4/1/2004
5/1/2004
6/1/2004
7/1/2004
8/1/2004
9/1/2004

Then "Cartesian join" this query to Deductions:

SELECT
Deductions.[Name],
Format([DeductDate],"yyyy mmm") AS DeductMonth,
Deductions.Amount,
Deductions.Explanation
FROM qryDeductDates, Deductions
WHERE
(((qryDeductDates.DeductDate)>=[Deductions].[StartingDate]
And
(qryDeductDates.DeductDate)<=[Deductions].[EndingDate]))
ORDER BY
Deductions.[Name],
qryDeductDates.DeductDate;

which would give the following results
using the data you have given:

Name DeductMonth Amount Explanation
Brown, Heather G. 2004 Jul $50.00 Company Health-Care
Brown, Heather G. 2004 Aug $50.00 Company Health-Care
Brown, Heather G. 2004 Sep $45.00 Media Center Fee
Brown, Heather G. 2004 Sep $50.00 Company Health-Care
Jones, Betty T. 2004 May $258.79 Retirement
Jones, Betty T. 2004 Jun $258.79 Retirement

I assume you are not really using a field name
of "Name" since it is a reserved word, and you
may have to adjust for this "lookup field" which,
if it is a lookup field, is discouraged by most here.

Please respond back if I have misunderstood.

Good luck,

Gary Walter

:
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.


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”