Need to create an invoice with a previous billing field

[cueballnavigator]

I have an invoice report that is grouped by organizational unit divisions . The detail of the divisions (name, hours worked, expenses) is summed in the 'division footer'. I would like to display the total number of hours previously billed under the current invoice hour sum in the division footer.

Example:

Atlanta (Div Header)
Employee1 (Detail) 40
Employee2 40
Total (Div Footer) 80
Prior Billings for Atlanta 240
Total 320

What is the most efficient way to pull the prior billing info for each group in the report?

Thanks in advance

 

[PaulBricker]

Well, you don't say where you have the data stored, but assuming it's not in the Record Source for the report, I would think you could use either a subReport that contains the Prior Billing data or a DLookUp() Function. Neither is going to be super speedy depending on the amount of data you have. My preference is the DLookUp. I think is will run faster.

Paul

 

[cueballnavigator]

I have one table that contains the following fields:

Invoice Date
Division
Employee
Hours
Amount
Expenses

Right now the report is built on a query based on the invoice date. The prior billing information is not stored anywhere. It would need to be calculated based on the division and invoice date. (i.e. prior billngs equals all billings prior to the current invoice date for each specific division) The prior billing information would be located in the division footer and thus would relate only to that particular division.

 

[PaulBricker]

I would just create another query that does your prior billings calculations and then use a DLookUp on the Report to pull that info into the report. The query can filter or group any info based on dates or divisions that you need.

Paul