nap0leon

asked on 1/4/2013

Limit report detail to date range

(Yes, the naming conventions on the tables and everything suck... I haven't worked in Access in, like, 15 years so this is my test database to get used to the updated UI, etc. I'll redo it once I get proof of concept worked out)

I have a simple database with two tables:
1- Project List
2- Comments

Every project may have multiple comments. Foreign-key between [Project List].ID and Comments.[Project ID]

Relevant field names are:

Project List.ID
Comments.Project ID
Comments.ID
Comments.Comment Date

Open in new window

My current report lists all projects (for a given Resource) and all comments. I want my report to show all projects (for a given Resource), but only comments that are less than 2 weeks old (some projects will not have any comments in the relevant time frame).

Here is the current SQL View:

SELECT *
FROM [Project List] INNER JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter];

Open in new window

Thanks!

Microsoft AccessSQL

Last Comment

nap0leon

8/22/2022 - Mon

Jim Horn

1/4/2013

(1) Change your INNER JOIN to a LEFT JOIN, which returns all rows from the left [Project List] table, and only matching values in Comments table.

(2) WHERE DATEADD(d, -14, GETDATE()) > {whatever date column you're using}

mbizup

1/4/2013

Try this:

SELECT *
FROM [Project List] INNER JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter] AND Comments.[Comment Date] > DateAdd("d",-14, date())

Open in new window

Jim Horn

1/4/2013

Disregard my (2) comment, which was the SQL Server answer. Miriam has the correct Access answer in the above comment.

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!

James Murphy

nap0leon

1/4/2013

ASKER

@mbiz - close, but that's only returning projects that have comments within the specified date range

SELECT *
FROM [Project List] INNER JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter]
AND Comments.[Comment Date] > DateAdd("d",-14, date());

Open in new window

I need *all* projects but only comments from the last 2 weeks.
I've tried left joins, outer joins, inner joins, etc.

nap0leon

1/4/2013

ASKER

Super simplified example report. In this example, Project 2 does not have any comments (status updates) within the past 2 weeks.

Project 1
	Comment from 4-JAN-2013
	Comment from 24-DEC-2012

Project 2

Project 3
	Comment from 27-DEC-2012

Open in new window

mbizup

1/4/2013

Just verifying... you tried Jim's LEFT JOIN suggestion?

SELECT *
FROM [Project List] LEFT JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter]
AND Comments.[Comment Date] > DateAdd("d",-14, date());

Open in new window

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

mbizup

1/4/2013

If that doesn't work, try Jim's LEFT JOIN, accounting for nulls like this:

SELECT *
FROM [Project List] LEFT JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter]
AND (Comments.[Comment Date] > DateAdd("d",-14, date()) OR Comments.[Comment Date] IS NULL)

Open in new window

nap0leon

1/4/2013

ASKER

@ID: 38745267
Yes - that returns me only 8 records (which are the number of comments from the past 2 weeks)
I have 27 projects with 33 total comments.

So my report should return between 27 and 33 rows (at least one for every project, projects that have multiple comments in the time frame would appear twice)

Will try the "NULLS" now... one sec

nap0leon

1/4/2013

ASKER

@ID: 38745275
Still only 8 records (those that have comments from the past 2 weeks).

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

rwheeler23

mbizup

1/4/2013

I think we cross-posted...

Did you catch this comment? http:#a38745275

_____
Edit--- Uggh! back to the drawing board

nap0leon

1/4/2013

ASKER

In case it would be helpful to play inside the database.

The query I am adjusting is "qry-Project List Query w/ Comments"
It is used for the report "Report- Projects for Resource"

When it prompts you for a Resource, enter my name "Jason Dahlin"
Projects.zip

nap0leon

1/4/2013

ASKER

Heading home... won't be able to check on this until Monday (no MS Office on my home computers)

Thanks in advance!!!

Get an unlimited membership to EE for less than $4 a week.

Unlimited question asking, solutions, articles and more.

mbizup

1/4/2013

Backing up a bit and removing the Date criteria...

Do you see the records you want to select when you run this query?

SELECT *
FROM [Project List] LEFT JOIN Comments ON [Project List].ID = Comments.[Project ID]
WHERE [Project List].Resource=[ResourceFilter]

Open in new window

What do you see in the Comments.CommentDate field for a representative set of the records that we are not picking up?

__

Edit: I'll take a look at the db,

ASKER CERTIFIED SOLUTION

mbizup

1/4/2013

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a question

Robert Granlund

1/5/2013

Is it something simple like your dates are wrong? Change the line DateAdd("d",-14, date() to -7 or -21 and see what happens.

nap0leon

1/7/2013

ASKER

Perfect thanks!
"sub query" was the keyword I was missing.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst

William Peck