Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old August 4th, 2003, 07:55 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default Crosstab Query

Hiya

I have a crosstab query, the query retrieves data from one table and looks at Date (Col) vs Name (Row). Each name does not have data for every date, and therefore some blanks are returned. How can I make these blanks appear as 0?

Thanks in advance
Ben
  #2 (permalink)  
Old August 4th, 2003, 11:36 AM
Authorized User
 
Join Date: Jun 2003
Location: , FL, USA.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Try...

Nz(Date, 0)

Kenny Alligood
  #3 (permalink)  
Old August 4th, 2003, 12:26 PM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I couldn't get this to work? The Date is the column field, I also tried the function with the value field, but this didn't work either... ...Is there something I'm doing wrong?
  #4 (permalink)  
Old August 4th, 2003, 03:24 PM
Authorized User
 
Join Date: Jun 2003
Location: , FL, USA.
Posts: 91
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Very simply (meaning you will have to incorporate this into your own Query) this is the syntax...

SELECT Name, Nz(Date,0) AS MyDate
FROM MyTable;

What this will do is add a 0 in the Date column if the field Is Null. I showed it to you in SQL but you can copy this into an Access Query - change the names to match your fields and tables - and see the result.

Hope this helps.....

Kenny Alligood
  #5 (permalink)  
Old August 5th, 2003, 04:10 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Kenny,

This still doesn't work. Maybe I explained myself incorrectly... ...I have a table which contains names, dates, and data. I then query this table with a crosstab query, the crosstab query has the Name set as Row and the Date set as Column. The crosstab query displays every name in the table and every date (there are no gaps here), however as some names don't have attributable data on some dates, There are blanks in the values. I want these blanks to appear as 0. The query I have is as follows:

TRANSFORM Last(tblShiftStarts.[Start Time]) AS [LastOfStart Time]
SELECT agent.AGENT_NAME
FROM tblShiftStarts INNER JOIN agent ON tblShiftStarts.AGENT_ID = agent.AGENT_ID
GROUP BY agent.AGENT_NAME
ORDER BY agent.AGENT_NAME, tblShiftStarts.DATE
PIVOT tblShiftStarts.DATE;

I have tried the nz function on date, and it doesn't have the required effect, all the date column headers just appear as two unprintable characters.

I'd really appreciate it if anyone can offer any advice, I've checked other forums for similar questions and have got the same answer every time - people suggest to use the nz function, but no one can get it to work???

Thanks in advance
Ben
  #6 (permalink)  
Old August 5th, 2003, 08:23 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi Ben,

Kenny is pointing you in the right direction. You just need to send the Nz funtion the correct argument, which in this case is your TRANSFORM statement. Here's what you need:

TRANSFORM Nz(Last(tblShiftStarts.START_TIME),0) AS LastOfSTART_TIME
SELECT agent.AGENT_NAME
FROM agent INNER JOIN tblShiftStarts ON agent.AGENT_ID = tblShiftStarts.AGENT_ID
GROUP BY agent.AGENT_NAME
ORDER BY agent.AGENT_NAME, tblShiftStarts.DATE
PIVOT tblShiftStarts.DATE;

HTH,

Bob
  #7 (permalink)  
Old August 5th, 2003, 08:30 AM
Ben Ben is offline
Authorized User
 
Join Date: Jun 2003
Location: , , United Kingdom.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Sweeeeet Thanks Bob
  #8 (permalink)  
Old November 22nd, 2004, 06:08 AM
Registered User
 
Join Date: Nov 2004
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi All,

This topic did solve 50% of my problem; hopefully I can solve the rest with your help.

I created a crosstab query that counts the number of records, and distribute the results, based on a time stamp, over the 4 quarters. When there is now result for i.e. Q2, the Nz function display's a "0". Perfect so far..!

But... when there are no results at all, the result of the crosstab query is completely empty. I want four "0"'s in that case.

Please advice!


With regards,
Eric



Similar Threads
Thread Thread Starter Forum Replies Last Post
crosstab query and parameter stoneman Access 10 January 6th, 2006 04:45 PM
crosstab query and asp tulincim Classic ASP Databases 0 September 13th, 2005 03:00 PM
Crosstab query with no results edubbelaar Access 0 November 22nd, 2004 06:12 AM
Crosstab query Berni016x SQL Server 2000 2 September 17th, 2004 10:43 AM
Crosstab query vladimir Access 1 December 1st, 2003 04:02 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.