<

QuickBooks: Distributing Overhead Using QODBC sp_report ProfitByCustomer WHEN NOT ALL EXPENSES WERE ASSIGNED TO JOBS

Published on
7,538 Points
1,538 Views
Last Modified:
Approved
Annaliese Dell
Find Annaliese on Amazon, Barnes & Noble and VBQuick.com.
Oh no! Did you forget to:

1. Set up QuickBooks Payroll for Job Costing?
2. Check Payroll Items to Track Expenses By Job?
3. Assign Overhead to expenses not belonging to a particular job?

Not sure if you did or didn't do any of the above?

Find out quickly by running the QuickBooks Profit & Loss Standard report. Select a date macro that includes a lot of transactions. An entire year is best.

Next:

1. Set columns to Total Only and note the Net Profit at the bottom.
2. Set columns to Customer:Job and note the Net Profit at the bottom.

If the two Net Profits do not match, NOT all expenses were assigned to jobs. Quickly fix the features you forgot to avoid this dilemma going forward.

For past transactions, this article provides a work around.

Applications/Utilities required:

1. Microsoft Access
2. QOCBC
3. QuickBooks

Knowledge required:

1. Basic understanding of VBA
2. How to create a Microsoft Access form
3. How to use the Visual Basic Editor
4. How to run QuickBooks reports

You can simply create the form and copy the code below into it. If you want to understand the process so you can alter it to fit your needs, an explanation follows. Be warned, however, that it may seem confusing. A good many calculations and loops are required. The end result, however, is tidy and handy.

This method uses the QODBC sp_report ProfitAndLossStandard to:

 1. run the Profit & Loss Standard for ALL
 2. run the Profit & Loss Standard for Overhead 
 3. run the Profit & Loss Standard for EACH customer
 4. calculate amounts not applied to jobs or overhead
 5. add NoJob amounts to Overhead amounts for TotalOverhead
 6. distribute TotalOverhead among jobs according to each job's percent of payroll
 
This outline explains the steps. Sample code for DAO is provided.

1.  Create a Main table in Microsoft Access using QODBC sp_report ProfitAndLossStandard
2.  Add columns:
      a.  TotalGross
      b.  TotalNets

3.  Create a temporary table using QODBC sp_report ProfitAndLossStandard filtered by 'Overhead'. Add fields:
     a.  Overhead_Gross
     b.  Overhead_OH  (dropped later)
     c.  Overhead_Net (dropped later)

4.  Transfer temporary tabel data to main table
5.  Add column: NoJob for the expenses that were not assigned to either jobs or overhead
6.  Repeat steps 3-5 for each customer EXCEPT Overhead. (already done)
7.  Drop columns in Main table:
     a.  Overhead_OH
     b.  Overhead_Net

At this point the table has these columns:

 a.  Description: from initial profit report by TotalOnly
 b.  Amount: from initial profit report by TotalOnly
 c.  TotalGross: Total of Overhead + Each Job + No Job Assigned
 d.  TotalNets: Total of Each Job + Overhead + Each Job's Share of Overhead
 e.  Overhead_Gross: from profit report filtered by 'Overhead'
 f.  NoJob: subtract (Overhead + Each Job) from Amount
 g.  Columns for each job:
     i. Job_Gross: from Profit report filtered by customer
     ii. Job_OH: Job's share of overhead
     iii. Job_Net: Job's Gross + Job's share of overhead

The table should look similar to this with the three job columns repeating for each job.

Table1.png
Next you need to fill in the blanks by calculating the NoJob, Overhead_Gross and TotalGross amounts.
 
 LOOP through the Main table RECORDS
      LOOP through the Main table COLUMNS stopping at each "_Gross" column
           calculate dblGross by adding values
      NEXT COLUMN
      NoJob = dblGross - Amount
      Overhead_Gross = Overhead_Gross + NoJob
      TotalGross  = dblGross + NoJob
      dblGross=0
 NEXT RECORD

Open in new window


The Overhead_Gross column now includes NoJob amounts so you can distribute that figure among jobs. But how?

This method uses payroll to calculate Percent of Overhead. The premise is that if each job took a certain percent of Total Payroll to complete, that job should also take the same percent of Overhead. To do this:

 1. Calculate Total Payroll minus Overhead Payroll as dblPayroll.
 2. Divide each job's payroll by dblPayroll to get each job's Percent of Total Payroll as dblPercentPayroll
 3. Multipy Overhead_Gross by dblPercentPayroll to get each job's SHARE of Overhead as Job_OH

