query to show span of months

D

deb

I have a Query with the below fields:
ProjectName - Text
Start - Date
End - Date
Span - Date -calculated????

Data is:
ProjectName - Start - End
Proj1 - 1/21/08 - 3/1/08
Proj2 - 1/15/08 - 2/12/08
Proj3 - 2/2/08 - 4/20/08

I need a query that will show the projects that are ongoing per month.
I need the query to look like the below example:

ProjectName - Start - End - Span
Proj1 - 1/21/08 - 3/1/08 - Jan2008
Proj1 - 1/21/08 - 3/1/08 - Feb2008
Proj1 - 1/21/08 - 3/1/08 - Mar2008
Proj2 - 1/15/08 - 2/12/08 - Jan2008
Proj2 - 1/15/08 - 2/12/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Mar2008
Proj3 - 2/2/08 - 4/20/08 - Apr2008

How can I build a query that will span the Start through the End fields?
 
J

Jeff Boyce

Not sure I understand what you are trying to do...

By "ongoing", do you mean a project that started before the first of a given
month? Does it have to have an End date after the end of that given month?

How would you categorize a project that started on the 3rd and was done by
the 23rd of the same month?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
K

KARL DEWEY

Create a table named CountNumber with field CountNUM containing integers from
0 (zero) through your maximum spread.
Use this query --
SELECT deb.ProjectName, deb.Start, deb.End,
Format(DateAdd("m",[CountNUM],[Start]),"mmm yyyy") AS Span
FROM CountNumber, deb
WHERE
(((Format(DateAdd("m",[CountNUM],[Start]),"yyyymm"))<=Format([End],"yyyymm")))
ORDER BY deb.ProjectName, deb.Start,
Format(DateAdd("m",[CountNUM],[Start]),"yyyymm");
 
D

deb

this is very close to perfect.

It does not give me the first month for each project.
Proj1 - 1/21/08 - 3/1/08 - Jan2008 === gives me Feb08 and Mar08 but not Jan08
and
if the start and end dates are within the same month, I get nothing.

Any ideas??



--
deb


KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing integers from
0 (zero) through your maximum spread.
Use this query --
SELECT deb.ProjectName, deb.Start, deb.End,
Format(DateAdd("m",[CountNUM],[Start]),"mmm yyyy") AS Span
FROM CountNumber, deb
WHERE
(((Format(DateAdd("m",[CountNUM],[Start]),"yyyymm"))<=Format([End],"yyyymm")))
ORDER BY deb.ProjectName, deb.Start,
Format(DateAdd("m",[CountNUM],[Start]),"yyyymm");

--
KARL DEWEY
Build a little - Test a little


deb said:
I have a Query with the below fields:
ProjectName - Text
Start - Date
End - Date
Span - Date -calculated????

Data is:
ProjectName - Start - End
Proj1 - 1/21/08 - 3/1/08
Proj2 - 1/15/08 - 2/12/08
Proj3 - 2/2/08 - 4/20/08

I need a query that will show the projects that are ongoing per month.
I need the query to look like the below example:

ProjectName - Start - End - Span
Proj1 - 1/21/08 - 3/1/08 - Jan2008
Proj1 - 1/21/08 - 3/1/08 - Feb2008
Proj1 - 1/21/08 - 3/1/08 - Mar2008
Proj2 - 1/15/08 - 2/12/08 - Jan2008
Proj2 - 1/15/08 - 2/12/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Mar2008
Proj3 - 2/2/08 - 4/20/08 - Apr2008

How can I build a query that will span the Start through the End fields?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Did you include a 0 (zero) in the CountNumber table?
--
KARL DEWEY
Build a little - Test a little


deb said:
this is very close to perfect.

It does not give me the first month for each project.
Proj1 - 1/21/08 - 3/1/08 - Jan2008 === gives me Feb08 and Mar08 but not Jan08
and
if the start and end dates are within the same month, I get nothing.

Any ideas??



--
deb


KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing integers from
0 (zero) through your maximum spread.
Use this query --
SELECT deb.ProjectName, deb.Start, deb.End,
Format(DateAdd("m",[CountNUM],[Start]),"mmm yyyy") AS Span
FROM CountNumber, deb
WHERE
(((Format(DateAdd("m",[CountNUM],[Start]),"yyyymm"))<=Format([End],"yyyymm")))
ORDER BY deb.ProjectName, deb.Start,
Format(DateAdd("m",[CountNUM],[Start]),"yyyymm");

--
KARL DEWEY
Build a little - Test a little


deb said:
I have a Query with the below fields:
ProjectName - Text
Start - Date
End - Date
Span - Date -calculated????

Data is:
ProjectName - Start - End
Proj1 - 1/21/08 - 3/1/08
Proj2 - 1/15/08 - 2/12/08
Proj3 - 2/2/08 - 4/20/08

I need a query that will show the projects that are ongoing per month.
I need the query to look like the below example:

ProjectName - Start - End - Span
Proj1 - 1/21/08 - 3/1/08 - Jan2008
Proj1 - 1/21/08 - 3/1/08 - Feb2008
Proj1 - 1/21/08 - 3/1/08 - Mar2008
Proj2 - 1/15/08 - 2/12/08 - Jan2008
Proj2 - 1/15/08 - 2/12/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Mar2008
Proj3 - 2/2/08 - 4/20/08 - Apr2008

