name and date with conflicting criteria

[tbradley]

I am working on an older database that was built on the 2002/2003 version of Access. When we converted to 2007/2010 Access we lost a small function of the database and cannot find a way to override it. We have a report that prints all names with a summary of hours by the quarter. The database retains all data. After conversion the report will not print all names, only those that were in the database prior to conversion and those with hours in the current quarter. Is there a way to write a multiple set of criteria that will allow the names and the dates to function separately?

[Dal Jeanis]

1) Find the query the report is based on.
2) Bring up the QUery in SQL view.
3) Copy and Post the SQL here.

[tbradley]

1) Find the query the report is based on.
2) Bring up the QUery in SQL view.
3) Copy and Post the SQL here.

SELECT [PSD and PSO Information Table].[LAST NAME], [PSD and PSO Information Table].FIRST, [PSD and PSO Information Table].[ID #], Sum([PSD and PSO OT Hours Table].HOURS) AS SumOfHOURS, [PSD and PSO OT Hours Table].DATE
FROM [PSD and PSO Information Table] INNER JOIN [PSD and PSO OT Hours Table] ON [PSD and PSO Information Table].[ID #] = [PSD and PSO OT Hours Table].[ID #]
GROUP BY [PSD and PSO Information Table].[LAST NAME], [PSD and PSO Information Table].FIRST, [PSD and PSO Information Table].[ID #], [PSD and PSO OT Hours Table].DATE
HAVING ((([PSD and PSO OT Hours Table].DATE)>=#10/1/2013# Or ([PSD and PSO OT Hours Table].DATE)=#1/1/2011#))
ORDER BY [PSD and PSO Information Table].[LAST NAME];

[Dal Jeanis]

I've aliased the tables and structured your query to make it more readable. This is the same query as yours. If you go into design view, right-click the tables, bring up the properties pane, and change the name of the tables in the top box to T1 and T2 respectively, this is what your query looks like.

SELECT 
   T1.[LAST NAME], 
   T1.FIRST, 
   T1.[ID #], 
   Sum(T2.HOURS) AS SumOfHOURS, 
   T2.DATE
FROM 
   [PSD and PSO Information Table]  AS T1
   INNER JOIN 
   [PSD and PSO OT Hours Table] AS T2 ON 
   T1.[ID #] = T2.[ID #]
GROUP BY 
   T1.[LAST NAME], 
   T1.FIRST, 
   T1.[ID #], 
   T2.DATE
HAVING 
   (((T2.DATE)>=#10/1/2013# 
  Or (T2.DATE)=#1/1/2011#))
ORDER BY 
  T1.[LAST NAME];

Okay, so the above code is asking the database to do this:

1) Join my information table and my OT hours table on their ID#s
2) Include only records where the date is 
   A) greater than or equal to 10/1/2013 or
   B) exactly equal to 1/1/2011.
3) Sum up the Hours for each value of "Date".
4) Sort it by Name.

I'd like to point out that your table and field names are problematic. "Date" and "First" are reserved words and function names. You should also avoid spaces and special characters in field and table names.

I'm going to assume that the ID# gives the unique person, so that you don't really need to group by the names, just sort by them afterward. I'm also going to assume that if two people have the same last name, you want their first names in alpha order.

Here's a fix for your query, without changing the table or field names. I'm just aliasing them ("OldName AS NewName") to make the query more readable.

SELECT 
   T1.[ID #] AS IDNumber, 
   First(T1.[LAST NAME]) AS LastName, 
   First(T1.FIRST) AS FirstName, 
   T2.DATE AS OtDate,
   Sum(T2.HOURS) AS SumOfHOURS 
FROM 
   [PSD and PSO Information Table]  AS T1
   INNER JOIN 
   [PSD and PSO OT Hours Table] AS T2 ON 
   T1.[ID #] = T2.[ID #]
GROUP BY 
   T1.[ID #], 
   T2.DATE
HAVING 
   (((T2.DATE)>=#10/1/2013# 
  Or (T2.DATE)=#1/1/2011#))
ORDER BY 
  LastName, FirstName;

Now, all you have to do is figure out what your desired criteria are, and code that in the "HAVING" clause. If you explain exactly what you'd like the output to be, we can figure out that code for you.

[tbradley]

I've aliased the tables and structured your query to make it more readable. This is the same query as yours. If you go into design view, right-click the tables, bring up the properties pane, and change the name of the tables in the top box to T1 and T2 respectively, this is what your query looks like.

SELECT 
   T1.[LAST NAME], 
   T1.FIRST, 
   T1.[ID #], 
   Sum(T2.HOURS) AS SumOfHOURS, 
   T2.DATE
FROM 
   [PSD and PSO Information Table]  AS T1
   INNER JOIN 
   [PSD and PSO OT Hours Table] AS T2 ON 
   T1.[ID #] = T2.[ID #]
GROUP BY 
   T1.[LAST NAME], 
   T1.FIRST, 
   T1.[ID #], 
   T2.DATE
HAVING 
   (((T2.DATE)>=#10/1/2013# 
  Or (T2.DATE)=#1/1/2011#))
ORDER BY 
  T1.[LAST NAME];

Okay, so the above code is asking the database to do this:

1) Join my information table and my OT hours table on their ID#s
2) Include only records where the date is 
   A) greater than or equal to 10/1/2013 or
   B) exactly equal to 1/1/2011.
3) Sum up the Hours for each value of "Date".
4) Sort it by Name.

I'd like to point out that your table and field names are problematic. "Date" and "First" are reserved words and function names. You should also avoid spaces and special characters in field and table names.

I'm going to assume that the ID# gives the unique person, so that you don't really need to group by the names, just sort by them afterward. I'm also going to assume that if two people have the same last name, you want their first names in alpha order.

Here's a fix for your query, without changing the table or field names. I'm just aliasing them ("OldName AS NewName") to make the query more readable.

SELECT 
   T1.[ID #] AS IDNumber, 
   First(T1.[LAST NAME]) AS LastName, 
   First(T1.FIRST) AS FirstName, 
   T2.DATE AS OtDate,
   Sum(T2.HOURS) AS SumOfHOURS 
FROM 
   [PSD and PSO Information Table]  AS T1
   INNER JOIN 
   [PSD and PSO OT Hours Table] AS T2 ON 
   T1.[ID #] = T2.[ID #]
GROUP BY 
   T1.[ID #], 
   T2.DATE
HAVING 
   (((T2.DATE)>=#10/1/2013# 
  Or (T2.DATE)=#1/1/2011#))
ORDER BY 
  LastName, FirstName;

Now, all you have to do is figure out what your desired criteria are, and code that in the "HAVING" clause. If you explain exactly what you'd like the output to be, we can figure out that code for you.

ok, what we need to add to the HAVING clause is to have the report print ALL names regardless of OT hours based on the date field. In other words we need all names and the hours even if no hours were entered for the quarter.

[Dal Jeanis]

Okay, that may be a problem, since in *THAT* query the date field is coming from the OT Hours table. I'm going to switch the SQL to a LEFT JOIN, so it will include everyone regardless of the presence or absence of overtime hours records, and use the NZ function to provide a default of current date and zero hours.

Try this -

SELECT 
   T1.[ID #] AS IDNumber, 
   First(T1.[LAST NAME]) AS LastName, 
   First(T1.FIRST) AS FirstName, 
   NZ(T2.DATE,Date) AS OtDate,
   Sum(NZ(T2.HOURS,0)) AS SumOfHOURS 
FROM 
   [PSD and PSO Information Table]  AS T1
   LEFT JOIN 
   [PSD and PSO OT Hours Table] AS T2 ON 
   T1.[ID #] = T2.[ID #]
GROUP BY 
   T1.[ID #], 
   NZ(T2.DATE,Date)
ORDER BY 
  LastName, FirstName;

By the way, quoting the whole prior post isn't useful - please delete any part that isn't relevant to your own comments.

[tbradley]

OK, I'm having trouble writing in SQL. When I write in this code, I can't save it due to a syntax error on the PSD & PSD INFORMATION TABLE LASTNAME showing "missing operator". Also, I don't understand how to input NZ.
Please, please help.


SELECT [PSD & PSO INFORMATION TABLE].IDNumber, (PSD & PSO INFORMATION TABLE [LASTNAME]), First (PSD & PSO INFORMATION TABLE FIRSTNAME), PSD & PSDO OT HOURS ENTRY OTDate, Sum (PSD & PSDO OT HOURS ENTRY SumofHOURS)
FROM [PSD & PSDO OT HOURS ENTRY] INNER JOIN [PSD & PSO INFORMATION TABLE] ON [PSD & PSDO OT HOURS ENTRY].IDNumber = [PSD & PSO INFORMATION TABLE].[IDNumber]
GROUP BY [PSD & PSO INFORMATION TABLE],IDNumber, Nz([PSD & PSDO OT HOURS ENTRY].OTDate)
HAVING ((([PSD & PSDO OT HOURS ENTRY].OTDAte)>=#10/1/2013# Or ([PSD & PSDO OT HOURS ENTRY].OTDAte)=#1/1/2011#))
ORDER BY [PSD & PSO INFORMATION TABLE].FIRSTNAME, [PSD & PSO INFORMATION TABLE].LASTNAME;

[Dal Jeanis]

[psd & pso information table].[lastname],
Two methods -
1) Put square braces around the exact name of each table, then a dot, then square braces around the exact name of each field. The braces aren't always mandatory, but Access will accept (and delete) any braces it doesn't need.
2) PUT "AS XX" after the table name in the FROM clause of the SQL, and then use XX. instead of the table name everywhere else in the SQL. I ALWAYS do that when I'm coding SQL by hand.


NOTE - If you gave the actual SQL in your post number 3, then you should be able to just paste my code from post #6 into the SQL view of a blank query and it should work as is.

[tbradley]

Dal,
Thank you so much for your help. We have built a small practice query and report to experiment with. We named things correctly in it. After applying the SQL that you sent we are getting a sum of the hours after 10/1/2013, just like we want, but we are still not getting the names that have 0 hours after 10/1/2013. Attached is the SQL that we are currently trying. Any suggestions on how to get the report to show the null entries?

SELECT T1.IDNumber, T1.LastName, T1.FirstName, Sum(Nz(T2.OTHours,0)) AS SumOfOTHours
FROM T1 LEFT JOIN T2 ON T1.[IDNumber] = T2.[IDNumber]
WHERE (((Nz([T2].[OTDate]))>=#10/1/2013# Or (Nz([T2].[OTDate]))=#1/1/2011#))
GROUP BY T1.IDNumber, T1.LastName, T1.FirstName
ORDER BY T1.LastName, T1.FirstName;

Sorry to be such a pain, but we have to use this report every day, and the originator has retired.

[Dal Jeanis]

1) I wasn't telling you to rename the entire tables to T1 and T2. You can leave the tables as descriptive names, and in the FROM clause, use

FROM MyTable1 AS T1, MyTable2 AS T2

as a way of shortening the table name in the rest of the code.

2) NZ has two parameters. The first is the normal field returned, the second is what to return if that first field is Null. If you leave the second field blank, then it will return zero. Zero will not be >= #10/1/2013# and will not be = #1/1/2011#.
Try This:

SELECT 
  T1.IDNumber, 
  First(T1.LastName), 
  First(T1.FirstName), 
  Sum(Nz(T2.OTHours,0)) AS SumOfOTHours
FROM 
  T1 
  LEFT JOIN 
  T2 
ON T1.[IDNumber] = T2.[IDNumber]
WHERE 
  ( ((Nz([T2].[OTDate],Date))>=#10/1/2013# 
  Or (Nz([T2].[OTDate],Date))=#1/1/2011#))
GROUP BY T1.IDNumber
ORDER BY First(T1.LastName), First(T1.FirstName);

[tbradley]

Dal,
Sorry, but we cannot get Access 2010 to give us the results we were getting in 2003. We have tried what you sent us, and have tried rewriting pieces and parts, but nothing seems to give us the names of people that do not have overtime entries in the current quarter. If there is anything else that you can recommend we would greatly appreciate it. Thank you for all the time and help you have given us.

[Dal Jeanis]

1) Please post the exact SQL that worked in 2003.

2) Please post the current structure of the tables.

3) I hope you are doing all this "rewriting" in test copies of the database, and backing up early and often. Iterative rewriting often causes other things to break, and that should only happen in junk databases. Each time you think you have developed a solution, port only those required changes into a fresh test copy of the database, and test the heck out of it. If it still works, then do the same steps to port the changes to the production database. If not, then (again in a fresh copy) figure out what part of the actual changes you missed.

[tbradley]

This is what is left of the query after the conversion. It still looks like 2003, but works kind of like 2010.
SELECT [PSD and PSO Information Table].[LAST NAME], [PSD and PSO Information Table].FIRST, [PSD and PSO Information Table].[ID #], Sum([PSD and PSO OT Hours Table].HOURS) AS SumOfHOURS, [PSD and PSO OT Hours Table].DATE
FROM [PSD and PSO Information Table] INNER JOIN [PSD and PSO OT Hours Table] ON [PSD and PSO Information Table].[ID #] = [PSD and PSO OT Hours Table].[ID #]
GROUP BY [PSD and PSO Information Table].[LAST NAME], [PSD and PSO Information Table].FIRST, [PSD and PSO Information Table].[ID #], [PSD and PSO OT Hours Table].DATE
HAVING ((([PSD and PSO OT Hours Table].DATE)>=#10/1/2013# Or ([PSD and PSO OT Hours Table].DATE)=#1/1/2011#))
ORDER BY [PSD and PSO Information Table].[LAST NAME];
This is the actual original (again after some conversion) that does not list by name, but lists by overtime hours worked.
SELECT DISTINCTROW [PSD AND PSO Information Table].[ID #], [PSD AND PSO Information Table].[LAST NAME], [PSD AND PSO Information Table].FIRST, [PSD AND PSO Information Table].[OT Restriction?], Sum([PSD and PSO OT Hours Table].HOURS) AS SumOfHOURS, [PSD AND PSO Information Table].[Seniority #]
FROM [PSD AND PSO Information Table] LEFT JOIN [PSD and PSO OT Hours Table] ON [PSD AND PSO Information Table].[ID #] = [PSD and PSO OT Hours Table].[ID #]
WHERE ((([PSD and PSO OT Hours Table].DATE)>=#10/1/2013# Or ([PSD and PSO OT Hours Table].DATE)=#1/1/2011#))
GROUP BY [PSD AND PSO Information Table].[ID #], [PSD AND PSO Information Table].[LAST NAME], [PSD AND PSO Information Table].FIRST, [PSD AND PSO Information Table].[OT Restriction?], [PSD AND PSO Information Table].[Seniority #]
ORDER BY [PSD AND PSO Information Table].[LAST NAME], Sum([PSD and PSO OT Hours Table].HOURS), [PSD AND PSO Information Table].[Seniority #];
Here are the tables that we use
OT Hours Table
ID # DATE OT Description HOURS PAID/CTO

PSD and PSO Information Table
CALSSIFICATION LAST NAME FIRST ID# SENIORITY # SENIORITY DATE OT RESTRICTION
AND PHONE # (WHICH WE NO LONGER USE)

I hope this helps as I don't know how to send the "structure" of the tables to you.
We have changed to a report that is based on name first, but that query was built after we switched to Microsoft Office 2010. It works the same way this one does though, it does not give us names for the people that do not have hours in the current quarter.

[Dal Jeanis]

1) Open a new query - create tab, query design.
2) Do not insert a table, just close the box
3) Switch to SQL view - home tab, views group, choose SQL
4) Paste in thefollowing SQL.

SELECT 
   First(T1.[LAST NAME]), 
   First(T1.[FIRST]), 
   T1.[ID #], 
   Sum(T2.HOURS) AS SumOfHOURS, 
   MAX(NZ(T2.DATE,#1/1/2010#)) As ThruDate
FROM 
   [PSD and PSO Information Table] AS T1
   LEFT JOIN 
   [PSD and PSO OT Hours Table] AS T2
ON T1.[ID #] = T2.[ID #]
WHERE ( ((T2.DATE) >= #10/1/2013# ) 
  OR    ((T2.DATE) = #1/1/2011# ) )
GROUP BY T1.[ID #]
ORDER BY First(T1.[LAST NAME]), First(T1.[FIRST]);

5) Run it and see if you have what you need. If so, save it.

First Lesson - you don't have to add every field to the "GROUP BY" in order to get fields to show up in the aggregate query. You usually should only only GROUP BY the items that you need to make the desired grouping. In this case, [ID #] is unique, so you don't need anything else.

Then, for the other items you want in the query, in the select and the order by you can use an aggregate function such as First, Last, Avg, Max, Min, and so on. Since the person's [LAST NAME] will be the same for all records, we generally use First() to accept the first value in the group.

Second Lesson - Avoid using reserved words and function names as the names of tables and fields. First, Date, Time, Name, Hours, and so on, can cause major headaches at the wrong moment, and also make it hard to read. Also avoid spaces and special characters, and avoid having table names that are several words long and don't give you any extra information anyway. As a suggestion, tblStaff and tblOvertime are simple, readable, and understandable names that would meet the need for your usage.

Third Lesson - if you put the word "code" in square braces [] before your SQL code and the word "/code" in square braces after your SQL code, without any quotes, then the forum won't squish your SQL all together and ruin your formatting. You can also use the words quote and /quote in square braces to quote someone else and make it obvious you're quoting them.

FYI, this is how I read the structure of your tables. If this is correct, then the above code should work fine.

PSD AND PSO OT Hours Table
   ID #
   DATE 
   OT Description 
   HOURS 
   PAID/CTO 

PSD and PSO Information Table
   CLASSIFICATION 
   LAST NAME 
   FIRST 
   ID# 
   SENIORITY # 
   SENIORITY DATE 
   OT RESTRICTION 
   PHONE #