A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Setting Up & Running Reports
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Strange Report display problem - XP and Vista - Access 2003



 
 
Thread Tools Display Modes
  #1  
Old April 19th, 2010, 08:00 AM posted to microsoft.public.access.reports
Kash
external usenet poster
 
Posts: 11
Default Strange Report display problem - XP and Vista - Access 2003

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.p...ey=0nxqwntmnmd

XP: http://www.mediafire.com/imageview.p...ey=gdazznyyom5
  #2  
Old April 19th, 2010, 01:41 PM posted to microsoft.public.access.reports
John Spencer
external usenet poster
 
Posts: 7,815
Default Strange Report display problem - XP and Vista - Access 2003

Try adding quote marks around all those STRINGS in the Pivot clause.

('4/1/2010','4/2/2010','4/3/2010','4/4/2010','4/5/2010','4/6/2010','4/7/2010',
...., '4/29/2010','4/30/2010');

Other than that I am not seeing anything else that looks abnormal with the
query. Well, other than why the Count is being "named" AvgOfkeyMasterID.

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/29/2010','4/30/2010');

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Kash wrote:
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.p...ey=0nxqwntmnmd

XP: http://www.mediafire.com/imageview.p...ey=gdazznyyom5

  #3  
Old April 19th, 2010, 03:16 PM posted to microsoft.public.access.reports
Duane Hookom
external usenet poster
 
Posts: 7,177
Default Strange Report display problem - XP and Vista - Access 2003

Check my reply to this same question in Tek-Tips
http://www.tek-tips.com/viewthread.c...1600329&page=1.
--
Duane Hookom
Microsoft Access MVP


"John Spencer" wrote:

Try adding quote marks around all those STRINGS in the Pivot clause.

('4/1/2010','4/2/2010','4/3/2010','4/4/2010','4/5/2010','4/6/2010','4/7/2010',
...., '4/29/2010','4/30/2010');

Other than that I am not seeing anything else that looks abnormal with the
query. Well, other than why the Count is being "named" AvgOfkeyMasterID.

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/29/2010','4/30/2010');

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Kash wrote:
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.p...ey=0nxqwntmnmd

XP: http://www.mediafire.com/imageview.p...ey=gdazznyyom5

.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:02 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.