Solved

NZ fuction does not work properly

Posted on 2008-10-21
16
520 Views
Last Modified: 2012-05-05
need your help!!!!!!!!
Every morning user must clock in in the system. Clock In time recorded as Time.
it looks like:
Name           Time
Person B      9/2/2008 6:38 AM
Person A      9/2/2008 8:10 AM
Person B      9/3/2008 6:41 AM
Of course, calendar month has weekends(WE), holidays(H), and also the person might be just off
When you are clock in- the status should be 1, when you are not clock in - you are off and the status  0
i can make the cross tab query and see the results

here is my table and the results of my query:
Name      Time                           Count      Day
Person A      9/2/2008 8:10:03 AM      1      02
Person B      9/2/2008 6:38:04 AM      1      02
Person B      9/3/2008 6:41:57 AM      1      03
Person A      9/3/2008 7:57:26 AM      1      03
Person B      9/4/2008 6:42:10 AM      1      04
Person A      9/4/2008 7:46:48 AM      1      04
Person B      9/5/2008 6:37:18 AM      1      05
On september 1 it was a  holiday, that is why there is no any records for the 01,
on september 05 the Person A was off, that is why his time not in the table
my query2:
SELECT Test.Name, Test.Time, Count(nz([ID],0)) AS Count, Format([Time],'dd') AS [Day]
FROM Test
GROUP BY Test.Name, Test.Time, Format([Time],'dd');

My goal to see the results in crosstab format:
TRANSFORM Count(Query2.Count) AS Count
SELECT Query2.Name, Count(Query2.Count) AS Total
FROM Query2
GROUP BY Query2.Name
PIVOT Format([Time],"dd");
that is the result:
Name      Total      02      03      04      05
Person A      19      1      1      1      
Person B      19      1      1      1      1

My query is not working, it gives me a blank instead of 0 for person A for 05,

also how to make to see also 01 with H value in
0
Comment
Question by:rfedorov
  • 9
  • 7
16 Comments
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22770111
Ok this fixes your 0 issue
I changed some the field names

Query2
 
SELECT Test.Name, Count(nz([Name],0)) AS Cnt, Format([Time],'dd') AS [Day]
FROM Test
GROUP BY Test.Name, Format([Time],'dd');
 
 
Query3
TRANSFORM nz(Count([Cnt]),0) AS [Count]
SELECT Query2.Name, Count(Query2.Cnt) AS Total
FROM Query2
GROUP BY Query2.Name
PIVOT Query2.Day;

Open in new window

0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22770774
Lets see, the holiday question.  I assume that you are pulling this report by a certain date range.  Do you have a table with the holiday dates listed?  if so then you could simply create a union query that pulls the holidays within your date range and tie those back to your [ID] (so each employee gets a holiday) and add it to the bottom of query2.  This gives you the data needed for the holidays.  With the attached querys you can also display the H instead of a 0 or (count of time enteries per person per day).

New Union Query
SELECT Test.Name, Count(nz([Name],0)) AS Cnt, Format([Time],'dd') AS [Day]
FROM Test
GROUP BY Test.Name, Format([Time],'dd')
UNION 
SELECT "Person B" AS Name, "H" AS cnt, Format([Holidaydate],'dd') AS [Day]
FROM Holidays;
 
Updated Query3
TRANSFORM nz(First([Cnt]),0) AS [Count]
SELECT Query2.Name, Count(Query2.Cnt) AS Total
FROM Query2
GROUP BY Query2.Name
PIVOT Query2.Day;

Open in new window

0
 

Author Comment

by:rfedorov
ID: 22770777
Works great,THANK YOU, the problem with Zero is over, how to make
appear missing dates, let say 01
0
Resolve Critical IT Incidents Fast

If your data, services or processes become compromised, your organization can suffer damage in just minutes and how fast you communicate during a major IT incident is everything. Learn how to immediately identify incidents & best practices to resolve them quickly and effectively.

 

Author Comment

by:rfedorov
ID: 22770922
masterjojobinks:
thank you for correcting the zero problem
I want to do * in the right order:
i assume that holiday table should be:
#  HolidayDate              Descrition  
1. September 01, 2008 Labor Day
2. September 29, 2008 Another holiday
please advise
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22771528
That is what I created to test the SQL.
So you could create following SQL

SELECT Test.Name, "H" AS cnt, Format([Holidaydate],'dd') AS [Day]
FROM Holidays, Test
GROUP BY Test.Name, "H", Format([Holidaydate],'dd');

and union it in place of what I posted earlier
0
 

Author Comment

by:rfedorov
ID: 22771779
GREAT!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
Name      Total      01      02      03      04      05      08
Person A      20      H      1      1      1      0      1
Person B      20      H      1      1      1      1      1
ZEROES, HOLIDAYS PROBLEMS DO NOT EXIST ANYMORE,
THE ONLY THING LEFT IS HOW TO SHOW THE REST OF THE MONTH :WEEKENDS
FOR O6 AND O7 SHOULD BE WE, PLEASE



0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22771931
No easy way to do this without a seperate table or creating a function to use the query.  I have to run now but can provide a solution tomorrow.
0
 

Author Comment

by:rfedorov
ID: 22772103
THANKS A LOT, LOOKING FORWARD TO HEAR FROM TOMORROW
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22775587
Ok, are you wanting to run function as part of the query, or can this function be run before you run the query(query3)?
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22776477
There are two ways to approach this problem.
First is to create a temporary table and populate the table with dates of the week end.  This requires that the function be run before the query, to get the results, is run.  Also the easiest to code.
Second is to create a function that can be impedeed into the query itself that creates the above table.  This is more difficult because the function must be run for every date already found and create the table only once.  The issue of deleting and recreating the table must also be dealt with.

