Solved

Limit report detail to date range

Posted on 2013-01-04
16
295 Views
Last Modified: 2013-01-07
(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!
0
Comment
Question by:nap0leon
  • 7
  • 6
  • 2
  • +1
16 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38745195
(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}
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38745199
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

0
 
LVL 65

Expert Comment

by:Jim Horn
ID: 38745207
Disregard my (2) comment, which was the SQL Server answer.  Miriam has the correct Access answer in the above comment.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 18

Author Comment

by:nap0leon
ID: 38745253
@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.
0
 
LVL 18

Author Comment

by:nap0leon
ID: 38745265
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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38745267
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

0
 
LVL 61

Expert Comment

by:mbizup
ID: 38745275
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

0
 
LVL 18

Author Comment

by:nap0leon
ID: 38745282
@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
0
 
LVL 18

Author Comment

by:nap0leon
ID: 38745305
@ID: 38745275
Still only 8 records (those that have comments from the past 2 weeks).
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38745307
I think we cross-posted...

Did you catch this comment?  http:#a38745275

_____
Edit--- Uggh! back to the drawing board
0
 
LVL 18

Author Comment

by:nap0leon
ID: 38745316
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
0
 
LVL 18

Author Comment

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

Thanks in advance!!!
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38745331
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,
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 38745377
Ah - I follow what you're trying to do now.

You need a subquery to select the comment records in the date range.  That will get the limited set of comments you want.

If you then join the projects table on that subquery, you'll get all projects with any comments that fall into that date range.  The ResourceFilter creteria gets applied to the outer query:

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

Open in new window

0
 
LVL 7

Expert Comment

by:rgranlund
ID: 38747400
Is it something simple like your dates are wrong?  Change the line DateAdd("d",-14, date() to -7 or -21 and see what happens.
0
 
LVL 18

Author Closing Comment

by:nap0leon
ID: 38750882
Perfect thanks!
"sub query" was the keyword I was missing.
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question