The code does it this way:

  Extract Total Payroll by DLookup

  Extract Overhead Payroll by DLookup
  LOOP through COLUMNS stopping at each COLUMN like "*_Gross" (excluding Overhead)
       Extract Job Payroll by DLookup as dblJobPayroll
       Divide Job Payroll by Total Payroll to get Percent of Payroll as dblPercentPayroll
            LOOP through the records
                Multiply dblPercentPayroll by Overhead_Gross to get Job_OH
                * (each job's share of overhead is Overhead_Gross X that job's percent of payroll)
            NEXT RECORD
           dblJobPayroll=0
           dblPercentPayroll=0
  NEXT COLUMN
Using this method means any job with no payroll gets no share of overhead. For example, if Sale of Asset is assigned to Customer:Cash Sale, there is no payroll for Cash Sales and therefore no share of overhead.

Now that you have each Job's cost + share of overhead, you can add them together for each Job's NET.

The code does it this way:
 LOOP through Main table COLUMNS stopping at each column like "*_Net"
       LOOP through Main table RECORDS
            Calculate Job_Net by adding Job_Gross and Job_OH
       NEXT RECORD
 NEXT COLUMN
Also handy is adding all the JOB NETS for TotalNets to make sure Amount, TotalGross and TotalNets all match. The code does it this way:
 LOOP through Main Table RECORDS
        LOOP through Main table COLUMNS stopping at each column like "*_Net"
               add each value to dblTotal
        NEXT COLUMN
        TotalNets=dblTotal
        dblTotal=0
 NEXT RECORD 
  
Finishing Touches

The resulting table contains a lot of data, much of it for calculation purposes. You may want to delete all zero rows using fncDeleteRecord (included) or your own procedure.

You may also want to delete the following columns:

 1. NoJob
 2. like "*_OH"
 3. like "*_Gross"

Now the table should look similar to this. Amounts in the Amount, TotalGross and TotalNets columns should all equal each other.

The Overhead_Gross column will equal the first three columns when none of the transactions for those accounts were applied to jobs.

The Overhead_Gross column will NOT equal the first three columns when some of the transactions were applied to jobs but others were applied to Overhead.

table2.pngAfter examining the table, you may also wish to also delete the columns:

 1. TotalGross
 2. TotalNets

Creating a Form

Create a form containing:

    1. Listbox with One column: lstCustomer
    2. TextBox: DateFrom
    3. TextBox: DateTo
    4. CommandButton: cmdCreateTable
    5. CommandButton: cmdDropCustomerOHColumns
    6. CommandButton: cmdDropCustomerGrossColumns
    7. CommandButton: cmdOpenTable

Add error trapping and other buttons and options as desired.

The code for the form follows. In the interest of brevity and because long lines of code can be intimidating and confusing, all but the most essential error trapping and module references were removed (for example, no trapping for existing table or query, etc.). Essential public procedures and functions follow in another  snippet below. You can substitute your own or use the ones provided.
 
Option Compare Database
Option Explicit

Private Sub cmdOpenTable_Click()
DoCmd.OpenTable "ProfitByCustomer"
End Sub

Private Sub Form_Open(Cancel As Integer)
On Error GoTo Form_Open_err
100     lstCustomer.RowSource = ""
110     Call fncQODBCQuery("Select FullName from Customer where sublevel=0", True)
120     Call fncRunSQL("select * into tblCustomer_RL from qryTemp")
130     lstCustomer.RowSource = "select FullName from tblCustomer_RL"
Exit Sub
Form_Open_err:
Debug.Print "Form_Open", Erl, Err.Number, Err.Description
End Sub

Private Sub cmdCreateTable_Click()
On Error GoTo cmdCreateTable_Click_err

'*** Make Total Profit Report
100     Dim t As String, i As Integer
110     t = "ProfitByCustomer"
120     Call fncProfitTable(t, DateFrom, DateTo)

'*** add columns
130     Call fncAddField(t, "TotalGross", dbDouble, False)
140     Call fncAddField(t, "TotalNets", dbDouble, False)

'*** add overhead first so it comes before the customer data
150     Call PutGross("tblTemp", t, "Overhead")
160     Call fncAddField(t, "NoJob", dbDouble, False)

