Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #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
Reply With Quote
  #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
Reply With Quote
  #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?
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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
Reply With Quote
  #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

Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
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

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


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



All times are GMT -4. The time now is 04:04 PM.


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