Cross Tab Query (blank value show X)

I have a query that shows employee attendance. I created a cross tab query for this. The cross tab query shows the names of employees on the rows and the dates as the column headings. In the columns it show Present if atx_time_amount >0 and X if it is < 0. Only thing I realized is the atx_time_amount field will never be a negative number. It will always have a positive number or be blank if they are absent. So I am trying to figure out how to make it so the report shows present for atx_time_amount > 0 and X for a blank field. I added an attachment so you can see what I am working with.

THANKS!
Query.PNG
HOTWATTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Can you get the SQL for that query and paste it into a comment please.

there is a function in Access for this NZ() which you use, using SQL makes this easy to introduce.
BUT you cannot mix numbers and characters in a single column unless you convert ALL the numbers to characters as well. That means you lose formatting and the ability to add-up numbers.

So, to display an "X" instead of NULL may be a bad idea.
0
HOTWATTAuthor Commented:
ORIGINAL QUERY
SELECT EM.EM_KEY, ATX.ATX_DATE, ATX.ATX_DPKEY, ATX.ATX_TMKEY, ATX.ATX_CLOCK_IN, ATX.ATX_CLOCK_OUT, ATX.ATX_TIME_AMOUNT, EM.EM_FIRST_NAME, EM.EM_LAST_NAME, EM.EM_DPKEY, EM.EM_TMKEY, IIf([atx_time_amount]>0,"present","X") AS ATTENDANCE
FROM EM INNER JOIN ATX ON EM.EM_KEY = ATX.ATX_EMKEY
WHERE (((EM.EM_KEY)<>"000000") AND ((ATX.ATX_DATE) Between [Forms]![frmEmployeeATLX]![dfsBegDate] And [Forms]![frmEmployeeATLX]![dfsEndDate]) AND ((EM.EM_DPKEY)<>"MFG"));


CROSS TAB QUERY
PARAMETERS [Forms]![frmEmployeeATLX]![dfsBegDate] DateTime, [Forms]![frmEmployeeATLX]![dfsEndDate] DateTime;
TRANSFORM Last(qryEmpAttendance.ATTENDANCE) AS LastOfATTENDANCE
SELECT qryEmpAttendance.EM_FIRST_NAME, qryEmpAttendance.EM_LAST_NAME, qryEmpAttendance.EM_DPKEY, Count(qryEmpAttendance.ATTENDANCE) AS [Total Of ATTENDANCE]
FROM qryEmpAttendance
GROUP BY qryEmpAttendance.EM_FIRST_NAME, qryEmpAttendance.EM_LAST_NAME, qryEmpAttendance.EM_DPKEY
PIVOT Format([ATX_DATE],"Short Date");
0
HOTWATTAuthor Commented:
I added an example of how my cross column query looks now and what I was hoping to make it look like. Where the field is blank there would be an X. Are you saying that is not possible? I do not need to add up any numbers. This is more  of a visual report.

Also is there a way to pull this query into a report since the amount of days depends on a criteria?  Not sure how that would work in a report.
Example.PNG
0
PortletPaulfreelancerCommented:
It's been a very long while since I used Access but I believe you will finad that it is Count(qryEmpAttendance.ATTENDANCE) that generates the numbers (or NULLs), so convert that column to a string using STR() and then output 'X' when the string is a null using NZ(). It may be possible to just use NZ() by itself, but I refer to be explicit.

PARAMETERS [Forms]![frmEmployeeATLX]![dfsBegDate] DateTime, [Forms]![frmEmployeeATLX]![dfsEndDate] DateTime;
TRANSFORM Last(qryEmpAttendance.ATTENDANCE) AS LastOfATTENDANCE
SELECT qryEmpAttendance.EM_FIRST_NAME, qryEmpAttendance.EM_LAST_NAME, qryEmpAttendance.EM_DPKEY, Nz(STR(Count(qryEmpAttendance.ATTENDANCE)),'X') AS [Total Of ATTENDANCE]
FROM qryEmpAttendance
GROUP BY qryEmpAttendance.EM_FIRST_NAME, qryEmpAttendance.EM_LAST_NAME, qryEmpAttendance.EM_DPKEY
PIVOT Format([ATX_DATE],"Short Date");
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HOTWATTAuthor Commented:
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.