Crosstab Query with Complete list of dates 1

[Noel2]

Here is my situation:

I have an Access 97 database where I track technicians time on work orders through a table, this would include gl codes, hours spent on jobs, tech names, and dates.

I have created a crosstab query as follows:

PARAMETERS [Forms]![tmsht_dial_bx]![beg_date] DateTime, [Forms]![tmsht_dial_bx]![end_date] DateTime;
TRANSFORM Sum(Nz([daily].[reg_hrs])+Nz([daily].[ot_hrs])+Nz([daily].[dt_hrs])) AS hrs
SELECT tch_tbl.emp_num AS [employee no], tch_tbl.technm AS technician, daily.gl_cde AS [gl code]
FROM tch_tbl INNER JOIN daily ON tch_tbl.techid = daily.techid
WHERE (((daily.dt) Between [Forms]![tmsht_dial_bx]![beg_date] And [Forms]![tmsht_dial_bx]![end_date]))
GROUP BY tch_tbl.emp_num, tch_tbl.technm, daily.gl_cde
PIVOT daily.dt;

The parameters are based on a Form that I have where I can run the query between two dates. The problem I have is that when I run the query I only retrieve dates that are fixed to the times when the technician's work. I need to also include the weekends on my payroll report. How do I include dates in my query that don't have time attached to it? Please let me know if you have any ideas.

Thanks
Noel

 

[coco86]

You could add some code to the event that opens the query that would populate a temporary table with all the dates from beginningdate to enddate and then modify your final SQL line to this:

PIVOT daily.dt in (Select DateField from TempTable);

 

[Noel2]

Coco,

This looks like it will do what I need. I am still a baby at this and probably need the code to create the SQL behind creating that temporary table. Can you please help me with this. I know that I can do the rest with opening the query and then revising the crosstab query with the adjusted PIVOT statement. Sorry to be a pain.

Thanks!
Noel

 

[coco86]

Well, there are more complicated ways of doing it but the following code is easy and it works:

Dim i As Date
    
CurrentDb.Execute "Delete * from tempdatetable"
    
For i =  [Forms]![tmsht_dial_bx]![beg_date] To [Forms]![tmsht_dial_bx]![end_date]
    CurrentDb.Execute "insert into tempdatetable (daterange) values (#" & _
                       Format(i, "mm/dd/yy") & "#)"
Next i

tempdatetable = the name of the temporary table
daterange = the name of the field in the table

 

[Noel2]

This looks like it may be my answer. Let me try it out and I will let you know what I find out.

Thanks again!
Noel

 

[Noel2]

alrighty... here is the finished query:

PARAMETERS [Forms]![tmsht_dial_bx]![beg_date] DateTime, [Forms]![tmsht_dial_bx]![end_date] DateTime;
TRANSFORM Sum(Nz([daily].[reg_hrs])+Nz([daily].[ot_hrs])+Nz([daily].[dt_hrs])) AS hrs
SELECT tch_tbl.emp_num AS [employee no], tch_tbl.technm AS technician, daily.gl_cde AS [gl code]
FROM tch_tbl INNER JOIN daily ON tch_tbl.techid = daily.techid
WHERE (((daily.dt) Between [Forms]![tmsht_dial_bx]![beg_date] And [Forms]![tmsht_dial_bx]![end_date]))
GROUP BY tch_tbl.emp_num, tch_tbl.technm, daily.gl_cde
PIVOT daily.dt in (Select tempdatetable.daterange from tempdatetable);

The code ran terrificly to setup all of the dates in the tempdatetable. The only problem that I am having now is that I am getting this error when trying to run the query:

This expression is typed incorrectly, or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables.

Do you have any words of advise or maybe where I have gone wrong? Please let me know.

Thanks!
Noel

 

[coco86]

I've done this before but it's been a long time and I can't for the life of me get it to work now. I have another idea, though. Try this:

Add your tempdatetable to the original query
Create a right join between tempdatetable and daily so that all records from tempdatetable are included

Change the last line to

PIVOT tempdatetable.daterange

I think that should work for getting your column headings but you're going to end up with an extra row whose row heading is null.

I wish I could remember how I got the other approach to work

 

[Noel2]

This has gotten this to work except for that row which the row heading is null. Is there anyway to get all of the null values in that row to change to zero. This would assist me in my efforts to push this out to my report. Please let me know if you have any ideas or remember how to get it working without the null value. I have a feeling that we are almost there. You are definitely getting a star for helping me out so much.

