Hi, I have a crosstab query which feeds into my report. I'm keeping track of leave requests for one of my office locations. The staff have leave for lets say 10 days of a month from 10 Feb (Date of Dep ) - 20 Feb (Date of Arrival ). These 10 days are displayed as 1's in my query & report. What I want is to display a '0' at the last day of leave, in this case on 20th Feb, their date of arrival. Can anyone let me know please how to do it. Below is my crosstab query : -
TRANSFORM Nz(Count(tblMaster_TEMP.keyMasterID),0) AS AvgOfkeyMasterID
-
SELECT tblMaster_TEMP.[Mission Status] AS Msn, (tblMaster_TEMP.OrgName) AS Department, tblMaster_TEMP.FullName AS [Staff Name], tblMaster_TEMP.Location, tblMaster_TEMP.[Date of Departure] AS [Start of Leave], tblMaster_TEMP.[Date of Arrival] AS [End of Leave]
-
FROM tblMaster_TEMP
-
WHERE (((tblMaster_TEMP.[Date of Departure]) Between #2/1/2010# And #2/28/2010# And tblMaster_TEMP.Location='Islamabad'))
-
GROUP BY tblMaster_TEMP.OrgName, tblMaster_TEMP.[Mission Status], tblMaster_TEMP.FullName, tblMaster_TEMP.Location, tblMaster_TEMP.[Date of Departure], tblMaster_TEMP.[Date of Arrival]
-
PIVOT Format([Date of Departure],'Short Date') In (2/1/2010,2/2/2010,2/3/2010,2/4/2010,2/5/2010,2/6/2010,2/7/2010,
-
2/8/2010,2/9/2010,2/10/2010,2/11/2010,2/12/2010,2/13/2010,
-
2/14/2010,2/15/2010,2/16/2010,2/17/2010,2/18/2010,
-
2/19/2010,2/20/2010,2/21/2010,2/22/2010,2/23/2010,
-
2/24/2010,2/25/2010,2/26/2010,2/27/2010,2/28/2010);
-
Many thanks for helping me out.
2 1409
For each Employee, assuming the From and To Dates are displayed in a Linear fashion, then you can create a Calculated Field that will display a Zero (0) where they both are equal, and 1 where they are not, as in: - SELECT tbl_employees_TEMP.FullName, tbl_employees_TEMP.LeaveDateFrom, _
-
tbl_employees_TEMP.LeaveDateTo, tbl_employees_TEMP.Status, _
-
IIf([LeaveDateFrom]=[LeaveDateTo],0,1) AS LCode _
-
FROM tbl_employees_TEMP;
P.S. - Sorry, no time for Column Formatting! - FullName LeaveDateFrom LeaveDateTo Status LCode
-
Fred 11/22/2009 11/30/2009 1 1
-
Fred 11/23/2009 11/30/2009 1 1
-
Fred 11/24/2009 11/30/2009 1 1
-
Fred 11/25/2009 11/30/2009 1 1
-
Fred 11/26/2009 11/30/2009 1 1
-
Fred 11/27/2009 11/30/2009 1 1
-
Fred 11/28/2009 11/30/2009 1 1
-
Fred 11/29/2009 11/30/2009 1 1
-
Fred 11/30/2009 11/30/2009 1 0
-
Barney 10/4/2009 10/10/2009 1 1
-
Barney 10/5/2009 10/10/2009 1 1
-
Barney 10/6/2009 10/10/2009 1 1
-
Barney 10/7/2009 10/10/2009 1 1
-
Barney 10/8/2009 10/10/2009 1 1
-
Barney 10/9/2009 10/10/2009 1 1
-
Barney 10/10/2009 10/10/2009 1 0
-
Wilma 11/12/2009 11/22/2009 1 1
-
Wilma 11/13/2009 11/22/2009 1 1
-
Wilma 11/14/2009 11/22/2009 1 1
-
Wilma 11/15/2009 11/22/2009 1 1
-
Wilma 11/16/2009 11/22/2009 1 1
-
Wilma 11/17/2009 11/22/2009 1 1
-
Wilma 11/18/2009 11/22/2009 1 1
-
Wilma 11/19/2009 11/22/2009 1 1
-
Wilma 11/20/2009 11/22/2009 1 1
-
Wilma 11/21/2009 11/22/2009 1 1
-
Wilma 11/22/2009 11/22/2009 1 0
-
Betty 10/19/2009 11/2/2009 1 1
-
Betty 10/20/2009 11/2/2009 1 1
-
Betty 10/21/2009 11/2/2009 1 1
-
Betty 10/22/2009 11/2/2009 1 1
-
Betty 10/23/2009 11/2/2009 1 1
-
Betty 10/24/2009 11/2/2009 1 1
-
Betty 10/25/2009 11/2/2009 1 1
-
Betty 10/26/2009 11/2/2009 1 1
-
Betty 10/27/2009 11/2/2009 1 1
-
Betty 10/28/2009 11/2/2009 1 1
-
Betty 10/29/2009 11/2/2009 1 1
-
Betty 10/30/2009 11/2/2009 1 1
-
Betty 10/31/2009 11/2/2009 1 1
-
Betty 11/1/2009 11/2/2009 1 1
-
Betty 11/2/2009 11/2/2009 1 0
-
Julie 11/17/2009 11/20/2009 1 1
-
Julie 11/18/2009 11/20/2009 1 1
-
Julie 11/19/2009 11/20/2009 1 1
-
Julie 11/20/2009 11/20/2009 1 0
-
Bam-Bam 12/1/2009 12/10/2009 1 1
-
Bam-Bam 12/2/2009 12/10/2009 1 1
-
Bam-Bam 12/3/2009 12/10/2009 1 1
-
Bam-Bam 12/4/2009 12/10/2009 1 1
-
Bam-Bam 12/5/2009 12/10/2009 1 1
-
Bam-Bam 12/6/2009 12/10/2009 1 1
-
Bam-Bam 12/7/2009 12/10/2009 1 1
-
Bam-Bam 12/8/2009 12/10/2009 1 1
-
Bam-Bam 12/9/2009 12/10/2009 1 1
-
Bam-Bam 12/10/2009 12/10/2009 1 0
-
Aziz Khan 10/12/2009 10/15/2009 1 1
-
Aziz Khan 10/13/2009 10/15/2009 1 1
-
Aziz Khan 10/14/2009 10/15/2009 1 1
-
Aziz Khan 10/15/2009 10/15/2009 1 0
-
Rubik 10/29/2009 11/4/2009 1 1
-
Rubik 10/30/2009 11/4/2009 1 1
-
Rubik 10/31/2009 11/4/2009 1 1
-
Rubik 11/1/2009 11/4/2009 1 1
-
Rubik 11/2/2009 11/4/2009 1 1
-
Rubik 11/3/2009 11/4/2009 1 1
-
Rubik 11/4/2009 11/4/2009 1 0
-
Bam-Bam 11/4/2009 11/23/2009 1 1
-
Bam-Bam 11/5/2009 11/23/2009 1 1
-
Bam-Bam 11/6/2009 11/23/2009 1 1
-
Bam-Bam 11/7/2009 11/23/2009 1 1
-
Bam-Bam 11/8/2009 11/23/2009 1 1
-
Bam-Bam 11/9/2009 11/23/2009 1 1
-
Bam-Bam 11/10/2009 11/23/2009 1 1
-
Bam-Bam 11/11/2009 11/23/2009 1 1
-
Bam-Bam 11/12/2009 11/23/2009 1 1
-
Bam-Bam 11/13/2009 11/23/2009 1 1
-
Bam-Bam 11/14/2009 11/23/2009 1 1
-
Bam-Bam 11/15/2009 11/23/2009 1 1
-
Bam-Bam 11/16/2009 11/23/2009 1 1
-
Bam-Bam 11/17/2009 11/23/2009 1 1
-
Bam-Bam 11/18/2009 11/23/2009 1 1
-
Bam-Bam 11/19/2009 11/23/2009 1 1
-
Bam-Bam 11/20/2009 11/23/2009 1 1
-
Bam-Bam 11/21/2009 11/23/2009 1 1
-
Bam-Bam 11/22/2009 11/23/2009 1 1
-
Bam-Bam 11/23/2009 11/23/2009 1 0
Thxs ADezii. Your help is very much appreciated, again :). Best regards,
Kashif
Sign in to post your reply or Sign up for a free account.
Similar topics
by: MJ |
last post by:
Hi,
I'm hoping this is relatively easy.
I have a report based on a query - when you run the report, a form
opens up and you are prompted for a date range. These are combo boxes
(ie. January...
|
by: MJ |
last post by:
I'm not following... where do I put that?
I put a textbox on my report and put the following property for it:
=!!.Value
This displays 1 (for January 2003).
How do I get it to display...
|
by: Claudia Fong |
last post by:
Hi,
I want to know how should I do to display a long date format in a report
(designed in Ms-access) but it only shows July 29, 2005 instead of the
whole expression
Friday, July 29, 2005
...
|
by: bruce24444 |
last post by:
First of all I'm new to the forum and am working on my first database. So far I think I've done not too bad but have hit a stumbling block for which I'm not sure how to get around.
What I have is...
|
by: keithsimpson3973 |
last post by:
Does anyone know if it is possible to display the value of a date picker control on a vb6 form that the user selected on an access report? I am using vb6 to display a report selection and it has a...
|
by: bhavu10 |
last post by:
i got date parameter but it shows only 2007 records and not for other
year and would like to know where i have writte code wrong it shows
month only from 1/3/2007 to 2/4/2007 not able to figureout...
|
by: keithsimpson3973 |
last post by:
Does anyone know if it is possible to display the value of a date picker control on a vb6 form that the user selected on an access report? I am using vb6 to display a report selection and it has a...
|
by: alive84 |
last post by:
Hi there,
I have a two problems concerning option button values on a report and data report creator reports.
The situation:
I have three option value boxes two have 3 option and one has...
|
by: smorrison64 |
last post by:
I have a form that is coded to open a File Dialog boc to pick a picture to display on that particular item on a subform. My form is not based on query, but rather two separate tables (one primary,...
|
by: Nepenthen |
last post by:
Hello –
I am still new to Access and I am currently working on debugging some code. I have a form with the following:
Employee Name (combo with select all choice)
Task (combo with select all...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: CloudSolutions |
last post by:
Introduction:
For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |