Solved

Need to modify the existing query to get results i want. Looks complicated

Posted on 2008-10-15
6
240 Views
Last Modified: 2011-10-19
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).
When you are clock in- the status should be 1, when you are not clock in - you are off and the status  0
I have two attachnemtes:
Excel file and Database in access:

So i want to present the data for a whole calendar month, from 1 to 31
you can see in the attached Excel file how it should be

                01           02               03       04           05
Person A   H             1                  1           1         0
Person B   H             1                   1           1          1
which means for 01 it was a holiday, it means nobody clocked in, that is why this is H
for 02,03,04 05 1 stands for working, 0 for off,
so the person A was off only on 05 and Person B was here whole week but holiday




Action.xls
DBtest.mdb
0
Comment
Question by:rfedorov
  • 3
  • 2
6 Comments
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
And?
0
 

Author Comment

by:rfedorov
Comment Utility
Hi, what do you mean?
0
 
LVL 44

Expert Comment

by:GRayL
Comment Utility
For me, a well-worded question is one that can stand on its own.  The data serves only to show what the answer should be.  I just get tired of having to download data to find out what the question is about.  'She who must be obeyed'  is about to tear the door down looking for dinner - later my friend.
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:rfedorov
Comment Utility
To GRayL:
I am really sorry if i did something wrong to You. Somethimes is is hard (for me) to make a  perfect question. In this case it is really complicated. at least for me. That is why in my excel file you can see exactly what i need and h ow the results should be present.
I want to see the attendance on daily base for each day in the month, from 1 to 31 even if it was no action, like saturday, holiday or something else
My peace of data represent only if the person did clock in and the status is 1, this is only the data i can get from the database, you can see blanks in query because i can not make zeroes, i am using the function NZ but it did not work for me.
I will answer on all your questions if you have
0
 
LVL 8

Accepted Solution

by:
masterjojobinks earned 500 total points
Comment Utility
0
 

Author Closing Comment

by:rfedorov
Comment Utility
No words, great job, clear, well explained!
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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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 …
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…

728 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

11 Experts available now in Live!

Get 1:1 Help Now