Access 2003 Help!!!

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
 
K

KARL DEWEY

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;
 
S

Shirley

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

KARL DEWEY

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

Shirley

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

KARL DEWEY

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Like to start using Access 1
Sum If range of dates date range, sum totals 7
Pivot Table - Compare Current Year Sales To Previous Year Sales 0
Access 2007 - return maximum date 1
Help showing active months between two dates 5
Update cell value based on name and specific date 0
Crate Table Query - Closest to the 15th day..... 0
Conditional Format based on Other Cells 2

Top