0

My MS Access 013 database had a table that records hours worked on specific projects in [Design Hours] and cross-references with a table of information about our people in [People], who manages whom in [Managers], and information about each project in [Projects]. (This description simplified and serial numbers scraped off for this public posting.)

We have a report that accepts a date interval from a very simple form, then runs a query that should return all records of hours recorded as having been worked in that date interval, with each row of the query results returning the hours, name of the person working, his/her manager's name, and how the hours for that item are divided among out clients. Here's an anonymized query result, for your reference:

Person Project WorkDate Hours PersonID Managers_ID Managers_FirstName Doe Projectname 9/30/2015 8 54 4 Jeff

The weird behavior I'm seeing is that if there are records of the person having worked on the same project twice on the same day, AND if that is the last date of the interval (or possibly the month, the intervals are always monthly), NEITHER record appears in the query result, and thus neither appears in the report. How can I adjust the query to include multiple records on the same day that match the date interval?

I include the SQL of the query for reference. It's much more complex than implied above, partly because it was done by three inexperienced developers working in semi-coordination over a period of years. My apologies for the messiness.

SELECT [Design Hours TABLE].UniqueID, [Design Hours TABLE].Person, [Design Hours TABLE].Project, [Design Hours TABLE].WorkDate, [Design Hours TABLE].Hours, Managers.PersonID, Managers.ID AS Managers_ID, Managers.FirstName AS Managers_FirstName, Managers.LastName AS Managers_LastName, Managers.[NE-Sub], Managers.[DNY-Sub], Managers.Jurisdiction, Managers.Business, Managers.Operations, Managers.Client, Managers.Active AS Managers_Active, Managers.Phone, People.ID AS People_ID, People.FirstName AS People_FirstName, People.LastName AS People_LastName, People.Manager, People.IsInstructor, People.IsDesigner, People.Active AS People_Active, People.ActiveDirectoryName, [Project Request Log TABLE].[LI-Gas], [Project Request Log TABLE].[NYC-Gas], [Project Request Log TABLE].[DNY-Gas], [Project Request Log TABLE].[UNY-Gas], [Project Request Log TABLE].[NE-Gas], [Project Request Log TABLE].[LI-CMS], [Project Request Log TABLE].[NYC-CMS], [Project Request Log TABLE].[DNY-CMS], [Project Request Log TABLE].[UNY-CMS], [Project Request Log TABLE].[NE-CMS], [Project Request Log TABLE].LIPA, [Project Request Log TABLE].[UNY-Dist], [Project Request Log TABLE].[NE-Dist], [Project Request Log TABLE].[UNY-PTO], [Project Request Log TABLE].[NE-PTO], [Project Request Log TABLE].[UNY-PMCC], [Project Request Log TABLE].[NE-PMCC], [Project Request Log TABLE].Powerplant, [Project Request Log TABLE].Other, [Project Request Log TABLE].ProjectName, People.FirstName, People.LastName, [Design Hours TABLE].WorkType, [Project Request Log TABLE].SpecialProject, [Design Hours TABLE].UniqueID, * FROM [Project Request Log TABLE] INNER JOIN ((Managers INNER JOIN People ON Managers.ID = People.Manager) INNER JOIN [Design Hours TABLE] ON People.ID = [Design Hours TABLE].Person) ON [Project Request Log TABLE].ProjectID = [Design Hours TABLE].Project WHERE ((([Design Hours TABLE].WorkDate)<=[Forms]![frmSelectDateRangeOnly]![EndDate] And ([Design Hours TABLE].WorkDate)=[Forms]![frmSelectDateRangeOnly]![StartDate]) AND (([Design Hours TABLE].WorkType)="Design") AND (([Project Request Log TABLE].SpecialProject)=False));

Thanks in advance for any help.

CarlF
  • 226
  • 4
  • 13
  • Nothing in your query jumps out as being an obvious cause of this. Why don't you run some example queries that include records of the type you mention, with enough of the `WHERE` clause (or the `JOIN`ed tables) removed or modified so that the records DO show up? If you can get to a query that DOES include the records that you are interested in, then you can start adding back the correct joins/filters until you identify the root cause of the problem. – SlimsGhost Feb 11 '16 at 23:01
  • @SlimsGhost, I played with the date restrictions a bit and can't seem to locate the issue. HansUp, the WorkDate is a Date/Time field. Thanks. – CarlF Feb 11 '16 at 23:32
  • should `([Design Hours TABLE].WorkDate)=[Forms]![frmSelectDateRangeOnly]![StartDate])` be `([Design Hours TABLE].WorkDate)>=[Forms]![frmSelectDateRangeOnly]![StartDate])` to encompass anything within the range not starting on the range start date? – GavinP Feb 12 '16 at 11:09
  • @CarlF - can you get a targeted query that does actually return the problem records (the two records on the same last day of the time period)? What I mean is basically remove all filters if necessary so that you're returning TOO MUCH data, but so the result set at least includes the records you're focusing on. If you can achieve this, please post the query that you used, and then work on which filter is actually causing them to be EXCLUDED in the final query. – SlimsGhost Feb 12 '16 at 14:40
  • This is really odd. There is one specific project. If hours are credit toward it, they only show up when the end date of the query is the day after the entered work date. Other projects work normally--the entered work date is really checked against the end date, and if it's the same or earlier, the record is in the query results. Maybe run a database cleanup? – CarlF Feb 16 '16 at 20:25
  • Now it appears it may just be a bug. Repair and compress did nothing, but I was able to trace the problem to certain specific records. If I delete a record and type the exact same information into a new record (as revealed by the Access table's datasheet view) the new record is correctly found by the query. It seems that somehow the records in question are showing one date in the datasheet but presenting a different one to the query. That should be impossible, but it's what I'm seeing. Maybe I can fix by exporting the data as CDL and then reimporting the table? – CarlF Feb 16 '16 at 21:17

0 Answers0