How can I build a query that will span the Start through the End fields?
Click to expand...
Click to expand...
Click to expand...
 
D

deb

oops, I did forget the 0. It works perfectly now.

Can you explain what the countnumber table does. How does this work?

How many numbers do I need in the table?

Thank you very, very, very much!!

--
deb


KARL DEWEY said:
Did you include a 0 (zero) in the CountNumber table?
--
KARL DEWEY
Build a little - Test a little


deb said:
this is very close to perfect.

It does not give me the first month for each project.
Proj1 - 1/21/08 - 3/1/08 - Jan2008 === gives me Feb08 and Mar08 but not Jan08
and
if the start and end dates are within the same month, I get nothing.

Any ideas??



--
deb


KARL DEWEY said:
Create a table named CountNumber with field CountNUM containing integers from
0 (zero) through your maximum spread.
Use this query --
SELECT deb.ProjectName, deb.Start, deb.End,
Format(DateAdd("m",[CountNUM],[Start]),"mmm yyyy") AS Span
FROM CountNumber, deb
WHERE
(((Format(DateAdd("m",[CountNUM],[Start]),"yyyymm"))<=Format([End],"yyyymm")))
ORDER BY deb.ProjectName, deb.Start,
Format(DateAdd("m",[CountNUM],[Start]),"yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I have a Query with the below fields:
ProjectName - Text
Start - Date
End - Date
Span - Date -calculated????

Data is:
ProjectName - Start - End
Proj1 - 1/21/08 - 3/1/08
Proj2 - 1/15/08 - 2/12/08
Proj3 - 2/2/08 - 4/20/08

I need a query that will show the projects that are ongoing per month.
I need the query to look like the below example:

ProjectName - Start - End - Span
Proj1 - 1/21/08 - 3/1/08 - Jan2008
Proj1 - 1/21/08 - 3/1/08 - Feb2008
Proj1 - 1/21/08 - 3/1/08 - Mar2008
Proj2 - 1/15/08 - 2/12/08 - Jan2008
Proj2 - 1/15/08 - 2/12/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Mar2008
Proj3 - 2/2/08 - 4/20/08 - Apr2008

How can I build a query that will span the Start through the End fields?
Click to expand...
Click to expand...
Click to expand...
 
K

KARL DEWEY

Create a table named CountNumber with field CountNUM containing integers
from 0 (zero) through your maximum spread.

It adds months to the Start until you reach the End.

--
KARL DEWEY
Build a little - Test a little


deb said:
oops, I did forget the 0. It works perfectly now.

Can you explain what the countnumber table does. How does this work?

How many numbers do I need in the table?

Thank you very, very, very much!!

--
deb


KARL DEWEY said:
Did you include a 0 (zero) in the CountNumber table?
--
KARL DEWEY
Build a little - Test a little


deb said:
this is very close to perfect.

It does not give me the first month for each project.
Proj1 - 1/21/08 - 3/1/08 - Jan2008 === gives me Feb08 and Mar08 but not Jan08
and
if the start and end dates are within the same month, I get nothing.

Any ideas??



--
deb


:

Create a table named CountNumber with field CountNUM containing integers from
0 (zero) through your maximum spread.
Use this query --
SELECT deb.ProjectName, deb.Start, deb.End,
Format(DateAdd("m",[CountNUM],[Start]),"mmm yyyy") AS Span
FROM CountNumber, deb
WHERE
(((Format(DateAdd("m",[CountNUM],[Start]),"yyyymm"))<=Format([End],"yyyymm")))
ORDER BY deb.ProjectName, deb.Start,
Format(DateAdd("m",[CountNUM],[Start]),"yyyymm");

--
KARL DEWEY
Build a little - Test a little


:

I have a Query with the below fields:
ProjectName - Text
Start - Date
End - Date
Span - Date -calculated????

Data is:
ProjectName - Start - End
Proj1 - 1/21/08 - 3/1/08
Proj2 - 1/15/08 - 2/12/08
Proj3 - 2/2/08 - 4/20/08

I need a query that will show the projects that are ongoing per month.
I need the query to look like the below example:

ProjectName - Start - End - Span
Proj1 - 1/21/08 - 3/1/08 - Jan2008
Proj1 - 1/21/08 - 3/1/08 - Feb2008
Proj1 - 1/21/08 - 3/1/08 - Mar2008
Proj2 - 1/15/08 - 2/12/08 - Jan2008
Proj2 - 1/15/08 - 2/12/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Feb2008
Proj3 - 2/2/08 - 4/20/08 - Mar2008
Proj3 - 2/2/08 - 4/20/08 - Apr2008

How can I build a query that will span the Start through the End fields?
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

Week by Day 1
Listing Projects based on Manager Name 7
Append Query Based on Max Date 9
Restricting query data 1
Totals on report 11
Help with Pilot Semi-annual Tracking SQL 2
converting year to date data to month to date 3
Problem with NetworkDays ... Inconsistent results 0

Top