Append Query, show all dates in range even if no data exists

[GinaStar]

Current query is based on a payroll table that has PayPeriod, Posted date, data. I need to show a value for PayPeriod/Posted date in a date range, even if no data exists for that date. This is used to set a column alias for an unbound crosstab report. Date range is always 7 days, running Saturday thru Friday and I need to show the date, even if there is no payroll for that particular date. Am using an unbound crosstab report that will be hard coded for 7 days, but the date value changes according to the current week. Normally, this report is only one week long, but in the winter time, it may cover 2-3 weeks, or more. That is why I am using the 'week' function to get a week number, and can set sorting/grouping on 'week'.

INSERT INTO tblPayAlias ( datPayPeriod, datTicketPost, [Level] )
SELECT tblPayrollCompany.datPayPeriod, tblPayrollCompany.datTicketPost, Format([datTicketPost],"ww",7) AS Week
FROM tblPayrollCompany
GROUP BY tblPayrollCompany.datPayPeriod, tblPayrollCompany.datTicketPost, Format([datTicketPost],"ww",7)
HAVING (((tblPayrollCompany.datPayPeriod)=[Forms]![frmPayroll]![txtPayDate]));

Some dates do not have payroll records. (ie: sunday) Would like to 'force' an entry for the date that has no records.

right now getting:
Pay Date Posted Week
2/13/04 1/19/04 4
2/13/04 1/20/04 4
2/13/04 1/21/04 4
2/13/04 1/22/04 4
2/13/04 1/23/04 4
2/13/04 1/26/04 5
2/13/04 1/27/04 5
2/13/04 1/28/04 5
2/13/04 1/29/04 5
2/13/04 1/30/04 5
2/13/04 1/31/04 6
2/13/04 2/2/04 6
2/13/04 2/3/04 6
2/13/04 2/4/04 6
2/13/04 2/5/04 6
2/13/04 2/6/04 6

need to show entries for 01/24, 01/25, 02/01, etc. I want it to show it in groups of 7 days, based on the 'week' value, even if there is no payroll entry for those particular pay dates. This will be used to make a crosstab report for payroll that looks similar to the following:

driver 01/24 01/25 01/26 01/27 01/28 01/29 01/30 Week 1
Joe 0 0 100 0 0 0 0 100
Tom 100 0 100 100 0 0 0 300
100 0 200 100 0 0 0 400


driver 01/31 02/01 02/02 02/03 02/04 02/05 02/06 Week 2
Joe 0 0 100 0 100 100 0 300
Tom 100 0 100 0 0 0 0 200
100 0 200 0 100 100 0 500

driver SAT SUN MON TUE WED THU FRI TOT PAY
Total 200 0 400 100 100 100 0 1000

Checks issued Week 2
Joe $400
Tom $500

I am generating a crosstab report looking like the above example. There will not be two records on one day for the same driver, but there will be days that have no records for a driver. Normally our pay period runs from Saturday to Friday and we pay weekly. In the winter, If a driver only has one or two days in a payperiod, we 'roll' that payment over to the next week and just issue one check for the multiple weeks, ie: Payroll for Monday in week 1, payroll for Monday, Wednesday and Thursday in week 2, no check issued week 1, check issued in Week 2 has payroll for Monday of first week and Monday, Wednesday and Thursday of second week. I am trying to set up a report that will show the payroll values for the payperiod (ie, 02/13/04) broken down by date (ie: payroll for 01/26 Monday, 02/02 Monday, 02/04 Wednesday and 02/05 Thursday)

driver 01/24 01/25 01/26 01/27 01/28 01/29 01/30 Week 1
Joe 0 0 100 0 0 0 0 100

driver 01/31 02/01 02/02 02/03 02/04 02/05 02/06 Week 2
Joe 0 0 100 0 100 100 0 300

driver SAT SUN MON TUE WED THU FRI TOT PAY
Joe 0 0 200 0 100 100 0 400

Total Check would be $400 in above example, with 4 days on it. I need to show the breakdown of the dates for verification purposes. Problem I am having is that it skips the dates if there is no data for that date..query just shows 01/26, 02/02, 02/04 and 02/05. I need it to show all dates from 01/24 to 02/06 with zero values for the date that has no data. Since this is an unbound crosstab report that has variable headings, I 'have' to have a date heading even for days with no data, or it does not fill my headers correctly <annoyed sound> Have been working on this for almost 2 weeks and it is driving me crazy.

