(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.IDComments.Project IDComments.IDComments.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).
@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
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.
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.
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
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
(2) WHERE DATEADD(d, -14, GETDATE()) > {whatever date column you're using}