Access 2003 Help!!!

  • Thread starter Thread starter Shirley
  • Start date Start date
S

Shirley

I have a table that consists of the following

Start Date End Date Amount
1 12 50,000
1 3 25,000
1 6 75,000

The end date represents a month (ie. 12 equals December).

What is the best way to either create a table or query that divides the
amount by the end date and have the results spread based on the end date
(i.e. if end date is 3, the result will need to be populated for each month
from January through March)? See example below:

Start Date End Date Amount Jan Feb Mar
1 3 75,000 25,000 25,000 25,000

Thank you,
Shirley
 
Try this ---
SELECT [Start Date], [End Date], [Amount], IIF([Start Date]<=1,
[Amount]/([End Date]-[Start Date]+1),0) AS JAN, IIF([Start Date]<=2 And [End
Date]>=2 , [Amount]/([End Date]-[Start Date]+1),0) AS FEB, IIF([Start
Date]<=3 And [End Date]>=3 , [Amount]/([End Date]-[Start Date]+1),0) AS MAR,
IIF([Start Date]<=4 And [End Date]>=4 , [Amount]/([End Date]-[Start
Date]+1),0) AS APR, IIF([Start Date]<=5 And [End Date]>=5 , [Amount]/([End
Date]-[Start Date]+1),0) AS MAY, IIF([Start Date]<=6 And [End Date]>=6 ,
[Amount]/([End Date]-[Start Date]+1),0) AS JUN, IIF([Start Date]<=7 And [End
Date]>=7 , [Amount]/([End Date]-[Start Date]+1),0) AS JUL, IIF([Start
Date]<=8 And [End Date]>=8 , [Amount]/([End Date]-[Start Date]+1),0) AS AUG,
IIF([Start Date]<=9 And [End Date]>=9 , [Amount]/([End Date]-[Start
Date]+1),0) AS SEP, IIF([Start Date]<=10 And [End Date]>=10 , [Amount]/([End
Date]-[Start Date]+1),0) AS OCT, IIF([Start Date]<=11 And [End Date]>=11 ,
[Amount]/([End Date]-[Start Date]+1),0) AS NOV, IIF([Start Date]<=12 And
[End Date]>=12 , [Amount]/([End Date]-[Start Date]+1),0) AS DEC
FROM YourTable;
 
Thank you! It works. However, I would like to enter more fields in the
query, but I am trouble doing this. Currently, I have 16 fields and need to
add about 5 more. How would I do this?

Shirley
--


KARL DEWEY said:
Try this ---
SELECT [Start Date], [End Date], [Amount], IIF([Start Date]<=1,
[Amount]/([End Date]-[Start Date]+1),0) AS JAN, IIF([Start Date]<=2 And [End
Date]>=2 , [Amount]/([End Date]-[Start Date]+1),0) AS FEB, IIF([Start
Date]<=3 And [End Date]>=3 , [Amount]/([End Date]-[Start Date]+1),0) AS MAR,
IIF([Start Date]<=4 And [End Date]>=4 , [Amount]/([End Date]-[Start
Date]+1),0) AS APR, IIF([Start Date]<=5 And [End Date]>=5 , [Amount]/([End
Date]-[Start Date]+1),0) AS MAY, IIF([Start Date]<=6 And [End Date]>=6 ,
[Amount]/([End Date]-[Start Date]+1),0) AS JUN, IIF([Start Date]<=7 And [End
Date]>=7 , [Amount]/([End Date]-[Start Date]+1),0) AS JUL, IIF([Start
Date]<=8 And [End Date]>=8 , [Amount]/([End Date]-[Start Date]+1),0) AS AUG,
IIF([Start Date]<=9 And [End Date]>=9 , [Amount]/([End Date]-[Start
Date]+1),0) AS SEP, IIF([Start Date]<=10 And [End Date]>=10 , [Amount]/([End
Date]-[Start Date]+1),0) AS OCT, IIF([Start Date]<=11 And [End Date]>=11 ,
[Amount]/([End Date]-[Start Date]+1),0) AS NOV, IIF([Start Date]<=12 And
[End Date]>=12 , [Amount]/([End Date]-[Start Date]+1),0) AS DEC
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


Shirley said:
I have a table that consists of the following

Start Date End Date Amount
1 12 50,000
1 3 25,000
1 6 75,000

The end date represents a month (ie. 12 equals December).

What is the best way to either create a table or query that divides the
amount by the end date and have the results spread based on the end date
(i.e. if end date is 3, the result will need to be populated for each month
from January through March)? See example below:

Start Date End Date Amount Jan Feb Mar
1 3 75,000 25,000 25,000 25,000

Thank you,
Shirley
Click to expand...
Click to expand...
 
What would the five other field be? How relate?
--
KARL DEWEY
Build a little - Test a little


Shirley said:
Thank you! It works. However, I would like to enter more fields in the
query, but I am trouble doing this. Currently, I have 16 fields and need to
add about 5 more. How would I do this?

Shirley
--


KARL DEWEY said:
Try this ---
SELECT [Start Date], [End Date], [Amount], IIF([Start Date]<=1,
[Amount]/([End Date]-[Start Date]+1),0) AS JAN, IIF([Start Date]<=2 And [End
Date]>=2 , [Amount]/([End Date]-[Start Date]+1),0) AS FEB, IIF([Start
Date]<=3 And [End Date]>=3 , [Amount]/([End Date]-[Start Date]+1),0) AS MAR,
IIF([Start Date]<=4 And [End Date]>=4 , [Amount]/([End Date]-[Start
Date]+1),0) AS APR, IIF([Start Date]<=5 And [End Date]>=5 , [Amount]/([End
Date]-[Start Date]+1),0) AS MAY, IIF([Start Date]<=6 And [End Date]>=6 ,
[Amount]/([End Date]-[Start Date]+1),0) AS JUN, IIF([Start Date]<=7 And [End
Date]>=7 , [Amount]/([End Date]-[Start Date]+1),0) AS JUL, IIF([Start
Date]<=8 And [End Date]>=8 , [Amount]/([End Date]-[Start Date]+1),0) AS AUG,
IIF([Start Date]<=9 And [End Date]>=9 , [Amount]/([End Date]-[Start
Date]+1),0) AS SEP, IIF([Start Date]<=10 And [End Date]>=10 , [Amount]/([End
Date]-[Start Date]+1),0) AS OCT, IIF([Start Date]<=11 And [End Date]>=11 ,
[Amount]/([End Date]-[Start Date]+1),0) AS NOV, IIF([Start Date]<=12 And
[End Date]>=12 , [Amount]/([End Date]-[Start Date]+1),0) AS DEC
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


Shirley said:
I have a table that consists of the following

Start Date End Date Amount
1 12 50,000
1 3 25,000
1 6 75,000

The end date represents a month (ie. 12 equals December).

What is the best way to either create a table or query that divides the
amount by the end date and have the results spread based on the end date
(i.e. if end date is 3, the result will need to be populated for each month
from January through March)? See example below:

Start Date End Date Amount Jan Feb Mar
1 3 75,000 25,000 25,000 25,000

Thank you,
Shirley
Click to expand...
Click to expand...
Click to expand...
 
Here are the current fields: ID, Asset ID, Amount, StartPd, EndPd, Oct, Nov,
Dec, Jan, Feb, Mar,April,May,June,July,August.

I need to had Sept, Category, and DeptID. Category describes the item (ie.
furniture, auto, medical equipment, etc.). The category can appear multiple
times. There are a number of Dept IDs also.

Ultimately, I will need to sum by category alone for each of the months. I
will then need a separate query to group by DeptID first and then sum by
category for each of the months.

Thanks,
Shirley
--
Shirley


KARL DEWEY said:
What would the five other field be? How relate?
--
KARL DEWEY
Build a little - Test a little


Shirley said:
Thank you! It works. However, I would like to enter more fields in the
query, but I am trouble doing this. Currently, I have 16 fields and need to
add about 5 more. How would I do this?

Shirley
--


KARL DEWEY said:
Try this ---
SELECT [Start Date], [End Date], [Amount], IIF([Start Date]<=1,
[Amount]/([End Date]-[Start Date]+1),0) AS JAN, IIF([Start Date]<=2 And [End
Date]>=2 , [Amount]/([End Date]-[Start Date]+1),0) AS FEB, IIF([Start
Date]<=3 And [End Date]>=3 , [Amount]/([End Date]-[Start Date]+1),0) AS MAR,
IIF([Start Date]<=4 And [End Date]>=4 , [Amount]/([End Date]-[Start
Date]+1),0) AS APR, IIF([Start Date]<=5 And [End Date]>=5 , [Amount]/([End
Date]-[Start Date]+1),0) AS MAY, IIF([Start Date]<=6 And [End Date]>=6 ,
[Amount]/([End Date]-[Start Date]+1),0) AS JUN, IIF([Start Date]<=7 And [End
Date]>=7 , [Amount]/([End Date]-[Start Date]+1),0) AS JUL, IIF([Start
Date]<=8 And [End Date]>=8 , [Amount]/([End Date]-[Start Date]+1),0) AS AUG,
IIF([Start Date]<=9 And [End Date]>=9 , [Amount]/([End Date]-[Start
Date]+1),0) AS SEP, IIF([Start Date]<=10 And [End Date]>=10 , [Amount]/([End
Date]-[Start Date]+1),0) AS OCT, IIF([Start Date]<=11 And [End Date]>=11 ,
[Amount]/([End Date]-[Start Date]+1),0) AS NOV, IIF([Start Date]<=12 And
[End Date]>=12 , [Amount]/([End Date]-[Start Date]+1),0) AS DEC
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