'*** iterate through customers
300     For i = 0 To lstCustomer.ListCount - 1
310         If lstCustomer.ItemData(i) <> "Overhead" Then
320             Call PutGross("tblTemp", t, lstCustomer.ItemData(i))
330         End If
340     Next i

'*** drop unnecessary Overhead columns
400     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [Overhead_OH]")
410     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [Overhead_Net]")

'*** perform calculations
500     Call PutTotals(t)
510     Call PutOverhead(t)
520     Call PutNet(t)
530     Call TotalNets(t)

'*** delete zero rows and drop NoJob column
540     Call fncDeleteRecord(t, "Amount", "0")
560     Call fncRunSQL("ALTER TABLE " & t & " DROP COLUMN [NoJob]")
57      DoCmd.OpenTable t
Exit Sub

cmdCreateTable_Click_err:
Debug.Print "cmdCreateTable_Click", Erl, Err.Number, Err.Description
End Sub

Private Sub PutGross(sTableFrom As String, sTableTo As String, sCustomer As String)
On Error GoTo PutGross_err

'*** create a viable Customer column by removing spaces, etc.
100     Dim sColumn As String
110     sColumn = fncColumn(sCustomer)

'*** import the data for this customer into a temporary table
120     Call fncProfitTable("tblTemp", DateFrom, DateTo, sCustomer)

'*** if this customer had no activity, don't add it
130     If Nz(DSum("[AMOUNT]", "tblTemp", "[DESCRIPTION]='NET INCOME'"), 0) = 0 Then Exit Sub

'*** add additional columns for this customer to the Main table
150     Call fncAddField(sTableTo, sColumn & "_Gross", dbDouble, False, 20)
160     Call fncAddField(sTableTo, sColumn & "_OH", dbDouble, False, 20)
170     Call fncAddField(sTableTo, sColumn & "_Net", dbDouble, False, 20)

'*** loop through the temporary table updating the Main table
'*** you may wish to do this using two recordsets instead of the Update Record method
'*** the two recordset method is faster, just more code lines

200     Dim db As DAO.Database, rs As DAO.Recordset
210     Set db = CurrentDb
220     Set rs = db.OpenRecordset(sTableFrom)

300     If rs.RecordCount > 0 Then
310         rs.MoveLast
320         rs.MoveFirst
330         Do While Not rs.EOF
340             Call fncRunSQL("Update " & sTableTo & _
                " set " & sColumn & "_gross = " & rs("Amount").Value & _
                " where Description='" & rs("Description") & "'")
350         rs.MoveNext
360         Loop
380     End If
390     rs.Close
400     Set rs = Nothing
410     Set db = Nothing
Exit Sub
PutGross_err:
Debug.Print "cmdCreateTable_Click", Erl, Err.Number, Err.Description
End Sub

Private Sub PutTotals(sTableName As String)
On Error GoTo PutTotals_err

'*** Step 1. Add all Cutomer_Gross + Overhead_Gross to get dblGross
'            This amount will differ from Amount column if all expenses were not assigned a job or overhead
'*** Step 2. Subtract Amount from dblGross for the NoJob amount--expenses not assigned to jobs
'*** Step 3. Add the NoJob amount to the Overhead_Gross amount for the true Overhead amount
'            This equals: Jobs amounts + Overhead Amount + Amounts not assigned to jobs
'*** Step 4. Add the NoJob amouns to the TotalGross amounts
'            This is a double check column that should match the Amount column figures in the end
'            It shows that jobs + overhead + no jobs = amounts from profit report by TotalOnly

100     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer
110     Dim dblGross As Double, dblNoJob As Double
210     Set db = CurrentDb
220     Set rs = db.OpenRecordset(sTableName)
230 If rs.RecordCount > 0 Then
240     rs.MoveLast
250     rs.MoveFirst
300     Do While Not rs.EOF
310     For i = 0 To rs.Fields.Count - 1
320         If rs.Fields(i).Name Like "*gross*" Then
330             dblGross = dblGross + Nz(rs.Fields(i).Value, 0)
370         End If
380     Next i
400     rs.Edit
410     rs("NoJob") = FormatNumber((dblGross - rs!Amount) * -1)
420     rs.Update
430     rs.Edit
440     rs("Overhead_Gross") = FormatNumber(rs!overhead_gross + rs!NoJob)
450     rs("TotalGross") = FormatNumber(dblGross + rs!NoJob, 0)
460     rs.Update
470     dblGross = 0
490     rs.MoveNext
500     Loop
510 End If
520 rs.Close
530 Set rs = Nothing
540 Set db = Nothing

