xtab, date range and static columns where value is null

[jtkjames]

Hi all

Long time since I last posted in here!!!

I have spent the last few hours trying to find a solution to an issue I am having with an XTab query, but to no avail, so was wondering if anyone else could cast their eye over it to see if it is even possible (I am thinking it might not be).

I have an XTab based on a set of date range parms, and have it defined such that each day has a count of a unique ID in its own column, all good. However if there is no data for that day, the column doesn't appear.

My SQL:

PARAMETERS [forms]![frmRep1]![textFrom] Text ( 255 ), [forms]![frmRep1]![textTo] Text ( 255 );
TRANSFORM Count(qryGetValidCalls.callid) AS CountOfcallid
SELECT "Calls Offered" AS Assessment
FROM qryGetValidCalls
WHERE (((qryGetValidCalls.realcallstart) Between CDate([forms]![frmRep1]![textFrom]) And (1+CDate([Forms]![frmRep1]![textTo]))))
GROUP BY "Calls Offered"
PIVOT qryGetValidCalls.calldate;

Now I know you can statically define the column headers in the PIVOT clause, eg.

PIVOT qryGetValidCalls.calldate in ("01/01/2000", "02/01/2000")

and that would be great if the parms range was always the same distance, but this report will be run for a variety of ranges of days from just a week to month and all combinations inbetween.

I was thinking of doing some kind of subquery i.e.

PIVOT qryGetValidCalls.calldate in (SELECT ......)

but am not sure where to proceed from there.

Is what I am after possible? If so could anyone perchance point me in the right direction please? I've been agonising over this for hours, I've read Duane's article, many posts on this forum and others, but everyone else who has encountered XTab column issues wants to define theirs over a fixed range i.e. 12 months, not a dynamic one based on parms.

Thanks for taking the time to read this, looking forward to any responses that might appear!!!

All the best
James

 

[Pat Hartman]

For short fixed lists such as days of the week and months of the year, you can use the column headings property to ensure that there is always a complete set of columns even if there is no data for one or more of them.

For longer fixed sets, you can use an outer join to force all values to appear. Create a table (if you don't already have one) that includes all the values you want. Then join to that table using a left join.

When your list is variable, it becomes more of a problem. You will need to create the list on the fly and then join to it.

 

[jtkjames]

Thanks for the reply!

I think I understand - I need to make a one-field table with one record per day, and use this table to drive the columns in the Xtab.

This however prompts an additional question - is the only way to do this using VBA to create a table, or can I create some SQL to do it for me?

In pseudocode, something like:

PARAMETERS strDate: string
FOR varDate FROM cdate([forms]![frmRep1]![dateFrom]) TO (1 + cdate([forms]![frmRep1]![dateTo]))
SELECT varDate

I am thinking in VBA it would be quite inefficient:

DoCmd.RunSQL ("DELETE [myDate] FROM tblTemp")
  FOR varDate = cdate(Forms!frmRep1!dateFrom) TO (1+cdate(forms!frmRep1!dateTo))
    DoCmd.RunSQL ("INSERT INTO tblTemp ([myDate]) VALUES (" & varDate & ")")
  NEXT varDate

especially if the date range were quite large i.e. one year?

Thank you for your time in this!
James

 

[jtkjames]

additional - having tested it on my Core i5 laptop, creating a year in VBA as above takes only 1-2 seconds, something which my users may just have to live with, if there isn't a better way to generate that table?

Also, I am having a bit of trouble with my modified Xtab, I have tried the left join method but it still brings in only the blanks

PARAMETERS [forms]![frmRep1]![dateFrom] Text ( 255 ), [forms]![frmRep1]![dateTo] Text ( 255 );
TRANSFORM Count(qryGetValidCalls.callid) AS CountOfcallid
SELECT "Calls Offered" AS Assessment
FROM tblTemp LEFT JOIN qryGetValidCalls ON tblTemp.MyDate = qryGetValidCalls.calldate
WHERE (((qryGetValidCalls.realcallstart) Between CDate([forms]![frmRep1]![dateFrom]) And (1+CDate([Forms]![frmRep1]![dateTo]))))
GROUP BY "Calls Offered"
PIVOT tblTemp.mydate;