:

I have a table that consists of the following

Start Date End Date Amount
1 12 50,000
1 3 25,000
1 6 75,000

The end date represents a month (ie. 12 equals December).

What is the best way to either create a table or query that divides the
amount by the end date and have the results spread based on the end date
(i.e. if end date is 3, the result will need to be populated for each month
from January through March)? See example below:

Start Date End Date Amount Jan Feb Mar
1 3 75,000 25,000 25,000 25,000

Thank you,
Shirley
Click to expand...
Click to expand...
Click to expand...
 
I need to had Sept, Category, and DeptID.
My post included September.

I think you can just add Category, and DeptID like this ---
SELECT [Category], [DeptID], [Start Date], [End Date], [Amount], IIF([Start
Date]

--
KARL DEWEY
Build a little - Test a little


Shirley said:
Here are the current fields: ID, Asset ID, Amount, StartPd, EndPd, Oct, Nov,
Dec, Jan, Feb, Mar,April,May,June,July,August.

I need to had Sept, Category, and DeptID. Category describes the item (ie.
furniture, auto, medical equipment, etc.). The category can appear multiple
times. There are a number of Dept IDs also.

Ultimately, I will need to sum by category alone for each of the months. I
will then need a separate query to group by DeptID first and then sum by
category for each of the months.

Thanks,
Shirley
--
Shirley


KARL DEWEY said:
What would the five other field be? How relate?
--
KARL DEWEY
Build a little - Test a little


Shirley said:
Thank you! It works. However, I would like to enter more fields in the
query, but I am trouble doing this. Currently, I have 16 fields and need to
add about 5 more. How would I do this?

Shirley
--


:

Try this ---
SELECT [Start Date], [End Date], [Amount], IIF([Start Date]<=1,
[Amount]/([End Date]-[Start Date]+1),0) AS JAN, IIF([Start Date]<=2 And [End
Date]>=2 , [Amount]/([End Date]-[Start Date]+1),0) AS FEB, IIF([Start
Date]<=3 And [End Date]>=3 , [Amount]/([End Date]-[Start Date]+1),0) AS MAR,
IIF([Start Date]<=4 And [End Date]>=4 , [Amount]/([End Date]-[Start
Date]+1),0) AS APR, IIF([Start Date]<=5 And [End Date]>=5 , [Amount]/([End
Date]-[Start Date]+1),0) AS MAY, IIF([Start Date]<=6 And [End Date]>=6 ,
[Amount]/([End Date]-[Start Date]+1),0) AS JUN, IIF([Start Date]<=7 And [End
Date]>=7 , [Amount]/([End Date]-[Start Date]+1),0) AS JUL, IIF([Start
Date]<=8 And [End Date]>=8 , [Amount]/([End Date]-[Start Date]+1),0) AS AUG,
IIF([Start Date]<=9 And [End Date]>=9 , [Amount]/([End Date]-[Start
Date]+1),0) AS SEP, IIF([Start Date]<=10 And [End Date]>=10 , [Amount]/([End
Date]-[Start Date]+1),0) AS OCT, IIF([Start Date]<=11 And [End Date]>=11 ,
[Amount]/([End Date]-[Start Date]+1),0) AS NOV, IIF([Start Date]<=12 And
[End Date]>=12 , [Amount]/([End Date]-[Start Date]+1),0) AS DEC
FROM YourTable;
--
KARL DEWEY
Build a little - Test a little


:

I have a table that consists of the following

Start Date End Date Amount
1 12 50,000
1 3 25,000
1 6 75,000

The end date represents a month (ie. 12 equals December).

What is the best way to either create a table or query that divides the
amount by the end date and have the results spread based on the end date
(i.e. if end date is 3, the result will need to be populated for each month
from January through March)? See example below:

Start Date End Date Amount Jan Feb Mar
1 3 75,000 25,000 25,000 25,000

Thank you,
Shirley
Click to expand...
Click to expand...
Click to expand...
 
Back
Top