Exit Sub
PutTotals_err:
Debug.Print "PutTotals", Erl, Err.Number, Err.Description

End Sub
Private Sub PutOverhead(sTableName As String)
On Error GoTo PutOverhead_err

'*** uses payroll to determine the percent of TotalOverhead to apply to each job
'*** gets the Total Payroll for all JOBs (excluding overhead payroll) as dblPayroll
'*** gets each Job Payroll as dblJobPayroll
'*** calculates percent of payroll attributable to each job as dblPercentPayroll
'*** adds all the job payroll percents and prints the total in the immediate window
'*** this should always equal 1 (for 100%)

100     Dim dblPayroll As Double, dblJobPayroll As Double
110     Dim dblPercentPayroll As Double, dblTotalPercent As Double
120     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String

'*** get the total payroll from the Amount column (original Profit Report by TotalOnly)
'----------------------------------------------------------------------------------------------------------------
'!!!!!//////////////////////  MAKE SURE YOU REPLACE 'Total 66000 · Payroll Expenses' \\\\\\\\\\\\\\\\\\\\\\\\\\\
'!!!!!\\\\\\\\\\\\\\\\\\\\  WITH THE NAME OF YOUR TOTAL PAYROLL ACCOUNT FROM QUICKBOOKS   ////////////////////
'----------------------------------------------------------------------------------------------------------------