So I did a combination of both.  The function requires a table to exists where the weekend dates can be added.  Each time the two querys below are run, the function addes the needed weekend dates the the table and then pulls that data into query2 and uses the query2 data for the cross tab query.  

You must add the begining and ending date you are running the query for to the function.

Query2
 
SELECT Test.Name, Count(nz([Name],0)) AS Cnt, Format([Time],'dd') AS [Day]
FROM Test
GROUP BY Test.Name, Format([Time],'dd'), AddWeekendDates(#9/1/08#,#9/30/08#)
UNION 
SELECT Test.Name, "H" AS cnt, Format([Holidaydate],'dd') AS [Day]
FROM Holidays, Test
GROUP BY Test.Name, "H", Format([Holidaydate],'dd')
UNION 
SELECT Test.Name, "W" AS Cnt, Format([WeekendDate],'dd') AS [Day]
FROM tblWeekendDates, Test
GROUP BY Test.Name, "W", Format([WeekendDate],'dd');
 
query 3
TRANSFORM nz(First([Cnt]),0) AS [Count]
SELECT Query2.Name, Count(Query2.Cnt) AS Total
FROM Query2
GROUP BY Query2.Name
PIVOT Query2.Day;
 
Function
-------------
Function AddWeekendDates(dteStart As Date, dteEnd As Date)
    
    Dim intCounter As Integer
    Dim strReadSQL As String
    Dim rsRead As DAO.Recordset
    Dim dteDate As Date
    
    For intCounter = 1 To dteEnd - dteStart
        dteDate = dteStart + intCounter
        'check to see if the date passed in plus 1 is a weekend
        If Weekday(dteDate) = 7 Or Weekday(dteDate) = 1 Then
            'check to see if the date exists in the table already
            strReadSQL = "Select tblWeekendDates.* " & _
                            "from tblWeekendDates " & _
                            "WHERE (((tblWeekendDates.WeekendDate)=#" & dteDate & "#));"
            Set rsRead = CurrentDb.OpenRecordset(strReadSQL)
                'if the date does not exist then add the date
                If rsRead.RecordCount = 0 Then
                    rsRead.AddNew
                        rsRead("WeekendDate") = dteDate
                    rsRead.Update
                End If
            rsRead.Close
        End If
    Next intCounter
End Function
---------

Open in new window

0
 

Author Comment

by:rfedorov
ID: 22778321
no words, everything looks GREAT!
tnanks a lot, really appreciate it!!!!!!!!!!!!!

deserve 10 times more!
if you will respond to my another queestion, the same stuff, you will get another 500 points
but i would like to ask you,  I am not fast like you, sloooooooooow person...
what about if the user will work on holidays or weekends???
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22778444
Using what has been posted if a user works on a holiday it will count both.  a small change to the part of query2 will stop that from happening.  The same thing for weekends as well.  Do you want to count it twice?
0
 
LVL 8

Expert Comment

by:Joe Overman
ID: 22778563
In order to stop this from counting twice you must exclude the holiday records where a user has time for a given day, The same for weekends.  

I assume that your data is more complex than the test data you have listed.

What it means is you would have a couple more nested querys to eliminate the unneeded data and then when you have what you want union the data together into query2 for use by query3.
0
 

Author Comment

by:rfedorov
ID: 22778580
No, it should override, let say if the user was woking on saturday or holiday it shold shows 1 only,

thanks again
0
 
LVL 8

Accepted Solution

by:
Joe Overman earned 500 total points
ID: 22778756
Here you go 2 new queries  and an update query2

query3 and the function remain the same.

Should get you want you want.  

Keep in mind, this assumes you are not going to cross months when you run the date range and it only counts a person once per day, even if there are more than on log-ins per person per day.
Query2_1
SELECT Test.Name, "H" AS cnt, Format([Holidaydate],'dd') AS [Day]
FROM Holidays, Test
GROUP BY Test.Name, "H", Format([Holidaydate],'dd');
 
Query2_2
SELECT Test.Name, "W" AS Cnt, Format([WeekendDate],'dd') AS [Day]
FROM tblWeekendDates, Test
GROUP BY Test.Name, "W", Format([WeekendDate],'dd');
 
Query2
SELECT Test.Name, Count(nz([Name],0)) AS Cnt, Format([Time],'dd') AS [Day]
FROM Test
GROUP BY Test.Name, Format([Time],'dd'), addweekenddates(#9/1/08#,#9/30/08#)
UNION 
SELECT Query2_1.Name, Query2_1.cnt, Query2_1.Day
FROM Query2_1 LEFT JOIN Test ON (Query2_1.Day = format(Test.Time,"DD")) AND (Query2_1.Name = Test.Name)
WHERE (((Test.Name) Is Null))
UNION 
SELECT Query2_2.Name, Query2_2.Cnt, Query2_2.Day
FROM Query2_2 LEFT JOIN Test ON (Query2_2.Day = format(Test.Time,"DD")) AND (Query2_2.Name = Test.Name)
WHERE (((Test.Name) Is Null));

Open in new window

0
 

Author Closing Comment

by:rfedorov
ID: 31508403
THANK YOU, BEST WISHES, GREAT HOLIDAY SEASON!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

685 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