thanks!
Noel

 

[coco86]

Ok, two steps...

Step one, Change

TRANSFORM Sum(Nz([daily].[reg_hrs])+Nz([daily].[ot_hrs])+Nz([daily].[dt_hrs])) AS hrs

to

TRANSFORM nz(Sum(Nz([daily].[reg_hrs])+Nz([daily].[ot_hrs])+Nz([daily].[dt_hrs])),0) AS hrs

Second create a second query that is the actual report query

Select * from crosstabquery where (((rownamefield) Is Null))

That ought to return the data you want. However, I see a potential problem with your report. If the field names are variable (i.e., the dates), how is the report going to know what they are?

There are ways around this problem but it's going to take some coding...

 

[Noel2]

Hey Coco,

The report is good to go, maybe you can work with this a little better if you actually were looking at the file itself. Would you mind if I e-mailed it to you so you could get a better grasp? Please let me know. There are some null values that I need to have left as null. I just need to hide that last null record in the query. Please let me know your e-mail and I will send the file to you.

Thanks!
Noel

 

[coco86]

If that's what you want, then all you need to do is the second step and not the first.

If you're still having problems after that, let me know and I'll take a look. I'll need to do it tonight when I get home, though.

 

[Noel2]

The problem you assessed is definitely going to be a problem due to the fact that the dates are variable and you would need to create a new query each and every time that you ran this report. Time is not really that big of a concern. If you checked it out tonight that would be cool. Plus all of this will be able to show you all of the code that I have placed into the report to get the dynamic column headings and how all of that is working. If you have a problem giving e-mail address over forums, you can send it to me at noelbaggett@cox.net and I will send the file to you.

Thanks!
Noel

 

[Noel2]

Coco,

I appreciate the assistance. For all of those who were wondering we finally got it completely figured out. What it turned out to be was creating a query in the open event of my report to eliminate the null value after the crosstab query had already inputed the extra dates. I have put the code and SQL below:

For the crosstab query:

TRANSFORM Sum(Nz([reg_hrs])+Nz([ot_hrs])+Nz([dt_hrs])) AS Expr1
SELECT qrysmpl_up_tmsht.emp_num AS [employee no], qrysmpl_up_tmsht.technm AS technician, qrysmpl_up_tmsht.gl_cde AS [gl code]
FROM tempdatetable LEFT JOIN qrysmpl_up_tmsht ON tempdatetable.daterange = qrysmpl_up_tmsht.dt
GROUP BY qrysmpl_up_tmsht.emp_num, qrysmpl_up_tmsht.technm, qrysmpl_up_tmsht.gl_cde
ORDER BY qrysmpl_up_tmsht.technm DESC
PIVOT tempdatetable.daterange;

Private Sub Command5_Click()

Dim i As Date

CurrentDb.Execute "Delete * from tempdatetable"

For i = [Forms]![tmsht_dial_bx]![beg_date] To [Forms]![tmsht_dial_bx]![end_date]
CurrentDb.Execute "insert into tempdatetable (daterange) values (#" & Format(i, "mm/dd/yy&quot & "#)"
Next i

DoCmd.OpenReport "crosstb", acViewPreview

End Sub

To get everything finally working the way that I wanted it to with all of the dates, I did just as you said by creating that select query. I have my code create a temporary query and then delete it after the query is completed. Below is the code that I use on the Open of the report:

Private Sub Report_Open(Cancel As Integer)

' Create underlying recordset for report.

Dim intX As Integer
Dim qdf As QueryDef
Dim astro As QueryDef
' Dim frm As Form

' Set database variable to current database.
Set dbsReport = CurrentDb
' Set frm = Forms![tmsht_dial_bx]
' Open QueryDef object.
Set astro = dbsReport.CreateQueryDef("tmp_qry", "SELECT qrytmsht_rpt.* FROM qrytmsht_rpt WHERE (((qrytmsht_rpt.[employee no]) Is Not Null));&quot
Set qdf = dbsReport.QueryDefs("tmp_qry&quot

' Open Recordset object.
Set rstReport = qdf.OpenRecordset()

' Set a variable to hold number of columns in crosstab query.
intColumnCount = rstReport.Fields.Count

' Delete temporary query
dbsReport.QueryDefs.Delete ("tmp_qry&quot

End Sub


Thanks!
Noel