Learn how to set up LaraDock in a Laravel project - LaraDock gives us an easy way to run a Laravel application using Docker in a single command.
COURSE of the MONTH
12 days, 22 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Limit report detail to date range
Posted on 2013-01-04
(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:
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:
Thanks!
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
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];
Thanks!
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
7
6
2- +1
16 Comments
Active today
(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}
(2) WHERE DATEADD(d, -14, GETDATE()) > {whatever date column you're using}
Active today
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())
Active today
Disregard my (2) comment, which was the SQL Server answer. Miriam has the correct Access answer in the above comment.
@mbiz - close, but that's only returning projects that have comments within the specified date range
I need *all* projects but only comments from the last 2 weeks.
I've tried left joins, outer joins, inner joins, etc.
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());
I need *all* projects but only comments from the last 2 weeks.
I've tried left joins, outer joins, inner joins, etc.
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
Active today
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());
Active today
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)
@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
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
Active today
I think we cross-posted...
Did you catch this comment? http:#a38745275
_____
Edit--- Uggh! back to the drawing board
Did you catch this comment? http:#a38745275
_____
Edit--- Uggh! back to the drawing board
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
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
Heading home... won't be able to check on this until Monday (no MS Office on my home computers)
Thanks in advance!!!
Thanks in advance!!!
Active today
Backing up a bit and removing the Date criteria...
Do you see the records you want to select when you run this query?
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,
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]
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,
Active today
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:
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]));
Active 1 day ago
Is it something simple like your dates are wrong? Change the line DateAdd("d",-14, date() to -7 or -21 and see what happens.
Featured Post
To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder.
But why not let Windows retr…
In part one, we reviewed the prerequisites required for installing SQL Server vNext.
In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another.
Base the dependent combo on a query for its row source:
Add a reference to the first combo on the form as criteria i…
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 …
Suggested Courses
Course of the Month12 days, 22 hours left to enroll
777 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.