Thanks! Regina

 

[bhoran]

One way to get the days with no records would be to build a table with each day of the year in it (you may have this available in some other package or you could build it in Excel) then join that table so the join shows all records from that table but only records that match on the tblPayrollCompany table. I am not sure if this is the best way, but it should work.

If you were prepared to show the days as 1-7 of a particular week you could build a table with only days 1-7 and wk1 -52 which would save building a table each year. But that may not be as useful to the end user when reviewing the reports.

 

[spongie1]

This might be dumb, and I might be missing the point of what you are trying to do, but that has never stopped me before.

Could you create a table containing the numbers 0,1,2,3,4,5,6...

Then create a query based off the posted date in your original table...

For the field in the query put something like:

posted-(Weekday(posted))

This should give you the Saturday that began that week for all entries.
Turn this into a group by query where you group by:
posted-(Weekday(posted))

Turning it into a group by query will eliminate all duplicate entries.

Next, create another query. In that query, add the table with the numbers and the query you just created. Do not link them in any way

Just put the a statement that looks like:

num+postedDate

in one of the fields.

That should give you all the dates that you are trying to get for that date range.

Next you can create a join between this latest query and the table that you are trying to create the crosstab for...

with this join make sure that you select the join type that gives you all entries for the last query I explained how to make.

for the fields that might be blank you probably can use something like....

iif([field],[field],0)

where field is the name of the field that you want to fill with a zero when it does not exist. (just put that statement in one of the fields.)

Now, you can use the query you just created to build your cross tab.

 

[spongie1]

One more thing... if your week begins on Monday instead of Saturday you could do something like:

posted-(Weekday(posted)-2)

 

[spongie1]

Oh, forgot to tell you to join the date field you constructed in the query to the posted field in your table..

Again remember to select the join type that shows all values in the query no matter what it shows in your table.

 

[spongie1]

Will there ever be a case where values for a week for one driver does not have any values (all will be zero), but you still need to list the values as zero anyway?

If yes, will there every be a week where values for all the drivers will be zero, but you still need to list the values for a particular driver as zero for that week?

The solution I suggested will work as expected provided that at least one driver has worked during that week.

If you have a condition where no driver works and you want to show the hours as zero for all drivers... I will need to think a bit on that one.

I am assuming at least one driver works during every week.

 

[GinaStar]

will try your suggestions. Yes, there may be weeks where all drivers off (ie: week of Christmas Holiday when we shut down completely), but we don't usually run a payroll report for that week. That applies to our company only, may not apply to other companies if I ever get this database to the point of sale. I would like to list all drivers, each week (not hard, can do that in the join to the employee table). But the problem comes in when I want to show a date that has no records <grin>. I think your suggestion may work if I modify it a bit and use the week function in combination with the day function to get a list of all days for that week. If I can add it together to get week 3 day 3, then I can use the dateadd function to get week 3, day 1 and convert it to a date and then use that as a starting date, show all dates between week 3 day 1 and week 4 day seven. Let me play around with this a bit and I will post the results.

 

[spongie1]

Are all pay dates the same for all drivers?

Are the pay dates fixed?... ie. 2/13/2004... 2/27/2004...

Does every driver get a check printed for every pay period even if he didn't work any days during that pay period?

 

[spongie1]

by check printed, I really meant &quot;run a payroll report&quot; : )

 

[GinaStar]

