Report based on cross tab query (1 Viewer)

[Samantha]

Good morning,

I have been working to create a solution to get our payroll from our time keeping app, build a report to review, and prepare the data for upload to our payroll processing company.

My specific questions today (or this moment):

1.) On the report when I replaced December dates with the payroll I am preparing to run Monday the column headings (Labels) of those fields did not update. I had to go in and manually update the captions. I feel like I shouldn't have built the query as a cross tab idk. I guess I should be able to put a date formula in the caption to get the right info there.

2.) Secondly - would love to get a grand total in each employee header with a grand total for all jobs they worked. Again, I attempted and broke it. Not sure what the heck I did. I can work around this and just build a separate report if impossible. Can I embed something like that in the footer of the report? Like grand total for each employee and grand total for each job?

3.) Is conditional formatting possible for the field [JobCode] I need based on a wild card so any project containing "CI" gets highlighted or bolded/I tried and searches that I completed all lead to no not possible - but some of those were outdated so I figured It wouldn't hurt to ask.

Thanks for reading!

 

[Pat Hartman]

1) There are a couple of ways to make variable header crosstab reports. Here's one. The documentation is in the second file.
2) Reports are sequential processes. You can't print something that doesn't yet exist. To solve this problem, you need a second query that sums the detail by employee. Then you can join the crosstab to the totals query and you will have the total for each employee on every row so it will be available in the employee header.
3) Right([JobCode],2)="CI" or InStr([JobCode],"CI")>0 will work.

 

[MajP]

This is how I did it.
I named the day labels L1, L2, ...L6
I named the day textboxes D1, D2, .... D6
This assumes the query you are going to use has only dates within the range and the min date is the first day of week

Private Sub Report_Load()
  Dim minDate As Date
  Dim maxdate As Date
  Dim i As Integer
  minDate = DMin("localDate", "qryNewData")
  maxdate = DMax("localDate", "qryNewData")
  For i = 1 To 7
    Me.Controls("L" & i).Caption = Format(minDate, "mm/dd/yyyyy")
    Me.Controls("D" & i).ControlSource = minDate
    minDate = minDate + 1
    If minDate > maxdate Then Exit For
  Next i
End Sub

However this will not work in Print Preview since you cannot do these modifications. It will work with your button as designed.

To demo that this works run "qryTest" which will add one week to all records. Then open your report.

 

[Samantha]

1) There are a couple of ways to make variable header crosstab reports. Here's one. The documentation is in the second file.
2) Reports are sequential processes. You can't print something that doesn't yet exist. To solve this problem, you need a second query that sums the detail by employee. Then you can join the crosstab to the totals query and you will have the total for each employee on every row so it will be available in the employee header.
3) Right([JobCode],2)="CI" or InStr([JobCode],"CI")>0 will work.

Pat,

I tackled the easiest parts first, which happens to be backwards.

#3 - Was a simple resolution went with InStr; and I saw a similar example but kept trying to throw in a * for good measure.
#2 - again I was working backwards and was trying to pull in a total before the crosstab qry instead of joining them afterwards. Which now makes sense that it was quadrupling my totals.

For number one I need more time to study what you have provided. One thing I have come across thus far in my report is say with tomorrow's weather no one works. Where they would typically work Saturday I don't want that date/column to disappear from my report. For clarity - it didn't disappear but provided an error that the field was not found.
It appears that shouldn't happen If I can figure out how to implement? Can you confirm?

 

[MajP]

Where they would typically work Saturday I don't want that date/column to disappear from my report. For clarity - it didn't disappear but provided an error that the field was not found.

Did you look at the solution I provided? No error and all days shown.
However. If there was no values for monday or in the middle of the week then it would error. This will provide the week regardless of how many days have values.

Private Sub Report_Load()
  Dim minDate As Date
  Dim i As Integer
  minDate = DMin("localDate", "tblData")
  'get monday of week if not the min
  minDate = DateAdd("d", 1 - Weekday(minDate, vbMonday), minDate)
 
  For i = 1 To 7
    Me.Controls("L" & i).Caption = Format(minDate, "mm/dd/yyyy")
    If DCount("*", "tblData", "localdate = # " & Format(minDate, "mm/dd/yyyy") & "#") > 0 Then Me.Controls("D" & i).ControlSource = minDate
    minDate = minDate + 1
  Next i
End Sub

To test I removed all monday values.

 

[Pat Hartman]

Actually, my example is more complicated than you need. The simplest solution for your situation is to use the weekday as the column name. Then use the range to calculate the column Title. The title for the Sunday column would be
Forms!yourform!StartDate
Then for Monday:
Forms!yourform!StartDate + 1
Then for Tuesday:
Forms!yourform!StartDate + 2
etc.
So, instead of basing the crosstab on the table, create a query that selects the date based on the form parameters but changes the date to the WeekDay. Then the crosstab uses the new query rather than the table. The crosstab also needs the day names put into the column heading property. This example uses month names. You need day names.

 

[Samantha]

Did you look at the solution I provided?

Oh no I must not have refreshed my screen in some time. I will take a look - you don't know how much time and frustration you've already saved me. The lady that normally does this in excel, manually, quit and now I get the pleasure of making sure everyone gets paid. Last Monday was a disaster and it took me all day nearly so I'm determined not to do that again.

Actually, my example is more complicated than you need.

sometimes I think I am pretty good - and other times something like your example bursts my bubble.

Seriously though thank you both for your help.

 

[arnelgp]

same method as majp, only i used the Open event to change/set the Captions of DayName label and the Date Label.
also to set the ControlSource of the textboxes.
I put conditional format on jobcode.