within month year

D

deb

I need the query to do the following...

fields are MilestoneDt(dateformat) and ChartMo(dateformat)

I want to show the MilestoneDt if it is within the same month as the ChartMo.
example
MilestoneDt are 1/22/2009, 3/3/2009
ChartMo are 1/1/2009, 2/1/2009, 3/1/2009
I want the query to show...

MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009

and so on

When I try it adds an extra line if there is a Milestone date... ie
MilestoneDt ChartMo 1/1/2009
MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt ChartMo 3/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009


It's making me crazy
 
D

deb

SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[PMMilestone])
AS Milestone,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[Unit]) AS
Units,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[MilestoneDate])
AS MilestoneDt, IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo])
AS ChartMo, qProjectsWDates.Mo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE (((IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo]))<>0))
ORDER BY qProjectsWDates.ProjectID, IIf(nz([Mo]) Between nz([projstart]) And
nz([projend]),[mo]);


Thank you in advance, deb
 
K

KARL DEWEY

Try this --
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
[milestonedate] AS Milestone, Format([qProjectsWDates].[Mo], "m/1/yyyy") AS
ChartMo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE Format([milestonedate], "yyyymm") = Format([qProjectsWDates].[Mo],
"yyyymm")
ORDER BY qProjectsWDates.ProjectID, Format([qProjectsWDates].[Mo],
"m/1/yyyy");


deb said:
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[PMMilestone])
AS Milestone,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[Unit]) AS
Units,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[MilestoneDate])
AS MilestoneDt, IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo])
AS ChartMo, qProjectsWDates.Mo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE (((IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo]))<>0))
ORDER BY qProjectsWDates.ProjectID, IIf(nz([Mo]) Between nz([projstart]) And
nz([projend]),[mo]);


Thank you in advance, deb
--
deb


KARL DEWEY said:
Post the SQL of your query.
Click to expand...
Click to expand...
 
P

Paul Shapiro

If I understand correctly, You want to compute the first day of the month
for any given date? Use the DateSerial(year, month, day) function:
DateFirstOfMonth(TheDate) = DateSerial(year(TheDate), month(TheDate), 1)

So your query could join Chart to Milestone something like:

Select ...
From Chart as C Inner Join Milestone as M On DateSerial(year(M.MilestoneDt),
month(M.MilestoneDt), 1) = C.chartDate
 
D

deb

This did eliminate the additional line, that was the issue, however, it also
eleimanated all of the other rows that did not have a Milestone...

I need all rows to show but if there is a MilestoneDate then show the
milestone date in the same row as the corresponding ChartMo.--

Thanks again, you help is much needed
deb


KARL DEWEY said:
Try this --
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
[milestonedate] AS Milestone, Format([qProjectsWDates].[Mo], "m/1/yyyy") AS
ChartMo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE Format([milestonedate], "yyyymm") = Format([qProjectsWDates].[Mo],
"yyyymm")
ORDER BY qProjectsWDates.ProjectID, Format([qProjectsWDates].[Mo],
"m/1/yyyy");


deb said:
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[PMMilestone])
AS Milestone,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[Unit]) AS
Units,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[MilestoneDate])
AS MilestoneDt, IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo])
AS ChartMo, qProjectsWDates.Mo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE (((IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo]))<>0))
ORDER BY qProjectsWDates.ProjectID, IIf(nz([Mo]) Between nz([projstart]) And
nz([projend]),[mo]);


Thank you in advance, deb
--
deb


KARL DEWEY said:
Post the SQL of your query.

:



I need the query to do the following...

fields are MilestoneDt(dateformat) and ChartMo(dateformat)

I want to show the MilestoneDt if it is within the same month as the ChartMo.
example
MilestoneDt are 1/22/2009, 3/3/2009
ChartMo are 1/1/2009, 2/1/2009, 3/1/2009
I want the query to show...

MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009

and so on

When I try it adds an extra line if there is a Milestone date... ie
MilestoneDt ChartMo 1/1/2009
MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt ChartMo 3/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009


It's making me crazy
Click to expand...
Click to expand...
Click to expand...
 
D

deb

I want to show all items in qProjectsWDates and if there is a MilestoneDate
that is within the ChartMo, I want it displayed in the MilestoneDate field.

I get duplicate entries when there is a Milestone Date. One with the
Milestone date and one without the MilestoneDate.

Thx
 
K

KARL DEWEY

Try changing FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
to
FROM qProjectsWDates LEFT JOIN tPMMilestoneDetails ON


deb said:
This did eliminate the additional line, that was the issue, however, it also
eleimanated all of the other rows that did not have a Milestone...

I need all rows to show but if there is a MilestoneDate then show the
milestone date in the same row as the corresponding ChartMo.--

Thanks again, you help is much needed
deb


KARL DEWEY said:
Try this --
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
[milestonedate] AS Milestone, Format([qProjectsWDates].[Mo], "m/1/yyyy") AS
ChartMo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE Format([milestonedate], "yyyymm") = Format([qProjectsWDates].[Mo],
"yyyymm")
ORDER BY qProjectsWDates.ProjectID, Format([qProjectsWDates].[Mo],
"m/1/yyyy");


deb said:
SELECT DISTINCT qProjectsWDates.ProjectID, qProjectsWDates.ProjectName,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[PMMilestone])
AS Milestone,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[Unit]) AS
Units,
IIf((Format([milestonedate],"mmmyyyy"))=(Format([Mo],"mmmyyyy")),[MilestoneDate])
AS MilestoneDt, IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo])
AS ChartMo, qProjectsWDates.Mo
FROM tPMMilestoneDetails LEFT JOIN qProjectsWDates ON
tPMMilestoneDetails.ProjectID = qProjectsWDates.ProjectID
WHERE (((IIf(nz([Mo]) Between nz([projstart]) And nz([projend]),[mo]))<>0))
ORDER BY qProjectsWDates.ProjectID, IIf(nz([Mo]) Between nz([projstart]) And
nz([projend]),[mo]);


Thank you in advance, deb
--
deb


:

Post the SQL of your query.

:



I need the query to do the following...

fields are MilestoneDt(dateformat) and ChartMo(dateformat)

I want to show the MilestoneDt if it is within the same month as the ChartMo.
example
MilestoneDt are 1/22/2009, 3/3/2009
ChartMo are 1/1/2009, 2/1/2009, 3/1/2009
I want the query to show...

MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009

and so on

When I try it adds an extra line if there is a Milestone date... ie
MilestoneDt ChartMo 1/1/2009
MilestoneDt 1/22/2009 ChartMo 1/1/2009
MilestoneDt ChartMo 2/1/2009
MilestoneDt ChartMo 3/1/2009
MilestoneDt 3/3/2009 ChartMo 3/1/2009


It's making me crazy
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

Display month as 01, 02 ect not 1, 2 , 3 2
WORKING WITH REVERSALS 3
Help with Listing last 4 records 4
sorting function yields unwanted results 6
Report with information cross the page 1
Running Sum within Query 4
How to find conflict between time 2
question 4

Top