asked on

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

PortletPaul

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.

HOTWATT

ASKER

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");

HOTWATT

ASKER

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.

ASKER CERTIFIED SOLUTION

PortletPaul

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

HOTWATT

ASKER

Thanks