Crosstab Between dates (1 Viewer)

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
Well the data query opens with the limiting txtboxes on the main rota but the crosstab just opens with a white screen.


I may run the office repair but not sure if that will make it worse or just give up for the day lol

I don't think there is anything wrong with your system, just your and my lack of expertise with crosstab queries.;)
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
See if you can fix the crosstab query. Not sure what was there initially :(
 

Attachments

  • Database1.zip
    214.3 KB · Views: 85

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
I don't think there is anything wrong with your system, just your and my lack of expertise with crosstab queries.;)


I have the same problem with crosstabs this is my first in years
thanks for your help I'll try the db guys solution again with my rota screen be back soon
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
I seem to recall that you have to do something fancy to get dynamic column headings?

I can change the values on the form and open the crosstab and I get the same data from when it worked, with Sep before Aug.?
 

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
Added the peramators both the way thedbguy and Isladogs suggested


Code:
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");


I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
 

isladogs

MVP / VIP
Local time
Today, 22:58
Joined
Jan 14, 2017
Messages
18,186
Actually that was the DB Guy's suggestion.
AFAIA I have never used parameters with a crosstab query.
Some forum members state they are required in any crosstab query. I disagree.
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
Yes, but if you change the form values, you still get the same data?

Added the peramators both the way thedbguy and Isladogs suggested


Code:
PARAMETERS Forms!frmRota.form.txtFrom DateTime, Forms!frmRota.form.txtTo DateTime;
TRANSFORM First(QryJobCrosstabData.Times) AS FirstOfTimes
SELECT QryJobCrosstabData.Employee
FROM QryJobCrosstabData
GROUP BY QryJobCrosstabData.Employee
ORDER BY Format([AssignedDate],"Short Date")
PIVOT Format([AssignedDate],"Short Date");


I still get a blank subform but it opens when I open the crosstab from the db window but it still shows all records
 

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
Well had no luck tried every way I can think of to add the limiting dates I've run out of idears
 

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
Yes, but if you change the form values, you still get the same data?


If it's opened without the form then it displays all data I had a look at your one could see where you made the change but as you said it mucks the date data up.


thanks
mick
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
I can get it to respect the form values now, but Aug still comes after Sep ?:banghead:
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
See if you can understand this one from HiTechCoach

I am sure I did something like this for the call logs in work.

I'll have a look tomorrow to see how I did it.
 

Attachments

  • RptCrossTab2k.mdb
    784 KB · Views: 80

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
See if you can understand this one from HiTechCoach

I am sure I did something like this for the call logs in work.

I'll have a look tomorrow to see how I did it.


That worked nicely might use tables with mine and create it manually.


tried useing the where like they did and thing still wouldn't open.


I might right a code solution as that I know would work just means another local table but was hoping to get this working.


thanks for all your help mick
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
OK, I found one of mine.

I used an actual form for the crosstab, not just a subform control.
Each of the controls is named vName and a digit, eg vName1, vName2


The form has no record source, that is created on Form open

Code:
Private Sub Form_Load()
Dim n As Integer
Dim fld As Variant
  
For Each fld In Me.Recordset.Fields
    Do
        With Me.Controls(n)
            If .ControlType = acTextBox And Left(.Name, 5) = "vName" Then
                .ControlSource = fld.Name
                .Controls(0).Caption = fld.Name
                 n = n + 1
                 Exit Do
              End If
           End With
           n = n + 1
      Loop
 Next
End Sub

Public Sub Form_Open(Cancel As Integer)
Dim strCaller As String, strSql As String, strQuery As String

strCaller = PersonList()

strQuery = "qryWeekCallSummary_Crosstab"

'strSQL = "PARAMETERS pCallSystem Text ( 255 );"
strSql = strSql & " TRANSFORM Sum([SumOfCallSecs]/86400) AS Duration"
strSql = strSql & " SELECT qryWeekCallSummary.WeekEndDate"
strSql = strSql & " FROM qryWeekCallSummary "
If Len(gCallSystem) > 0 Then
    strSql = strSql & " WHERE qryWeekCallSummary.CallSystem = '" & gCallSystem & "'"
End If
strSql = strSql & " GROUP BY qryWeekCallSummary.WeekEndDate"
strSql = strSql & " ORDER BY qryWeekCallSummary.WeekEndDate DESC"
strSql = strSql & " PIVOT qryWeekCallSummary.Caller IN (" & strCaller & ")"

'CurrentDb.QueryDefs(strQuery).Parameters("[pCallSystem]") = gCallSystem
CurrentDb.QueryDefs(strQuery).SQL = strSql
Me.RecordSource = strSql
End Sub

PersonList was just getting active users. In your case it would be your dates.
gCallSystem was either BT or the other one which I cannot remember now.:banghead:

So in fact mine is the opposite to yours as I have limited users and lots of dates, but that is a reasonably simple method to have dynamic crosstab headings.

HTH
 

Attachments

  • crosstabform.PNG
    crosstabform.PNG
    49.1 KB · Views: 176

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
I just found this which might be of interest he might share the code he uses if you ask nicely lol.


I didn't think of a coded solution I'll give it a try but got to write a time validation tonight that will complete my rota entry system.


thanks for your help mick
 

Gasman

Enthusiastic Amateur
Local time
Today, 22:58
Joined
Sep 21, 2011
Messages
14,048
I just found this which might be of interest he might share the code he uses if you ask nicely lol.


I didn't think of a coded solution I'll give it a try but got to write a time validation tonight that will complete my rota entry system.


thanks for your help mick

I think you have forgotten the link?
 

Dreamweaver

Well-known member
Local time
Today, 22:58
Joined
Nov 28, 2005
Messages
2,466
DONE IT you have to add both the Criteria and paramiters

Criteria = Between [Forms]![frmRota]![txtFrom] And [Forms]![frmRota]![txtTo]

paramiters
Forms!frmRota!txtFrom
Forms!frmRota!txtTo


You should note it don't like the brackets in the paramiters


Still having problems with the subform but I think It dosn't like an embeded query but it's working so nearly there lol


 

Attachments

  • 2019-09-02.png
    2019-09-02.png
    18.9 KB · Views: 176
Last edited:

Users who are viewing this thread

Top Bottom