Strange Report display problem - XP and Vista - Access 2003

[kashif2005]

Hi,

I have a strange problem in displaying of reports on Windows XP and Vista. My crosstab query display mission records for a particular month, for employees of my organisation. For example if I search by month of "April", the crosstab query will display all staff ( of different departments) who were on mission in April. On Windows Vista the report displays fine, that is the query only fetches records for APRIL. However on an XP system, apart from APRIL records, the report is repeating staff member names along with dates of arrival and exit of previous months. I have really failed to understand what is causing this problem Please see the attached pictures 'XP.jpg' and 'Vista.jpg' for better understanding.

Here's my crosstab query:

TRANSFORM Count(tblMaster_TEMP.keyMasterID) AS AvgOfkeyMasterID
SELECT tblMaster_TEMP.[Mission Status] AS Msn, (tblMaster_TEMP.OrgName) AS Agency, tblMaster_TEMP.FullName AS [Staff Member Name], tblMaster_TEMP.Location, tblMaster_TEMP.[Mission Start] AS [Arrival Date], tblMaster_TEMP.[Date of Exit] AS [Exit Date]
FROM tblMaster_TEMP
WHERE (((tblMaster_TEMP.[Date of Arrival]) Between #4/1/2010# And #4/30/2010# AND tblMaster_TEMP.Location = 'ISB' AND tblMaster_TEMP.[Mission Status] IN ('E' , 'P', 'S') ))
GROUP BY tblMaster_TEMP.OrgName, tblMaster_TEMP.[Mission Status], tblMaster_TEMP.FullName, tblMaster_TEMP.Location, tblMaster_TEMP.[Mission Start], tblMaster_TEMP.[Date of Exit]
PIVOT Format([Date of Arrival],'Short Date') In (4/1/2010,4/2/2010,4/3/2010,4/4/2010,4/5/2010,4/6/2010,4/7/2010,
4/8/2010,4/9/2010,4/10/2010,4/11/2010,4/12/2010,4/13/2010,
4/14/2010,4/15/2010,4/16/2010,4/17/2010,4/18/2010,4/19/2010,
4/20/2010,4/21/2010,4/22/2010,4/23/2010,4/24/2010,4/25/2010,
4/26/2010,4/27/2010,4/28/2010,4/29/2010,4/30/2010);

I'll be grateful if someone can help me. Many thanks.

URL for images:
Vista:

http://www.mediafire.com/imageview.php?quickkey=0nxqwntmnmd

XP:

http://www.mediafire.com/imageview.php?quickkey=gdazznyyom5

 

[dhookom]

I'm not sure how you handle months other than April of 2010. There is an alternative crosstab report solution in the faqs faq703-5466 that might be much easier to implement. It uses months as column headings but could easily be changed to days.

I don't know why you have [Mission Start], [Date of Exit] in the GROUP BY. This would create one record in the output for each unique pair of these values.

At a minimum, I would change the PIVOT to generate strings:

PIVOT "d" & Format([Date of Arrival],'dd') In ("01","02","03","04","05",..."30");

Duane
Hook'D on Access
MS Access MVP

 

[kashif2005]

Thxs Duane for your reply. users select month & year from a combo box,based on which the report is generated for a particular month/year.
I have tried to remove [Mission Start], [Date of Exit] in the GROUP BY clause of my query but I'm getting an error message "you triedto execute a query that does not include the specified function 'exit date' as part of an aggregate function". ..I don;t know what this error means??

secondly as said earlier the query & report works fine in VISTA system with Acces 2003. The problem is with XP systems where a query on e.g. 'April 2010' will display staff names, their arrival & exit dates from previous months also. I hope you saw the attached pictures in my post...I'm really stuck, as to what could be the problem

 

[dhookom]

I'm suggesting that I am surprised it worked in any version. If it did, it was because the version of Access resolved all of the issues.

You need to remove [Mission Start], [Date of Exit] from the SELECT clause also. I don't think you want these in the Row Headings.

IMO, use the link I suggested as it doesn't involve any code and should easily work in all versions.

Duane
Hook'D on Access
MS Access MVP