the pay dates are only fixed as to the range Saturday to Friday, not the particular dates. No, the driver does not get a check printed for the payperiod, but I do want it to show on the report as a zero value (for as long as that driver is employed, by which I check a datTerminated date field using isnull([datTerm]) or dateAdd([datTerm,&quot;d&quot;,-30) to show all drivers currently employed or terminated within the last 30 days (this part works perfectly!)) I currently have a report that hard codes SAT SUN MON TUE WED THU FRI, but I want it to show the dates and them to change based on the week value. The way it works now, it is just showing the values for all sat in sat, all sun in sun, etc, no matter what week it is for and this makes for problems when trying to check payroll for errors.

 

[dhookom]

Make the column names static by using relative date columns. Assuming a form with a text box for the ending date (Forms!frmA!txtEnd) for the user to enter the last date for the report. You can set up your column heading
expression as:
ColHead: &quot;Day&quot; & DateDiff(&quot;d&quot;, [DateField], Forms!frmA!txtEnd)
Set the column headings property to &quot;Day6:,&quot;Day5&quot;, &quot;Day4&quot;,&quot;Day3&quot;,..&quot;Day0&quot;
Day0 will have information for the date entered on the form. You will have to set the crosstab's Query | Parameters:
Forms!frmA!txtEnd Date/Time

To create column labels in your report, use text boxes with control sources
of:
=DateAdd(&quot;d&quot;, 0 ,Forms!frmA!txtEnd)
=DateAdd(&quot;d&quot;, -1 ,Forms!frmA!txtEnd)
...
=DateAdd(&quot;d&quot;, -6 ,Forms!frmA!txtEnd)


Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[GinaStar]

Duane, that is what I am currently using, but it only seems to work properly when I have only 1 week of payroll. If I have more than 1 week's worth of dates, it uses the same dates for each week instead of showing the proper dates. I have your unbound crosstab headers using column alias that I am trying to use to set this up, but have modified it to have two levels, week and day. I am trying to get it to show week/day 4a 4b 4c, 5a 5b, 6a 6b 6c etc, but it is not applying the a, b, c correctly. If I can get it to do the a/b/c properly, then it will solve my problem. it is doing 4a 4b 4c 5d 5a 5b 6c 6a 6b. It is not applying the a/b/c according to the week/day. I figure I just have the commands in the wrong places for the step thru, but Haven't quite figured out where to put them since I'm not 100% sure how you got it to work :/. Following is current function I am using:

Function UpdatePay(pbytNumColumns As Byte) As Long
'============================================================
' Purpose:
' Copyright: 1999 Business Results
' Company: Business Results
' Phone: 715-835-8130
' E-Mail: dhookom@invisibleinc.com
' Programmer: Duane Hookom
' Called From:
' Date: 1/22/00
' Parameters:
'============================================================
On Error GoTo UpdatePay_Err
Dim strErrMsg As String 'For Error Handling

Dim strSQL As String
Dim intAlias As Integer
Dim bytLevel As Byte
Dim bytMaxColumns As Byte
Dim datPayPeriod As Date
Dim datTicketPost As Date

Dim db As Database
Dim rs As Recordset


strSQL = &quot;Delete * from tblPayAlias&quot;
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL

DoCmd.OpenQuery &quot;qappColumnPay&quot;

DoCmd.SetWarnings True

bytMaxColumns = 7

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;tblPayAlias&quot 'table used to redefine/alias the column headings
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
intAlias = 65 'ascii value of 'A'
Do While Not .EOF
datTicketPost = !datTicketPost
bytLevel = Format([datTicketPost], &quot;ww&quot;, 7)
Do While datTicketPost = !datTicketPost And bytLevel = Format([datTicketPost], &quot;ww&quot;, 7)
.Edit
!Level = bytLevel
!ColumnAlias = Chr(intAlias) 'assign alias A - whatever
.Update
intAlias = intAlias + 1
If intAlias > 71 Then
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

UpdatePay_Exit:
On Error Resume Next
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function

UpdatePay_Err:
Select Case Err
Case Else
UpdatePay = Err.number
Resume UpdatePay_Exit
End Select

End Function

 

[dhookom]

GinaStar,
If you are always dealing with seven columns, I would create a crosstab that groups by Driver and
WeekOfateAdd(&quot;d&quot;,-Weekday([Posted]),[Posted])
Then set your column heading to Format([Posted],&quot;dddd&quot and your value to Sum of whatever field.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[spongie1]

GinaStar,

What if you changed>>>

intAlias = 65 'ascii value of 'A'
Do While Not .EOF

To>>>

Do While Not .EOF
intAlias = 65 'ascii value of 'A'

 

[GinaStar]

I got it working! using this:

Set db = CurrentDb
Set rs = db.OpenRecordset(&quot;tblPayAlias&quot
With rs
If Not (.EOF And .BOF) Then
.MoveFirst
Do While Not .EOF
intAlias = 65 'ascii value of 'A'
datTicketPost = !datTicketPost
bytLevel = !Level
Do While !Level = bytLevel
.Edit
!ColumnAlias = Chr(intAlias)
.Update
intAlias = intAlias + 1
If intAlias > 71 Then
intAlias = 65
End If
.MoveNext
If .EOF Then
Exit Do
End If
Loop
Loop
End If
End With

Thanks for all help!!!!