130     dblPayroll = _
        Nz(DLookup("[Amount]", sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)

'*** subtract the Overhead Payroll from dblPayroll for the Payroll attributable to jobs as dblPayroll
140     dblPayroll = _
        dblPayroll - Nz(DLookup("[Overhead_Gross]", _
        sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)   '*** REPLACE 'Total 66000 · Payroll Expenses'

'*** format dblPayroll
150     dblPayroll = FormatNumber(dblPayroll, 0)

'*** open the Main table as a recordset
210     Set db = CurrentDb
220     Set rs = db.OpenRecordset(sTableName)
230     If rs.RecordCount > 0 Then
240     rs.MoveLast
250     rs.MoveFirst

'*** LOOP through COLUMNS stopping at each "_Gross" EXCEPT "Overhead_Gross"
'*** these are the JOBS or CUSTOMERS
310     For i = 0 To rs.Fields.Count - 1
320         If rs.Fields(i).Name Like "*_gross*" And InStr(1, rs.Fields(i).Name, "overhead", vbTextCompare) = 0 Then

'*** Get each Job's payroll with DLookup
'----------------------------------------------------------------------------------------------------------------
'!!!!!//////////////////////  MAKE SURE YOU REPLACE 'Total 66000 · Payroll Expenses' \\\\\\\\\\\\\\\\\\\\\\\\\\\
'!!!!!\\\\\\\\\\\\\\\\\\\\  WITH THE NAME OF YOUR TOTAL PAYROLL ACCOUNT FROM QUICKBOOKS   ////////////////////
'----------------------------------------------------------------------------------------------------------------

330             dblJobPayroll = _
                Nz(DLookup(rs.Fields(i).Name, sTableName, "[Description]='Total 66000 · Payroll Expenses'"), 0)

'*** Divide the Job's payroll by the Total Payroll to get each Job's Percent of Payroll
360             If Nz(dblPayroll, 0) <> 0 Then dblPercentPayroll = FormatNumber(dblJobPayroll / dblPayroll, 2)

'*** start a new LOOP for each JOB

370                rs.MoveFirst
'*** LOOP through each JOB's RECORDS multiplying each Overhead_Gross value by dblPercentPayroll
'*** this gives you the portion of overhead attributable to each job
'*** store that value in the JOB_OH field which is represented by rs(sFieldName)
380     Do While Not rs.EOF
390         sFieldName = Replace(rs.Fields(i).Name, "_Gross", "_OH")
400         rs.Edit
410         rs(sFieldName).Value = FormatNumber(Nz(rs("overhead_gross") * dblPercentPayroll, 0), 0)
420         rs.Update
430         rs.MoveNext
440     Loop
450     End If 'THIS IS A "_Gross" COLUMN AND ALSO NOT THE OVERHEAD_Gross COLUMN

'*** RESET VARIABLES FOR NEXT COLUMN LOOP
460     dblJobPayroll = 0
470     dblPercentPayroll = 0

480     Next i 'COLUMN

510     End If 'RS.RECORDCOUNT>0
520     rs.Close
530     Set rs = Nothing
540     Set db = Nothing

Exit Sub
PutOverhead_err:
Debug.Print "PutOverhead", Erl, Err.Number, Err.Description

End Sub
Private Sub PutNet(sTableName As String)
On Error GoTo PutNet_err
'*** LOOP THROUGH COLUMNS ADDING EACH JOB_GROSS TO EACH JOB_OH TO GET EACH JOB_NET

150     Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String, dblTotalNets As Double
210     Set db = CurrentDb
220     Set rs = db.OpenRecordset(sTableName)
230     If rs.RecordCount > 0 Then
240     rs.MoveLast
310     For i = 0 To rs.Fields.Count - 1
250     rs.MoveFirst
320     If rs.Fields(i).Name Like "*_Net" Then
330         sFieldName = Replace(rs.Fields(i).Name, "_Net", "")
340         Do While Not rs.EOF
350             rs.Edit
360             rs(sFieldName & "_Net").Value = Nz(rs(sFieldName & "_Gross").Value + rs(sFieldName & "_OH").Value, 0)
370             rs.Update
380         rs.MoveNext
390         Loop
400         End If ' COLUMNNAME LIKE "*_NET"
410         Next i ' COLUMN
420     End If 'RS.RECORDCOUNT>0
430     rs.Close
440     Set rs = Nothing
450     Set db = Nothing


Exit Sub
PutNet_err:
Debug.Print "PutNet", Erl, Err.Number, Err.Description

End Sub
Private Sub TotalNets(sTableName As String)
On Error GoTo TotalNets_err

'*** LOOP THROUGH COLUMNS ADDING ALL THE JOB_NETS AND PUTTING THEM IN THE TOTALNETS FIELDS
100      Dim db As DAO.Database, rs As DAO.Recordset, i As Integer, sFieldName As String, dblTotal As Double
110      Set db = CurrentDb
120      Set rs = db.OpenRecordset(sTableName)
130      If rs.RecordCount > 0 Then
140      rs.MoveLast
150         rs.MoveFirst
160         Do While Not rs.EOF
170         For i = 0 To rs.Fields.Count - 1 'COLUMNS
180         If rs.Fields(i).Name Like "*_Net" Then
190             sFieldName = Replace(rs.Fields(i).Name, "_Net", "")
200             dblTotal = dblTotal + rs.Fields(i).Value
210         End If 'COLUMN NAME LIKE "*_NET"
220         Next i 'COLUMN
230         rs.Edit
240         rs("TotalNets").Value = dblTotal
250         rs.Update
260         dblTotal = 0
270         rs.MoveNext
280         Loop
290     End If 'RS.RECORDCOUNT>0
300 rs.Close
310 Set rs = Nothing
320 Set db = Nothing


Exit Sub
TotalNets_err:
Debug.Print "TotalNets", Erl, Err.Number, Err.Description

End Sub
Function fncColumn(sCustomer As String) As String
On Error GoTo fncColum_err
'*** REMOVE CHARACTERS THAT CAUSE ERRORS IN COLUMN NAMES
'*** ADD MORE IF NECESSARY
'*** USE AN ARRAY IF NECESSARY
100     fncColumn = Replace(Replace(sCustomer, " ", ""), "-", "")
110     fncColumn = Replace(fncColumn, "&", "")
120     fncColumn = Trim(Left(fncColumn, 8))
Exit Function
fncColum_err:
Debug.Print "fncColumn", Erl, Err.Number, Err.Description
End Function

Private Sub cmdDropCustomerGrossColumns_Click()
Dim db As DAO.Database, td As DAO.TableDef, i As Integer
Set db = CurrentDb
Set td = db.TableDefs("profitbycustomer")
For i = 0 To td.Fields.Count - 1
    If td.Fields(i).Name Like "*_Gross" Then
        Call fncRunSQL("ALTER TABLE PROFITBYCUSTOMER DROP COLUMN " & td.Fields(i).Name)
    End If
Next i
Set td = Nothing
Set db = Nothing
End Sub

Private Sub cmdDropCustomerOHColumns_Click()
Dim db As DAO.Database, td As DAO.TableDef, i As Integer
Set db = CurrentDb
Set td = db.TableDefs("profitbycustomer")
For i = 0 To td.Fields.Count - 1
    If td.Fields(i).Name Like "*_OH" Then
        Call fncRunSQL("ALTER TABLE PROFITBYCUSTOMER DROP COLUMN " & td.Fields(i).Name)
    End If
Next i
Set td = Nothing
Set db = Nothing

End Sub

Open in new window


The following need to be added in a public module. You might find them handy for other procedures as well.

1. fncQODBCQuery
2. fncRunSQL
3. fncProfitTable
4. fncAddField
5. fncDeleteRecord
 
Function fncQODBCQuery(sSQL As String, blnReturnsRecords As Boolean, Optional q As String = "qryTemp")
Dim db As Database, qd As QueryDef
      Set db = CurrentDb
      Set qd = db.CreateQueryDef(q)
      qd.Connect = "ODBC;DSN=QuickBooks Data;SERVER=QODBC"
      qd.SQL = sSQL
If blnReturnsRecords = True Then
      qd.ReturnsRecords = True
Else
      qd.ReturnsRecords = False
      qd.Execute
End If
Set qd = Nothing
Set db = Nothing
End Function



Function fncRunSQL(sSQL As String)
DoCmd.SetWarnings False
DoCmd.RunSQL sSQL
DoCmd.SetWarnings True
End Function


Function fncProfitTable(sTableName As String, BegDate As Date, EndDate As Date, Optional sCustomer As String = "")
Dim sDateString As String, sSQL As String, sEntityFilter
If Nz(sCustomer, "") <> "" Then sEntityFilter = ", entityfilterfullnamewithchildren ='" & sCustomer & "'"
sDateString = " datefrom=" & fncQBDate(BegDate) & " ,dateto=" & fncQBDate(EndDate)
sSQL = "sp_report ProfitAndLossStandard show text,label,amount_1 parameters " & sDateString & ", returnrows='All'" & sEntityFilter
Call fncQODBCQuery(sSQL, True)
Call fncRunSQL("select text  & label as Description ,fix((nz(amount_1,0)*100)/100) as Amount into " & sTableName & " from qryTemp")
End Function

Function fncAddField(sTableName As String, sFieldName As String, dbFieldType As String, Optional blnAutoNumber As Boolean = False, Optional iFieldSize As Integer = 255)
Dim db As DAO.Database, td As DAO.TableDef
Set db = CurrentDb
Set td = db.TableDefs(sTableName)
td.Fields.Append td.CreateField(sFieldName, dbFieldType, iFieldSize)
td.Fields.Refresh
If blnAutoNumber = True Then td.Fields(sFieldName).Attributes = dbAutoIncrField
Set td = Nothing
Set db = Nothing
End Function

Function fncDeleteRecord(sTableName As String, sFieldName As String, sFieldValue As String)
On Error GoTo fncDeleteRecord_err
100     Dim db As DAO.Database, td As DAO.TableDef
110     Set db = CurrentDb
120     Set td = db.TableDefs(sTableName)
130     If sFieldValue = "Null" Then
200     Call fncRunSQL("delete from [" & sTableName & "] where isnull([" & sFieldName & "])=true")
210     ElseIf td.Fields(sFieldName).Type = 20 Or td.Fields(sFieldName).Type = 7 Then
220     Call fncRunSQL("delete from [" & sTableName & "] where [" & sFieldName & "]=" & sFieldValue)
230     Else
300     Call fncRunSQL("delete from [" & sTableName & "] where [" & sFieldName & "]='" & sFieldValue & "'")
310     End If
Exit Function
fncDeleteRecord_err:
Call fncWriteError(Now, "", "Module Functions", "Function fncDeleteRecord", Err.Number, Err.Description, Erl)
End Function

Open in new window


Once you have this form and the procedures in the database, you may want to add features like:

1. A list box for One-click DateMacros
2. An option to select customers and skip the Share of Overhead part
3. Work with different sublevels of customers
4. Add an option for Active, Not-Active or All customers

Important!:  When using the Share of Overhead feature, you must include ALL customers, Active and Not-Active and ALL from the same sublevel and never mix sublevels.

Now don't forget to add job costing (if you use it) to your payroll items and expenses going forward.
0
Comment
1 Comment
 
LVL 8

Author Comment

by:Annaliese Dell
Thank you! I hope it helps somebody.
0

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Join & Write a Comment

Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month