Solved

NZ fuction does not work properly

Posted on 2008-10-21
16
486 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:masterjojobinks
Comment Utility
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:masterjojobinks
Comment Utility
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
Comment Utility
Works great,THANK YOU, the problem with Zero is over, how to make
appear missing dates, let say 01
0
 

Author Comment

by:rfedorov
Comment Utility
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:masterjojobinks
Comment Utility
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
Comment Utility
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:masterjojobinks
Comment Utility
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
Comment Utility
THANKS A LOT, LOOKING FORWARD TO HEAR FROM TOMORROW
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 8

Expert Comment

by:masterjojobinks
Comment Utility
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:masterjojobinks
Comment Utility
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
Comment Utility
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:masterjojobinks
Comment Utility
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:masterjojobinks
Comment Utility
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
Comment Utility
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:
masterjojobinks earned 500 total points
Comment Utility
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
Comment Utility
THANK YOU, BEST WISHES, GREAT HOLIDAY SEASON!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now